better to use find method of rs object or sql filtering?

[avikohl]

I notice that people write logn scripts with the find method of the recordset object. Isn't it better to filter with a WHERE clause in the query and test login with value of myRecordSet.eof?

My assumption is that the find method of the recordset object is scrolling throug all the record, which takes longer than sql filtering.

Is my thinking correct on this one?

Is there some advatage to the find method that I am not considering?

Andy

 

[Tarwn]

In my opinion I would agree with you. While it is only a differance of whether the driver is doing the work or the database is doing the work, I would assume that the database would be more efficient about it. I generally do a select for a specific username/password pair and then check for EOF.

At one point I was using the find method to help during a database conversion. Basically I was splitting a single table into three tables (hours table with employee name and job name in text to Job table, Employee Table, and hours table with integer keys). For each record I had to find the correct employee name and job name and then insert a new record into the new hours table using the keys from the new employee and job tables. By the time it finished doing a test conversion on 1000 records it was averaging somewhere around 8.5 seconds per record. As it had to search farther in the employee and job recordsets to find names it took longer and longer. Part f the reason for this is because the recordsets were live, still one connections. So the script kept refreshing their contents. It wasn't possible at the time to make the database do the work itself, but requesting the table data ordered by the text field, dumping it into an array, and using a quick search algorithm brought the average time per record down to a fraction of a second.
What this seems to show to me is that the find method is extremely innefficient. Even if it were to simply do an internal re-ordering of the data and use a more efficient search it could be sped up. Databases on the other hand are much more efficient about searching and are in fact optimized for it.

Just an example that may or may not prove the innefficiency of find,
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[avikohl]

Thank you for your very informative answer.

By the way. How do you measure how long a segment of code takes to execute?

 

[Tarwn]

I was using VB at the time so I was basicly grabbing the value of Now at the top of the code and then inside the loop where I was working I was grabbing a temporary copy of Now and comparing it using DateDiff to the first value. To get the actual average time per record I was grabbing an additional time right before I entered the loop and using DateDiff and the record counter for added records to come up with the avg time it took to find all the relevant data and insert the new record back into the db.

You could do something similar in VBScript if you wanted server-side times.

-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[avikohl]

Cool.

Thanks!