Can anyone tell me the relative performance of updating a single field in 20 column table using coded loops versus a SQL update query on say a table containing 50,000 rows?
Appreciate any assistance.
Tnx
bginhb
Can anyone tell me the relative performance of updating a single field in 20 column table using coded loops versus a SQL update query on say a table containing 50,000 rows?
Appreciate any assistance.
Tnx
bginhb
It would be simple enough to test but I'd bet on the SQL to out perform the loop. I'm on an iPad or I'd test it now.
I built the code and ran the two alternatives finding that the SQL approach not only was faster but at least 6 times faster. Now I'm trying to get an actual multiple since the SQL approach took 1 second or less to run I want to get the difference between the two running times in milliseconds but Access seems reluctant to provide it.
How can I get the difference between 2 dates/times to display as milliseconds?
Appreciate any assistance.
THere is a link for milliseconds in Access
http://www.devx.com/dbzone/Article/39046
I don't have a db in front of me but I think the Timer function can be used as well.
I found the easiest way was to capture the start and end times using now() in Date variables, then save their values into Double variables. Find the difference and multiply by the number of seconds in a day:
(Late Time - EarlyTime) * 86400 and presto you have seconds to 15 decimals.
Thank you all for your input!