While I was looking at bitsqueezer's lengthy contributions to this Q
, I was looking to test his assertion about the use of DCount being faster than other methods (BTW I don't agree with his assertion, but that's a different discussion) The form I started to look at didn't have the total records bit that I was think was there, but it's a weighty form with a perceptible pause in navigating so I figured I look to optimize its Current event.
There were a couple of DCounts in there and I eliminated them in favor of a "Select Count(PK)..." and testing with a block like
Dim TheTime As Single
TheTime = Timer
'code to test
TheTime = Timer - TheTime
And the recordset code was faster.
And then I broke down what parts of the event were taking up all the time.
The majority of it was being chewed up by tweaking the SQL on a saved passthrough query, running it, and mucking with controls based on the results. So I had a look at the sproc behind it, and tweaked that a little.
Still, it was about a quarter-second to get that all done.
So I figured, what the hell, let's hash out the ADO code to bypass the need to tweak the saved query and execute it in favor of ADO just getting it done. Let's see what we get. MS suggested DAO these days, things are going to be comparable, right?
Yeah, not so much!
Where the DAO code took about 25/100ths of second to execute, the ADO code to 2/100ths
That's a full order of magnitude faster!
Is that to be expected? Is that typical? Most folks go ADO or DAO and never mix the two.
Who else has similar experiences?