How to speed up your Record Set Processing??

[papasilo]

Hi,

Just wanna ask question if you use Record Set in your module. is there anyway around where you can optimize the speed of the process. like allocate a memory to that specific code to speed up the processing?

I hope you understand what I'm saying . hehehe

VBA Newbie

 

[vbaInet]

If you're not dealing with millions of records you needn't worry about the speed of accessing data. The Jet engine is fast enough. Just ensure that you create correct indexes, PKs and FKs and all should be fine.

 

[gemma-the-husky]

take out anything extraneous from the loop

if you do get slow performance, then test the code without doing anything, and add stuff gradually to try and find the bottlenecks.

eg, if you need to repeat lookups during the processing, maybe consider pre-loading the lookups into an array, for faster access.

 

[bparkinson]

Some other ideas:

Never use "SELECT *....". Always select named fields. Also, select only the columns you need.

Don't use an ORDER BY clause unless you have to.

Don't loop through recordsets unless you have to. If you have to update a bunch of records and can do it set-based, just issue that SQL to the database. That said, looping through an ADO or DAO recordset in VBA is actually pretty efficient, at least compared to an evil CURSOR in SQL Server.

 

[smig]

Some other ideas:

Never use "SELECT *....". Always select named fields. Also, select only the columns you need.

some will say using "SELECT *...." is faster then selecting specific fields

 

[bparkinson]

some will say using "SELECT *...." is faster then selecting specific fields

I'd be curious to know the rationale for saying that. I mean, Jet still has take a look at the schem to process a SELECT *... SQL Statement, doesn't it?

 

[smig]

maybe it's easier for the jet not to look for a specific field

 

[Galaxiom]

One of the commonly seen errors made by novices with processing recordsets is opening a new one each time a value is to be written.

Typically they will have found a piece of code that is a bit "over their head" that has been designed to write a single record. They paste the whole thing into another loop that is processing a different recordset. Most of the time is spent opening and closing the destination recordset.

Another factor that can seem counter intuitive. When it comes to speed, nothing beats a query coming from tables with properly indexed fields.

Keep the recordset as small as possible if you have to use Find or Filter on it. It is generally much, much faster to repeatedly Set a recordset to a new query that returns a small number of results than it is to open a huge recordset once and repeatedly apply a dynamic filter.

Keep reusing the same recordset object as it takes time to create and destroy it. Just Set it to the changing query in a loop and do you stuff.