Whats quicker - execute query or update a recordset

[davidfc]

I have an application that does a lot of executes with queries on a connection variable to a sql server, and I want to know whether it would be quicker to open a recordset and update fields in the recordset, rather than via the conn.execute update query...any help would be appreciated.
DavidFC

 

[JCruz063]

DavidFC,
I haven't done any benchmarks but I would assume that using simple SQL queries would be quicker as you are dealing with one object only (i.e. the connection object). When you use a recordset, you have add whatever overhead the Recordset object adds to the equation. In any case, you can perform some tests using the Timer function to see how long each method takes to execute, like this:

[COLOR=darkblue]Dim[/color] startTime [COLOR=darkblue]As Single[/color]
[COLOR=darkblue]Dim[/color] elapsedTime [COLOR=darkblue]As Single[/color]
[COLOR=green]...[/color]
startTime = Timer;
[COLOR=green]' Use the query or recordset here
...[/color]
elpasedTime = Timer - startTime

MsgBox "Executing the command took " & _
        elapsedTime & " seconds!"

JC

Oh Wow!!! I was unaware of all the smileys available here at tek-tips.

 

[williamu]

Hi David,

You may want to read the thread thread222-866413 as it appears to be relevant to your situation.

HTH


William
Software Engineer
ICQ No. 56047340