?
Solved

Performance: VBA case VS Recordset.filter

Posted on 2005-04-27
14
Medium Priority
?
392 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:DaFou
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13878406
Is this a homework question?
0
 
LVL 2

Author Comment

by:DaFou
ID: 13878473
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 13879786
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:DaFou
ID: 13879835
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 1200 total points
ID: 13879930
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 13883618
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
 
LVL 2

Author Comment

by:DaFou
ID: 13885577
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13885705
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
 
LVL 8

Expert Comment

by:wraith821
ID: 13893585
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
 
LVL 2

Author Comment

by:DaFou
ID: 14182087
wraith821, stored procs in an access environment? Please teach me more
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 14182171
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 14182194
jimhorn,

He is not asking, he is just making fun of wraith821.
0
 
LVL 2

Author Comment

by:DaFou
ID: 14182210
leon is semi correct, I am not asking here. I am merly cofused
0
 
LVL 8

Expert Comment

by:wraith821
ID: 14183593
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question