• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

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
0
apwbe
Asked:
apwbe
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
You need to execute an UPDATE query like this:

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

Do Until recordset.EOF
 
   'set a variable = to record matches'
  recordset.MoveNext
Loop
0
 
kprestageCommented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
fregCommented:
Dim strSQL As String

strSQL = "SELECT * FROM <tablename> WHERE <criteria>
Set recordset = Mydb.OpenRecordset(strSQL, dbOpenForwardOnly)
0
 
apwbeAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
apwbeAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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)
0
 
apwbeAuthor Commented:
Thanks to all the experts that replied.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now