Using Dynamic SQL versus Parameter Query

[Pat Hartman]

Don't confuse filters with the WhereCondition of the OpenForm and OpenReport methods. In the case of the WhereCondition, the criteria is merged into the RecordSource query and passed through to the server. This is not the case with filters. Filters are applied locally to an already created recordset. Notice when you use a filter, you see the word filtered in the navigation bar. This indicates that you are only viewing some of the available records whereas when you use the WhereCondition, you are viewing ALL the available records.

I have been using relational databases since the early 80's and IMNSHO there are only two valid reasons for using embedded SQL.
1. The query is truely dynamic and the criteria or select clause change at runtime.
2. The selection criteria needs to use a UDF or VBA function that is not available on the server and by using dynamic SQL, I take the hit of binding rather than having Jet retrieve an entire table or worse because it needs to apply my criteria locally.

In reality the performance hit for dynamic SQL is not the real issue. The real issue is the bloat that occurs because Jet needs to create an access plan each time a query is executed. People who use dynamic SQL invariably need to use the compact on close option for their databases in order to keep the app from blowing up to an unmanageable size. I also personally find the use of embedded SQL annoying because I can't play with it as I can with the SQL in a querydef and I can't run it outside of the context of the app for testing. And finally, what masochist wants to type all those data names when you can use the QBE and point and click. In the years between 1982 when I first started using DB2 and 1993 when I first discovered Access, my biggest dream was a tool like the QBE to save me from all the typing I needed to do to write SQL strings in my COBOL programs.

Queries should either be stored as querydefs (preferred) or used as strings in the RecordSource or RowSource properties of forms/reports and list/comboboxes. When used as RecordSources of forms/reports, they should take criteria to limit the rows returned or the WhereCondition must be used when the form/report is opened. If this is not done when converting a Jet application to ODBC, it is quite likely that the ODBC version of the application will end up slower than the Jet version.

The .adp was never widely adoped for several reasons. The two most important being that the .adp was Access but it wasn't the same as anything you did with an .mdb. Converting from an .mdb to an .adp was a nightmare and there was no tool to go the other way. The other reason was that the .adp allowed linking to only SQL Server tables. it couldn't even link to Jet tables!!! let alone DB2, Oracle, or Sybase which seriously reduced its usefulness in a corporate environment. In fact, the first Access application I developed linked to DB2 tables rather than Jet tables. I was hooked

 

[LPurvis]

Hi Pat.
I certainly agree with your opinion on the division of responsibilities of each method. I think the two points you officially list are a good summary of the prime situations for exemption that are mentioned over several posts here and indeed the many threads this has been discussed on other forums.

We must indeed always bear in mind what's going to be parsable - by Jet or an ODBC source (I'd personally only be using SQL Server in such a scenario - but it does of course speak to DB2, Oracle, or Sybase etc.)
It's the developer's responsibility to do so.

Though I personally don't see the on the fly execution plan as always a bad thing - and the slight bloat (certainly by today's standards) is, IMO, quite reasonable as far as file size creep and as you say - combatable with Compact on Close (which I'm not wild about in itself but should likely only be implemented by the FE as needed - i.e. the developer chooses a file size beyond which is unacceptable).

I also fully agree (as have said earlier) about the limitations of quickly manipulating SQL when it's obscured across multiple lines of a VBA procedure. That's one reason why I also like the SQL in tables approach as a better mid ground.

However, in regards to a Jet based solution, I personally don't perceive there to be any confusion between filters and the WhereCondition of the OpenForm and OpenReport methods.
But in fact feel quite comfortable that they are indeed both implementing filters. I'm not sure if I stated as much here or have only done so elsewhere - but it's all about timing.
The point I was looking to make was that filters aren't necessarily the same beast - depending upon when they're implemented.
With a static full table/query bound form sat open, if we apply a filter to that form then we're issuing a relatively inefficient request. (We already had the data there and more!) Applying the filter then sees Access issue a request from Jet for the new results (Jet won't necessarily fetch those from the database though).
Passing a WhereCondition allows Access to show off ;-)
As you mentioned, it doesn't load the form's source and then query again for the filtered set. It includes the incoming filter as part of it's request from the database.
Access is capable of even doing just that if you apply the filter in the Open event of the form (i.e. before it's loaded the recordsource data) - however I trust the timing of that less than the absolute nature of a WhereCondition.

When a form is opened with a WhereCondition - that condition is set as that form's filter property. You can fetch it as such.
I don't know about anyone else - but when I open a form with a WhereCondition - it displays the (Filtered) text in the Navigation bar.
When you remove the filter (having opened the form with a WhereCondition) all records in the underlying table/query are available.
It's a filter. But having been applied in a timely fashion for maximum efficiency.

I, over time, think the word "Filter" has come to be used in the context of "inefficient local pruning of already fetched data".
That certainly can be the case for the unwary.
But Access (and let's not be shy about this - it's Access that is doing the thinking here - not even Jet) is capable of using filters in a more efficient way - if we give it the chance.

As I've already said at fairly great length though...
I don't use filters very much at all. (Unless you're include Whereconditions - then it goes up a bit ;-)

Cheers!

 

[Banana]

I really should experiment a bit more with WhereCondition, but I honestly find it alarmingly too easy for us to pass off a bad query. Having actually looked at the logs from the MySQL, I recall my horrors when I discovered I wasn't just doing a full table scan, but twice for every time I navigated a record. It turns out that I had to be quite explicit with the source; initially I had a combobox functioning in very similar manner to a cascading combobox. I had the code in the necessary events to adjust the source, but the initial rowsource didn't have a WHERE clause and it does nails the table for full scan. Only by setting the rowsource explicitly before the form is even loaded, does away with that unwanted scan. There's probably one more thing, but it escapes me now. Still, the basic point is that it may not be always enough to just having a WHERE clause- you would have to make sure it's there in the start, before the form even gets loaded and is still there all to the end so Jet doesn't hammer the server for a full scan.

In a sense, we could say that Jet is both very intelligent and breathtakingly dumb in how it deals with ODBC sources, and it's the developer's responsibility to know exactly what queries is actually sent to the server as it may not the same one you made in QBE. Thus I've fallen in the habit of testing every queries I pass off to Jet, especially for ones used by any forms before I give it out to my end users compared to when Access was the BE, I just had to looked at the results to verify they were what I wanted.

This is because of that, I've come to strongly prefer parameter queries because I find them much more easier to use and maintain, even if the coding become a bit more complicated (but looking at it again, I probably could have created a UDF to save myself the usual steps of accessing and using a parameter query in VBA).

The only thing I can't really change is Jet's tendency to send AUTOCOMMIT=1 and AUTOCOMMIT=0 between every requests, and that drives me crazy.

 

[Pat Hartman]

One thing that really irks me (for the DBAs viewing this) is that at most of my client sites, the DBA will not give me sufficient permissions to run the SQL Profiler. So rather than me helping myself if I think a query may be inefficient, I need to involve a DBA who starts out by giving me an attitude about Access! I am currently working with one client who not only will not give me permission to create/modify anything in the TEST region but he insists on creating all my table and column names. I end up with table names like: PCR_PCR_WRK_ASOC_PROV and column names like: PPCR_PRV_CHG_RQT_NO. Isn't that swell? Not to mention the fact that they simply up and change something without even telling me so I look like a jerk when the user starts getting strange ODBC error messages.