Speed question, .seek vs. SELECT

[jmswe]

Hi!

Im collection multiple, specific, values in a table. (Not the entire table.) using a for next loop in VBA

Which is faster?

Using .seek to find the key of the item im searching for and then reading in the data,

or doing a new openrecordset with a changed sql statement for every loop of the loop?

 

[jmswe]

Addition:

If i use select: Should i read in the entire table and search "manually" by looping through all records or should i search by the sql statement? (new statement for every record i want to read)

 

[cheerio]

If you are searching on an indexed field the database engine should use the index and will take the same time however it is asked.

If you want to change records using SQL then use an UPDATE command not a SELECT. eg UPDATE Salaries Set Salary = 2*Salary Where Firstname = 'Ken'

Ken

 

[jmswe]

Ken,

Thanks for your answer.

Wy question was somewhat unclear...

Ill try again:

Is it faster to go through the entire table to find the record with the key you are looking for.. (maybe 100 different record in a loop)

Or performing a sql search a 100 times, one time for each specific record im after?

 

[cheerio]

If I have two tables EMPLOYEES with ID, currentsalary fields and PAYREVIEW with ID, newsalary fields I can create a query that joins the two tables and updates EMPLOYEE current salaries with the new salaries. It is not necessary for every employee to be in the PAYREVIEW table.

Assuming ID is a unique key field in both tables such an update query will be very fast because the database engine is optimised for this sort of activity.

If I have VBA code that is doing the update one person at a time, (say asking the human resources director what the new pay should be for each one) then the update time will be very similar whether you use a SEEK or whether you use a SQL update query for a single record. At the end of the day the engine goes through exactly the same process of looking in the index to locate the record and then processing the record for you. Whether you use DAO code, ADO code or you craft SQL depends more on what you feel most comfortable with.

The issue is slightly less clear cut if the table is remote such as being in a SQL server database. I would probably then do the updates using SQL pass through queries created in code as this might get better results from the remote engine.

Ken