Link to home
Start Free TrialLog in
Avatar of apwbe
apwbeFlag for United States of America

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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

You need to execute an UPDATE query like this:

Update Table1
Set Field1 = 'NewValue'
Where Field2 = 'Criteria'
Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
recordset.MoveFirst

Do Until recordset.EOF
 
   'set a variable = to record matches'
  recordset.MoveNext
Loop
Avatar of kprestage
kprestage

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
Avatar of freg
freg

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of apwbe

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.mdb")
Set rs2 =Mydb2.OpenRecordset(SQL2,dbOpenForwardOnly)
           
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.mdb")
Set rs2 =Mydb2.OpenRecordset(SQL2,dbOpenForwardOnly)
             
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
Avatar of apwbe

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.
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)
Avatar of apwbe

ASKER

Thanks to all the experts that replied.