Speed of Jet vs VBA

  • Thread starter Thread starter antgel
  • Start date Start date
A

antgel

Hi all,

I am experimenting with an order system. Particularly, calculating
discounted prices on the fly, rather than storing them in a table. I love
the idea of doing this - if the processor is fast enough, as it keeps the
data nice and compact. I know there's pros and cons, but this is what I'd
like to do, ok? :)

The discount process is quite involved and I've written a VBA function that
I call from within the Jet SQL. The function takes a couple of previous
fields as parameters, does a couple of ADO Opens, and returns the correct
price. It works, the speed is OK-ish, but still a little disappointing as
the procedure doesn't do _that_ much. My question is, generally speaking,
is it quicker to do things in Jet SQL where possible? (e.g. maths
functions) If so, I'll take another look and see if I can streamline it.

Antony
 
Allen said:
Yes, if you are able to perform the operation in JET SQL, it will be faster
than VBA.

The mathematical side of VBA is no problem, so by all means use a function
for that. However, opening a recordset in VBA is slower than using a
subqeury within your SQL statement.
Click to expand...

I thought that was true too until a couple of recent issues I had. Testing showed
that VBA may be an better performing alternative in some cases. I'm sure my
circumstances were unqiue - my advice is to consider both solutions if the SQL
version doesn't perform at an acceptable speed. You may be able to eliminate the
subquery in code doing a single pass through a recordset. Only testing will tell...
I agree that *generally* Jet SQL is *fast* but don't exclude testing a coded
solution where performance is unacceptable with Jet SQL.
 
Antony,
Slow as VBA is compared to some other approaches,
you CALCULATION speed is trivial compared to the
'ADP Opens'. Anything you can do to optimise those out
will improve speed. Perhaps you could cache the values,
or use persistent recordsets, or add the tables to the query
and declare your function with many input parameters.

Also, Jet will generally call each VBA function at least twice,
so simple caching in your function ("if all the parameters are
the same as last time, just return the same value") can make
a big difference to speed

(david)
 
Actually, it is not so cut and dry.

It was also fairly recent for me that I changed a simple sql update to a dao
"loop". The results in speed where about he the same. In fact, the dao loop
was slightly faster.

For sure, usually a sql statement is faster, but often there is little
difference here. However, if you have a choice, sql is less code, and of
course if one ever updates to a server, then you can used stored procs.


To the oringal poster:

The speed of JET is incredible. However, the time taken to open, and create
a recordset is thousands of times slower then a loop to process data.

In other words, opening some table and creating a recordset IN SIDE OF a
loop is the death to performance.

Often, these kinds of "loops" can be changed into a single sql statement
that does a relational join between two tables, and does the update. The
result is something that runs MANY times faster. In other words, VBA looping
code, and sql execute code is not that much differences. However, as
mentioned, if that loop has to open a recordset inside, you are duck soup,
and things run slow.

So, it is not the speed of the code...but that huge penalty hit when you
open a reocrdset, and grab the record...

Thus, often the solution is to pre-load that reocordset that needed for the
loop. So, if the 2nd recordset needs to be loaded 30 times (say, once each
day for a month of day reporting, then you are MUCH better off to pre-load
ONE recordset with 30 days of data, and do a find first on it. However, you
have to use caution with this approach, since if the recordset gets two
large, we all know that find first does NOT use the index. For anyting less
then 10,000 records, you are FAR better off to pre-load the 2nd recordset
with the data.
 
Hi,


In theory, you should avoid using a VBA function in a query, it is not portable, but if you
need them, use them, but avoid using them as replacement of what SQL should do.

With JET 4.0, the VBA functions you use in Jet are called just "as required". As example, if
you look at the data view, and see only 20 lines, out of 60 records, and you VBA function is not
involved a WHERE clause, Jet will call your VBA function only 20 times, not 60. Furthermore, if you
page down to see the records from 21 to 40, then page up again to see back records 1 to 20, you may
note that the VBA function has been recomputed, showing that a VBA function is executed "as
required", but just at the moment it is required, not before. Thus, there is no reason that a VBA
function should slow down your query.

SQL is generally faster on normalized tables if you take into account the time to create and to
destroy the recordset (thing you must do, if you use VBA). SQL is slower in problems where you know
something that SQL don't, so, you can device an imperative method (VBA) that will beat the
declarative method (SQL), the last one being limited by what it knows of the result to get, and the
kind of data at hand. Remember that SQL is adaptative, dynamic, self re-programming. In VBA, you
generally code ONE way to solve a problem, one you guess to be typical. In SQL, you do not say HOW
to solve the problem, like in VBA, but you say the result you want, and let SQL decides the method
to apply, from the data at hand (small or large table, indexes or not, what loop will be the outer
one, ... ) and from the statistics (more precisely, the information available at compile time).
Note that on the other side, too much normalization may slow SQL too.

I would not suggest to open a recordset of thousands of record and then, pick up the record you
want with a move first, in most circumstances. I would rather suggest to keep your recordset as
SMALL AS POSSIBLE, using a maximum of constraint in a WHERE or through a JOIN, since even slower
that the hard disk is the Ethernet cable, and less you use it (to transfer tons of useless data),
better you are.

You mention your formulation was complex and was needing VBA. Maybe, but you didn't tell what
it was. Do you know you can use INNER JOIN instead of LOOKUP, that the operator BETWEEN is useful
to extract value in a range, etc. Maybe you would be surprised how easy it may be in SQL, given the
right table design. After all, you have a database at your hand, it is to use it. Just as example,
if you have a SELECT CASE statement, in VBA, that is probably easier to put it into a table! And
values in a table can be changed without opening YOUR code (so, you can more easily export your
application to many clients, without having to tell them to open YOUR code to accommodate each of
their specific needs). So, what is your "complex" computation?



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top