Performance: VBA case VS Recordset.filter

Ola,

What would be faster in terms of procedure excecution time?

Scenario 1
Select all 3000 records in 1 recordset and loop through them in VBA and do a Select Case on some field for each record. If value is A then do some statements or if value is B then do some other statments

or

Scenario 2
Select all 3000 records in1 recordset and set a filter on some field to value A and loop through all records and do some statments and then reset the filter on some field and set te value to B and do some other statments

or

Scenario 3
Select all records in 1 recordset with some field having value A and do some statements and then clear the recordset and reselect all records in 1 recordset with some field having value B and do some other statments

Also please explain why you thins that is
LVL 2
DaFouAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
I would go with Scenario 3, unless bandwidth is a real issue.  With this you have the smallest recordset and you are not testing values.  Depending on what is beign done, you may be able to do it with SQL while the data is retrieved as well.

Leon
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Is this a homework question?
0
 
DaFouAuthor Commented:
No, this is not homework for some school.

I am on an important project right now and dont want to spend time on performance testing while I have alot of work to do still.
I personaly think scenario 3 is fastest but when this question does not provide me with the confirmation I need I will go performance test all 3 scenarios myself using the Timer function.

I am urgent thus the 500 points
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Gustav BrockConnect With a Mentor CIOCommented:
I would believe #2 as it is always wise to operate on the smallest possible recordset.

However, it will mostly depend on how your indexing is carried out.
On the other hand - and just to confuse you - 3000 records isn't really that much - even looping through all records with no index at all in VBA should take no more than a few seconds.

/gustav
0
 
DaFouAuthor Commented:
lets expand this idea.

instead of 3000 records there are 30k records. and there is an index set on "some field"

how will this affect the problem while you beleive having the smallest possible recordset is fuitful? and in such case I am still thinking of scenario #3

please elaborate
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Well, if we were dealing with 300.000 or 3 mio. records, still 30.000 records isn't that much.
In such a case I would use the method that I feel will fit best in the scenario and only if performance turned out bad investigate other methods.

On the other hand, why guessing? Setting up a full test scenario for all three methods can't take long.

/gustav
0
 
DaFouAuthor Commented:
I tested the scenarious in the mean time. With 3000 records scenario 1 is by far the fastest.

Scenario 2 is always crappy as the filter does not update the recordset on the fly, rather one needs to create a new recordset off the firstrecordset having the filter. THis is not helping.

Scenario 3 will probably be faster with a alot more then 3000 records but I have not have taken the time to determine where the break even point with scenario 1 lies.

I am going for scenario 1 for now
0
 
Gustav BrockCIOCommented:
Thanks for the feedback.

ad. 2: That's right, I've forgot that. It's not as for a filter you apply to a form.

ad. 1: VBA is fast. Many - indeed ASP guys - can't imagine any record handling without calling a query. Recently I wrote some routines for reading text files to update some recordsets in a couple of nested loops. Running these on today's GHz machines hardly takes any time.

/gustav
0
 
wraith821Commented:
the fastest way to do that is to write 2 stored procedures that perform the "A" function and then one that performed the "B" function or write one that does both, or one that accepts a parameter of "A" or "B" and does the apropriate functions. It is always better to do your processing on the server, if that is possible. If the functions are just manipulating the data. Your server (in most cases) is faster then your machine. it will eliminate the retreival of the records from the server. That is where you get into your processing time. in your script you just call the stored proc(s).
0
 
DaFouAuthor Commented:
wraith821, stored procs in an access environment? Please teach me more
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
DaFou - You have already accepted an answer to your original question.  If you have another question, please ask it in the form of a new EE question as opposed to adding on to this one.  That way, more experts will respond.

-Jim
0
 
leonstrykerCommented:
jimhorn,

He is not asking, he is just making fun of wraith821.
0
 
DaFouAuthor Commented:
leon is semi correct, I am not asking here. I am merly cofused
0
 
wraith821Commented:
HAHA! I could have read. I'm used to being in the VB Databases where its mostly SQL Server stuff, and if you knew anything at all, you would know i was thinking it was SQL Server or MSDE.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.