DAO versus ADODB speed

[CedarTree]

Hello -

I often, for a quick data point lookup, use DAO, such as:

Dim sql as string, qdf As DAO.QueryDef, rst as DAO.recordset
sql = "SELECT BirthDate FROM tblPerson WHERE Name = 'John'"
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = CONNECTION_STRING
qdf.ReturnsRecords = True
qdf.sql = sql
Set rst= qdf.OpenRecordset
Etc...

When looking up 1 or 2 data items, this is reasonably fast. But when doing a loop and looking up 1,000 data items, it gets slow.
Is there a faster way using ADODB to set the "rst" above?

Right now, I actually set rst = fnServerRecordset(sql) where that function does the basic coding shown above - just to lessen the coding needed for several lookup functions. Can I call a function in the same way with ADODB? I know I've used ADODB (using connection, command, etc.) to upload data to a server much faster than sending a sql INSERT command. So I'm thinking data requests can also be sped up? Sample coding help would be MUCH appreciated!

Thanks!!!

 

[theDBguy]

Hmm, are you saying you are opening a filtered recordset (possibly a single record resultset) multiple times using a loop?

 

[CedarTree]

Yes - I'm building a complex report in Excel and grabbing different data elements as needed. If ADODB lookup is 50% faster (0.25 seconds versus 0.5 seconds), that would add up I guess.

 

[theDBguy]

Yes - I'm building a complex report in Excel and grabbing different data elements as needed. If ADODB lookup is 50% faster (0.25 seconds versus 0.5 seconds), that would add up I guess.

I'm not sure about the 50% increase in speed. I was just wondering if it was possible to pull the entire data set once and then loop through it for your report. Have you tried that? I was thinking the back and forth trip to the server must be time expensive, so perhaps you could save some time by reducing the number of trips to the server.

 

[The_Doc_Man]

I would think that the processing load there is the frequent make-n-break connections, because if I understand system data flow correctly, every one of those actions has to release and regenerate file and network locks. Because of the network interactions involved, that will not be very fast. If you could figure a way to keep that connection open and just repeatedly do the DAO equivalent of

rs.FindFirst "Name = '" & [SomeField] & "' "

that ought to be significantly faster.

EDITED slightly to correct poor explanation.

 

[Pat Hartman]

I would never use a findfirst in a recordset. I would always use a query with criteria.

Is the BE ACE or SQL Server. If it is ACE, you should stick with DAO. If it is some other RDBMS, you might find a speed improvement with ADODB.

However, queries are always faster than code loops so I would use them to sort/summarize/update rather than a VBA loop every time. You also need to avoid domain functions in queries AND in VBA loops. Left Joins can almost always substitute for domain functions.

 

[GPGeorge]

Every problem begins to resemble a nail.....

There's an old aphorism about that. I remember when I first felt like I had learned enough about DAO recordsets to be comfortable using them in production. Almost overnight EVERY solution turned into a recordset in VBA. After a few months I realized that, as Pat says, sometimes there's a better, more efficient way to do many things, even if they aren't as sexy. While I've seen discussions that recordsets can be pretty fast in some situations, I'd join in recommending that you do this with parameterized queries because that's the quickest way to a workable solution.

 

[Minty]

Where is the data stored?
If it's SQL Server you could create a Stored Procedure / View to return the entire dataset you need in one fell swoop and then just pull the data directly from one source?

 

[Pat Hartman]

In the case of very complex reports against SQL Server, I have also on occasion used sp to collect the data for a report. It's not my go to solution. I would try views first. But when the report is too slow, it could end up being the fastest solution because it allows you to create temp tables without bloating your database.

 

[The_Doc_Man]

I would never use a findfirst in a recordset. I would always use a query with criteria.

Is the BE ACE or SQL Server. If it is ACE, you should stick with DAO. If it is some other RDBMS, you might find a speed improvement with ADODB.

However, queries are always faster than code loops so I would use them to sort/summarize/update rather than a VBA loop every time. You also need to avoid domain functions in queries AND in VBA loops. Left Joins can almost always substitute for domain functions.

Pat, my suggestion was actually intended as a way to keep open the recordset rather than make-n-break it for every iteration. Note also I suggested doing the "DAO EQUIVALENT" of what I showed. It's the same concept as keeping a hidden form or recordset open behind the scenes so that the back-end connection stays usable and you don't have open and close so much.

Since the OP's problem is piecemeal building of a worksheet in Excel, it is not clear that you could easily do this with SQL. I fully understand why you don't like VBA loops. They're based on emulated code so will not be efficient. But the process of building items in a spreadsheet won't be efficient either.

 

[Pat Hartman]

Back in the days of the mainframe before there was SQL, it was all about 2-file matches, 3-file matches, and I even once did a 4-file match. Where you started with 2 or more files sorted on some common field. Then you read file 1 and then read file 2 until you got to the value that matched the first in file 1. you matched until the record in file 2 was greater then the one in file 1. Then you read file1 until it caught up with file 2, ad nauseum. This is soooooooooo much easier with a left join I couldn't have even imagined how SQL would save me so much processing logic. I talk about dreaming of a tool like the QBE to eliminate the tedium of typing SQL but what joins did was simply so beyond what i could have envisioned, I never even imagined them. With joins (usually left but not always) you get the query engine to pull together all the data into a single row for you. Complex matching logic now becomes trivial.