Which is faster: DoCmd.RunSQL or Recordset manipulation 1

[ecannelora]

here's some background:

I've been working on a db for about 15 users. I have a backend on the network and the mde with forms, code, etc on each users machine.
I have 19 linked tables with relationships and a few native tables for temporarily holding data.

At first, all of my forms were bound to queries. If it was an input form, the query would SELECT on PK (a member number) WHERE the PK is null, that way the form doesn't bring back any records, but when data is put into the form it will update the right table.
My display forms were bound to queries that would have joins and whatnot, pulling in all of the info I want to display.

The database ran intolerably slow.
So I decided to try unbinding all of my forms. For input forms, I open a recordset based on the target table and I just add the data from the forms to the table.
For display forms, I open the recordset from wherever and populate the text boxes.
The initial load time of my main display form seems to be about the same as before, but once it's open in a session, I don't close it, I just hide it, and when it receives focus again, I clear all of the text boxes.
Doing this seems to have helped great deal.

Here's my first question: is it generally better to handle record retrieval and editing using recordsets in VBA rather than building queries?

My other question:
in VBA, what's the quickest way to update records? Should I use DoCmd.RunSQL, or should I open a record set and edit as needed?

 

[RoyVidar]

#1 - generally recordsetapproaches are slower than queries

It may be a design quetsion, when "loading" a bound form (bound to a table), you'll most likely load the whole table, whilst using a query, you can limit the number of reocords returned (faster), or using a recordset fetching only a few records.

#2 - Neither the runsql method of the docmd object nor recordset, use the .execute method of either the dao database object or the ado connection (though the runsql method is probably faster than recordset).

Here's a couple of links to performance issues
Microsoft Access Performance FAQ
Making Access Faster - 168 Tips to Speed Up Your Apps!

Roy-Vidar

 

[TheAceMan1]

How are ya ecannelora . . . . .

[blue]is it generally better to handle record retrieval and editing using recordsets in VBA rather than building queries?[/blue]

The [purple]Microsoft Jet Engine[/purple] which can be considered the hub of access, is [purple]optimized for Query/SQL[/purple], [blue]VBA[/blue] is not. Always go with [purple]Query/SQL[/purple] unless something dictates otherwise. The [blue]greater the recordcount[/blue], the more [purple]Query/SQL[/purple] will shine for you . . . and [purple]by a considerable margin![/purple]

[blue]in VBA, what's the quickest way to update records? Should I use DoCmd.RunSQL, or should I open a record set and edit as needed?[/blue]

Same answer as above! Also . . . [blue]recordset generates more code[/blue] as you have to include the SQL along with it and format it properly for VBA.

Also, queries in the query window which have been run at least onnce since last updated are [purple]precomplied![/purple]

Again . . . if nothing dictates otherwise, use [purple]Query/SQL . . .[/purple]

See Ya! . . . . . .

 

[willir]

Fantastic link Roy.

I had read the Granite site stuff, but the FMS white paper was new to me. Thanks!

 

[ecannelora]

thanks guys, I'll check out the links

 

[ecannelora]

so I've been thinking about this some more. There's no question that the changes I made improved the performance. And if it's not because I'm using recordsets instead of queries, than what can it be? Maybe because I'm opening my main form in the startup, and then hiding it after that instead of closing it?

Also, if I end up with 20 or 30 queries, is that still less detrimental than doing stuff with recordsets within VBA?

 

[willir]

Depends on the VBA code. For example, SEEK will outperform MoveNext and Find. But to walk through a data set can be slow. I improved one problematic form by 5 to 10 seconds by changing the code from VBA to a SELECT statement.

Openning the form and keeping it hidden is not a bad idea. The initial load time of the database will be slower, but the time to "open" the form will be much better.

Richard