apwbe
asked on
need faster data search
I need to know if anyone can supply me with code to replace this code example that would speed up the recordset creation.(SQL Server or ACCESS)
Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
Do Until recordset.EOF
recordset.MoveFirst
'set a variable = to record matches'
recordset.MoveNext
Loop
Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
Do Until recordset.EOF
recordset.MoveFirst
'set a variable = to record matches'
recordset.MoveNext
Loop
Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
recordset.MoveFirst
Do Until recordset.EOF
'set a variable = to record matches'
recordset.MoveNext
Loop
recordset.MoveFirst
Do Until recordset.EOF
'set a variable = to record matches'
recordset.MoveNext
Loop
Is SQL in the openrecordset a SELECT statement? Use a SQL SELECT statement to find the matches for you, and the recordset returned will only have the data you need. If you need help in doing this, let me know and I will see what I can do, but I need more info first.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Currently I use a SQL statement such as
'SQL = SELECT * FROM DATABASE'
my code is:
SQL2 = "SELECT * FROM MUNIVIEW_PARCEL"
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage \db2cama.m db")
Set rs2 =Mydb2.OpenRecordset(SQL2, dbOpenForw ardOnly)
Do Until rs2.EOF
If rs2.Fields("APN").value = g_tin Then
'BLA BLA BLA'
End If
rs2.MoveNext
Loop
A question might be 'Would putting the WHERE statement in the SQL string speed this process since the recordset should only contain the one matching record (given that all the records in the file are unique). Currently it builds a recordset of all the records then loops through and selects a match from the recordset. Am I thinking along the correct path or am I just talking turkey?
I read somewhere that by using a "record count" at the begining of the loop that somehow it can be set up so that the search does not have to manipulate the pointer as much (something like going through all the records for each loop) I wish I could find that article, but the result I want is a faster query and recordset creation.
'SQL = SELECT * FROM DATABASE'
my code is:
SQL2 = "SELECT * FROM MUNIVIEW_PARCEL"
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage
Set rs2 =Mydb2.OpenRecordset(SQL2,
Do Until rs2.EOF
If rs2.Fields("APN").value = g_tin Then
'BLA BLA BLA'
End If
rs2.MoveNext
Loop
A question might be 'Would putting the WHERE statement in the SQL string speed this process since the recordset should only contain the one matching record (given that all the records in the file are unique). Currently it builds a recordset of all the records then loops through and selects a match from the recordset. Am I thinking along the correct path or am I just talking turkey?
I read somewhere that by using a "record count" at the begining of the loop that somehow it can be set up so that the search does not have to manipulate the pointer as much (something like going through all the records for each loop) I wish I could find that article, but the result I want is a faster query and recordset creation.
Changing your code this way may help:
SQL2 = "SELECT * FROM MUNIVIEW_PARCEL WHERE APN = " & g_tin
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage \db2cama.m db")
Set rs2 =Mydb2.OpenRecordset(SQL2, dbOpenForw ardOnly)
Do Until rs2.EOF
'BLA BLA BLA'
rs2.MoveNext
Loop
This will help because, as you said, you limit the number of records processed.
Regarding the RecordCount, it can't help!
If your «'BLA BLA BLA'» is always the same, nothing will be faster than this syntax:
"Update MUNIVIEW_PARCEL
Set Field1 = 'NewValue'
Where APN = " & g_tin
SQL2 = "SELECT * FROM MUNIVIEW_PARCEL WHERE APN = " & g_tin
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage
Set rs2 =Mydb2.OpenRecordset(SQL2,
Do Until rs2.EOF
'BLA BLA BLA'
rs2.MoveNext
Loop
This will help because, as you said, you limit the number of records processed.
Regarding the RecordCount, it can't help!
If your «'BLA BLA BLA'» is always the same, nothing will be faster than this syntax:
"Update MUNIVIEW_PARCEL
Set Field1 = 'NewValue'
Where APN = " & g_tin
ASKER
emoreau
I do not have to update the database tables, just set some variables to select item values. In other words, how would and the update code speed up my query? I am querying 80,000 records. Not really a big database but since this table is queryied from a WEB request, it has to be as fast as possible on the server side.
I am trying the SELECT-WHERE code right now. FREQ was the first expert to suggest using this approach. If it works it would only be fare to give him the points.
I do not have to update the database tables, just set some variables to select item values. In other words, how would and the update code speed up my query? I am querying 80,000 records. Not really a big database but since this table is queryied from a WEB request, it has to be as fast as possible on the server side.
I am trying the SELECT-WHERE code right now. FREQ was the first expert to suggest using this approach. If it works it would only be fare to give him the points.
You need to consider 2 things in data retrieval:
-select only records needed (by inserting the appropriate Where clause).
-select only fields needed (instead of saying «SELECT * FROM ...» you can write «SELECT Field1, Field2, Field3 FROM ...» which will only retrieve selected fields)
-select only records needed (by inserting the appropriate Where clause).
-select only fields needed (instead of saying «SELECT * FROM ...» you can write «SELECT Field1, Field2, Field3 FROM ...» which will only retrieve selected fields)
ASKER
Thanks to all the experts that replied.
Update Table1
Set Field1 = 'NewValue'
Where Field2 = 'Criteria'