SPEED UP CODE...
I have this code to call a query directly on a Access mdb.
But when the code goto: Set RST = CMD.Execute the operation to get rset is very,very slow!!!!!
Sub QUERY_ACCESS_1()
On Error GoTo errore
Dim TEST As Long, var_SPORT As String, VAR_COD As String
Dim varDataRows As Variant, I As Long, TOTALE_CODICI As Long
Dim CNSQL As ADODB.Connection
Dim RST As ADODB.Recordset
Set RST = New ADODB.Recordset
Set CNSQL = New ADODB.Connection
Set CMD = New ADODB.Command
If Not CNSQL.State = adStateClosed Then
CNSQL.Close
End If
CNSQL.CursorLocation = adUseServer
CNSQL.Open [Ô]Provider=Microsoft.Jet.OLEDB.4.0;[Ô] & _
[Ô]Data Source=\\xxx.xxxx.xxxx.xxxxx\yyyy\PUBBLICA\VARIE\L0928.mdb;[Ô]
CMD.ActiveConnection = CNSQL
CMD.CommandText = [Ô]L0928_Query[Ô]
CMD.CommandType = adCmdStoredProc
[ô]NOTA: IL NOME DELLA QUERY DEVE ESSERE DIFFERENTE A NOME TABELLA E ALTRI CAMPI NEL DB
Set RST = CMD.Execute
varDataRows = RST.GetRows
TEST = UBound(varDataRows, 2) + 1
RST.Close
Set RST = Nothing
CNSQL.Close
Set CNSQL = Nothing
For I = 0 To TEST - 1
var_SPORT = varDataRows(0, I)
VAR_COD = varDataRows(1, I)
TOTALE_CODICI = varDataRows(2, I)
Next I
Exit Sub
errore:
MsgBox [Ô]Errore Numero: [Ô] & CStr(Err.Number) & vbCrLf & _
[Ô]Descrizione: [Ô] & Err.Description & vbCrLf & _
[Ô]Sorgente dell[ô]Errore: [Ô] & Err.Source
Err.Clear
End Sub
this is the query stored in access mdb:
SELECT DISTINCT L0928.PROVA03, L0928.PROVA05, Count(L0928.PROVA05) AS SOMMA5
FROM L0928
GROUP BY L0928.PROVA03, L0928.PROVA05;
note: the number of rows in access table L0928 are 115.457
But when the code goto: Set RST = CMD.Execute the operation to get rset is very,very slow!!!!!
Sub QUERY_ACCESS_1()
On Error GoTo errore
Dim TEST As Long, var_SPORT As String, VAR_COD As String
Dim varDataRows As Variant, I As Long, TOTALE_CODICI As Long
Dim CNSQL As ADODB.Connection
Dim RST As ADODB.Recordset
Set RST = New ADODB.Recordset
Set CNSQL = New ADODB.Connection
Set CMD = New ADODB.Command
If Not CNSQL.State = adStateClosed Then
CNSQL.Close
End If
CNSQL.CursorLocation = adUseServer
CNSQL.Open [Ô]Provider=Microsoft.Jet.OLEDB.4.0;[Ô] & _
[Ô]Data Source=\\xxx.xxxx.xxxx.xxxxx\yyyy\PUBBLICA\VARIE\L0928.mdb;[Ô]
CMD.ActiveConnection = CNSQL
CMD.CommandText = [Ô]L0928_Query[Ô]
CMD.CommandType = adCmdStoredProc
[ô]NOTA: IL NOME DELLA QUERY DEVE ESSERE DIFFERENTE A NOME TABELLA E ALTRI CAMPI NEL DB
Set RST = CMD.Execute
varDataRows = RST.GetRows
TEST = UBound(varDataRows, 2) + 1
RST.Close
Set RST = Nothing
CNSQL.Close
Set CNSQL = Nothing
For I = 0 To TEST - 1
var_SPORT = varDataRows(0, I)
VAR_COD = varDataRows(1, I)
TOTALE_CODICI = varDataRows(2, I)
Next I
Exit Sub
errore:
MsgBox [Ô]Errore Numero: [Ô] & CStr(Err.Number) & vbCrLf & _
[Ô]Descrizione: [Ô] & Err.Description & vbCrLf & _
[Ô]Sorgente dell[ô]Errore: [Ô] & Err.Source
Err.Clear
End Sub
this is the query stored in access mdb:
SELECT DISTINCT L0928.PROVA03, L0928.PROVA05, Count(L0928.PROVA05) AS SOMMA5
FROM L0928
GROUP BY L0928.PROVA03, L0928.PROVA05;
note: the number of rows in access table L0928 are 115.457
Luca,
Try to create an index into L0928 table with the fields PROVA03, PROVA05
CREATE INDEX idx_L0928_01 ON L0928 (PROVA03, PROVA05)
Try to create an index into L0928 table with the fields PROVA03, PROVA05
CREATE INDEX idx_L0928_01 ON L0928 (PROVA03, PROVA05)
this part of the code is [Ô]redundant[Ô]:
varDataRows = RST.GetRows
TEST = UBound(varDataRows, 2) + 1
you had fetch the records on previous statement : Set RST = CMD.Execute
do this way:
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
delete this:
For I = 1 To rst.recordcount
var_SPORT = varDataRows(0, I)
VAR_COD = varDataRows(1, I)
TOTALE_CODICI = varDataRows(2, I)
Next I
Excuse my poor english!!
varDataRows = RST.GetRows
TEST = UBound(varDataRows, 2) + 1
you had fetch the records on previous statement : Set RST = CMD.Execute
do this way:
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
delete this:
For I = 1 To rst.recordcount
var_SPORT = varDataRows(0, I)
VAR_COD = varDataRows(1, I)
TOTALE_CODICI = varDataRows(2, I)
Next I
Excuse my poor english!!
hummmmm.......
Tks LLAIA for suggestion..
But not completlyy undersand, can you post the complete my code with you addition?
Sorry for my bad english ( i[ô]m Italian from Napoli)
Pizza for you in ther case, sure!
Tks LLAIA for suggestion..
But not completlyy undersand, can you post the complete my code with you addition?
Sorry for my bad english ( i[ô]m Italian from Napoli)
Pizza for you in ther case, sure!
On Error GoTo errore
Dim TEST As Long, var_SPORT As String, VAR_COD As String
Dim varDataRows As Variant, I As Long, TOTALE_CODICI As Long
Dim CNSQL As ADODB.Connection
Dim RST As ADODB.Recordset
Set RST = New ADODB.Recordset
Set CNSQL = New ADODB.Connection
Set CMD = New ADODB.Command
If Not CNSQL.State = adStateClosed Then
CNSQL.Close
End If
CNSQL.CursorLocation = adUseServer
CNSQL.Open [Ô]Provider=Microsoft.Jet.OLEDB.4.0;[Ô] & _
[Ô]Data Source=\\xxx.xxxx.xxxx.xxxxx\yyyy\PUBBLICA\VARIE\L0928.mdb;[Ô]
CMD.ActiveConnection = CNSQL
CMD.CommandText = [Ô]L0928_Query[Ô]
CMD.CommandType = adCmdStoredProc
Set RST = CMD.Execute
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
RST.Close
Set RST = Nothing
CNSQL.Close
Set CNSQL = Nothing
Exit Sub
errore:
MsgBox [Ô]Errore Numero: [Ô] & CStr(Err.Number) & vbCrLf & _
[Ô]Descrizione: [Ô] & Err.Description & vbCrLf & _
[Ô]Sorgente dell[ô]Errore: [Ô] & Err.Source
Err.Clear
End Sub
Spero di aver contribuito. Sentiti libero di chiedere e aiutare gli altri in VBMANIA [S30] Google translator [S20]
Dim TEST As Long, var_SPORT As String, VAR_COD As String
Dim varDataRows As Variant, I As Long, TOTALE_CODICI As Long
Dim CNSQL As ADODB.Connection
Dim RST As ADODB.Recordset
Set RST = New ADODB.Recordset
Set CNSQL = New ADODB.Connection
Set CMD = New ADODB.Command
If Not CNSQL.State = adStateClosed Then
CNSQL.Close
End If
CNSQL.CursorLocation = adUseServer
CNSQL.Open [Ô]Provider=Microsoft.Jet.OLEDB.4.0;[Ô] & _
[Ô]Data Source=\\xxx.xxxx.xxxx.xxxxx\yyyy\PUBBLICA\VARIE\L0928.mdb;[Ô]
CMD.ActiveConnection = CNSQL
CMD.CommandText = [Ô]L0928_Query[Ô]
CMD.CommandType = adCmdStoredProc
Set RST = CMD.Execute
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
RST.Close
Set RST = Nothing
CNSQL.Close
Set CNSQL = Nothing
Exit Sub
errore:
MsgBox [Ô]Errore Numero: [Ô] & CStr(Err.Number) & vbCrLf & _
[Ô]Descrizione: [Ô] & Err.Description & vbCrLf & _
[Ô]Sorgente dell[ô]Errore: [Ô] & Err.Source
Err.Clear
End Sub
Spero di aver contribuito. Sentiti libero di chiedere e aiutare gli altri in VBMANIA [S30] Google translator [S20]
Hi friend... LLAIA ( i dont know your real name) tks as usual.
But i dont see the statetment Getrows varDataRows = RST.GetRows, peraphs not is needed?
But i dont see the statetment Getrows varDataRows = RST.GetRows, peraphs not is needed?
Hi friend... LLAIA ( i dont know your real name) tks as usual.
But i dont see the statetment Getrows varDataRows = RST.GetRows, ...peraphs not is needed?
But i dont see the statetment Getrows varDataRows = RST.GetRows, ...peraphs not is needed?
RST.GetRows is not necessary. This is one of the methods used to work with cursors disconnected.
Note that with the GetRows, you transfer the records to an array and then work with this array. So that was taking too long, because you were bringing the records of the database, placing the records in the array and then reading the array. In the code I posted, we ignore the second part.
Have you tested the code with my additions?
Note that with the GetRows, you transfer the records to an array and then work with this array. So that was taking too long, because you were bringing the records of the database, placing the records in the array and then reading the array. In the code I posted, we ignore the second part.
Have you tested the code with my additions?
I dont write ....... english ......
Hi LLAIA... sorry for delay, but busy.
ok, you code work very fast!
But when the mdb is in local hard disk s.a. c:\ the statement Set RST = CMD.Execute is i lightning!
In other case when i store the mdb in a Server dir the statement Set RST = CMD.Execute is very very slow!!!!
Note:
Your loop:
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
work fast when the statement Set RST = CMD.Execute is finished when the mdb is in c:\ or on a server dir
ok, you code work very fast!
But when the mdb is in local hard disk s.a. c:\ the statement Set RST = CMD.Execute is i lightning!
In other case when i store the mdb in a Server dir the statement Set RST = CMD.Execute is very very slow!!!!
Note:
Your loop:
While Not rst.EOF
var_SPORT = rst(0)
VAR_COD = rst(1)
TOTALE_CODICI = rst(2)
rst.movenext
Wend
work fast when the statement Set RST = CMD.Execute is finished when the mdb is in c:\ or on a server dir
Luca90,
MS-ACCESS works slow in a network. Well, everybody talks about it, I think !!! Many TOPICS about the same problem wera posted here in VBMANIA about this and, in all of them, ACCESS IS VERY SLOW TO WORK IN A NETWORK.
After MS MSDE and SQL Server Express, sinse 2000 (I think), I just work with then for small system (up to 4GB of data) and the performance still great.
Finally, if it[ô]s possible to you, try to change to SQL SERVER (Express, for free) or MySQL and realy speed-up your access to data.
MS-ACCESS works slow in a network. Well, everybody talks about it, I think !!! Many TOPICS about the same problem wera posted here in VBMANIA about this and, in all of them, ACCESS IS VERY SLOW TO WORK IN A NETWORK.
After MS MSDE and SQL Server Express, sinse 2000 (I think), I just work with then for small system (up to 4GB of data) and the performance still great.
Finally, if it[ô]s possible to you, try to change to SQL SERVER (Express, for free) or MySQL and realy speed-up your access to data.
Tópico encerrado , respostas não são mais permitidas