No Queries Just SQL

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

If I got rid of all my saved Queries and just used SQL would there be
any benifit to to this?
Thanks
DS
 
There are pros and cons either way. In my opinion, the pros and cons pretty
much balance each other out, to the extent that neither approach has a
conclusive advantage over the other, and it comes down to personal
preference.
 
Saved queries run faster than SQL in code because they are partially compiled
upon saving. You also know the SQL is correct since Access syntax checks it.
That said you can build SQL dynamically in code which is very powerful.
Personally, I use a mix of both.

Dorian.
 
Saved queries run faster than SQL in code because they are partially
compiled
upon saving. You also know the SQL is correct since Access syntax checks
it.
That said you can build SQL dynamically in code which is very powerful.
Personally, I use a mix of both.
Click to expand...

Believe it not, pre-compiled queries are not always faster in terms of
performance.
If that query has got any parameters, then building the sql string is
actually faster.

The problem is that query compile time is very small.

When you use in-line sql, you FORCE the query optimizer to re-compile. This
actually means that the JET engine often does a BETTER job when you force a
re-compile each time.

I have experienced this in-line speed advantage over querydefs with
parameters, and to quote from the JET white paper page 18, we get:


<quote>


Check Parameterized Queries for Optimal Performance


Parameterized queries can only be implemented by using a stored query. Since
stored queries have a precompiled query plan, parameterized queries that
contain parameters on indexed columns may not execute efficiently. Since the
query engine does not know the values to be passed in a parameter in
advance, it can only guess as to the most efficient query plan. Based on
customer performance scenarios that we have examined, we have discovered
that in some instances substantial performance gains can be achieved by
replacing a stored parameterized query with a temporary query. This means
creating the SQL string in code and passing it to the DAO OpenRecordset or
Execute methods of the Database object.


</quote>


Note the word "substantial performance gains", and I seen the above many
times...

It is important to point out that use of quires is generally preferred since
to go on a wild goose hunt everytime to change some sql in code is rather a
maintains nightmare. If you can put the sql in saved quires, then you
should.

Of course, I like as you mentioned, you use a mix of both, and that is
really the answer.
 
In addition, (but this goes back to Access 97 and may no longer be as much
of an issue), as a general rule, saved & compiled queries don't contribute
to database bloat as much as having SQL strings as Recordsources,
Rowsources, etc. can.

HTH,
 
Back
Top