Performance

[bdfigler]

Does anyone know how speed of methods in VBA compares to that of actions in macros? And could an experienced access programmer tell me how a well designed access application should generally look? In other words, are macros used a lot? Queries? Books on access generally have a lot to say about intelligent and efficient design but I feel like these chapters are designed for non-VBA users so I don't know how applicable this information is to me. Thanks. -Brad

ps links are greatly appreciated!

 

[JerryDennison]

As a rule, most good Access application developers rarely if ever use Macros. The primary reason: errors are not trappable. If a macro bombs it can cause the application to simply quit unexpectedly.

 

[JimHorton]

As Jerry said, macros are essentially non-existent in a seasoned Access developer's app. Queries, on the other hand, there are a few schools of thought--some say they clutter and they feel it's better to have all the forms & reports recordsources by sql strings instead of queries, and to build other sql on the fly whererever you can. That's fine and valid, but in my opinion if you stick to a naming convention with them (not just the 'qry' prefix, but extending that to where it's used--form, report, combobox, procedure, etc.) it is an easier app to work with when you have most sql in saved queries.

With queries it's much easier to manage things--you don't need to sit there and open all sorts of forms and reports and modules when you want to make sql changes or to just view it while debugging. When named well (my opinion again here--no friggin' sentences that are darn near paragraphs like "Query that shows all of current month sales for the sales departments morning report&quot you can find them easy and it's not cluttered. There's also the small performance gain from Access not having to parse it each time it's run.

This thread you've started could open up a whole can of worms and it's really too much to go into here, I wish I had a link for you but I don't know of any now. Lot's of opinions on user-interface issues, there are entire books on that subject alone.

One thing I will add--put error handlers EVERYWHERE. Even if you have to take the easy way out and do 'Resume Next' on seemingly innocuous one-line event code fragments--don't EVER let the user see an untrapped run-time error. That's just my thing, I'd rather have the user say "I click the button and nothing happens", then "I click the button and get this nasty error message--is my machine going to crash?!" If you have a handled error,and nothing can be done about it and you have to put up a message, you can always make it more user-friendly than the abrupt, cryptic run-time messages.

--Jim

 

[bdfigler]

Thanks guys -- pretty helpful. I'm curious about the "small performance increase" when using queries instead of SQL that Jim mentioned. If the increase in performance is small, I would rather use SQL statements because that's what I feel more comfortable with. But I've read that the performance increase can be huge if the query has been run and saved. This makes sense except in the case where the underlying tables have been affected since running and saving the query. Any thoughts on this? Also, should I be thinking of Access queries as being equivalent to SQL Server procedures?

-b

 

[JimHorton]

The performance increase would only be notices by queries that run in a few microseconds anyway, since it's a very small hit that is taken. What 's happening is that every time you run sql, Access has to look into it's data-dictionary, make sure the tables you reference exist, along with the field names, then check syntax, then decide on an execution plan.

When you hit the save button after creating a query, the time all of that takes is right there--do you notice a big delay after hitting 'save' when doing a query? Probably not. So a query that takes 25 seconds to run will now take (in the raw sql text mode) 25.01 seconds. Where this is important is in lookups, but here again if you're not using a parameter query, the sql must be parsed again (I'm not fully familiar with Access sql parsing logic, I'm going by what Oracle does, but it should be similar in a general sense).

The case of the parameter query is that the sql is already parsed and saved, so only the parm changes, not requiring a re-parse. However, if you're building a Where clause, or using .Filter method on a form, the sql text is effectively changed, and in Oracle for example, this would be considered an entirely new sql, so it must be reparsed, where as with a parameter, the basic sql structure hasn't changed, only the value of the parameter, which the parser doesn't care about.

And yes, if the tables change then the stored sql is 'dirty', and it must be reparsed.
--Jim

 

[JerryDennison]

Actually, there is a small (sometimes very significant) increase in speed performance when using SQL instead of queries. What Jim was referring to was an increase in performance when using pre-compiled(saved queries) vs. queries that are made "after the fact" in forms or reports. These queries are made when you change the recordsource property of a form or report that was originally based on a table. These are adhoc queries that Access makes on the fly and are not saved except as an object within the form or report. These queries are parsed whenever the form or report is opened. By basing the form or report on an already saved query you're essentially pre-compiling the query and so there's a small gain in performance.

 

[bdfigler]

Jerry, you seem to reiterate what Jim said -- that a pre-compiled query offers a small gain in performance over a SQL statement. But your first sentence says that SQL can yield better performance than queries. Did I miss something? Can you elaborate?

-b

 

[JerryDennison]

A pre-compiled query will perform better than a query that is an object of a form or report. Modularized SQL, that is SQL that is executed as part of VBA code, executes faster than pre-compiled queries. Think of Queries as interpreted SQL (much like interpreted BASIC). Think of executed SQL (i.e SQL that is run using VBA recordsets) as compiled SQL. It runs much faster, in most cases much faster is a matter of degree. If a query runs less than one second and SQL runs 50% faster, you never know the difference. But multiply that by several seconds and you start seeing a difference.

 

[bdfigler]

I see. So there are three options -- (1) Access Query, (2) SQL statement as Form.RecordSource and (3) SQL in a VBA recordset object -- and the fastest of those is SQL in a VBA recordset object. Thanks for all the replies.