by Ross Mack- GUI Computing

SQL - To Jet or not to Jet

It seems an all too common problem - Database applications still running slow.

It’s ridiculous that in the age of SQL and query optimisers, code never seems to run as fast as it should. Hopefully I can shed some light on the reasons our apps aren’t up to speed, and perhaps demonstrate how to improve performance.

In the day and age of bound controls and whiz bang query generators, like Microsoft Access, we sometimes need to take the time to look under the covers and code in order to speed up our apps. Often we find forms with several controls each bound to its own data control. Sure this works, but do we really need all these data controls each with their own connection to the database?

It is worth considering adding code to populate these controls, rather than simply binding them. This often proves to improve speed in applications with several bound controls and is particularly appropriate for non updatable controls such as comboboxes, Labels, etc.

It is also worth considering, when applicable, the use of snapshots rather than dynasets for these types of controls.

In general it is always important to use a querydef or stored procedure to access the Database. The databases query optimisation process results are stored as part of the querydef. As you will see, this saves several steps in the information retrieval process.

Client Server - Distributed Muscle

At last, with trends heading towards client server and Beefy database servers, we’re beginning to see serious number crunching servers becoming just another variable to be considered in the application development process.

With all this server power at our fingertips we want to offload as much of the work to the server as possible. Hence my three rules of VB Client/Server development: Passthrough, Passthrough and Passthrough

Once again we should avoid the use of bound controls in such applications, because all bound controls use the access database engine therefore adding an extra process layer to the information retrieval process.

To use the SQL Passthrough option on the CreateDynaset() and CreateSnapshot() Methods you must set bit 7 of the intOptions Argument.

eg.

Set dsQueryname = dbDatabase.CreateDynaset(sSqlString, 64)

SQL passthrough passes the SQL string directly to the server. This improves speed in several ways such as:

• allowing the Powerful backend database engine to perform the Query optimisation
• by-passing the access engine
• by returning only those rows which meet your Query criteria

Application performance can be further improved by using the passthrough method with Stored_procedures and by using available server functions in SQL statements, rather than in code for arithmetical and type conversion etc. Yes, I know all you purists who are about to shout "STOP NOW!". In this article I refer to Performance optimisation and I am yet to see the two go hand in hand.

Optimisation Process - It All takes Time

It stands to reason that if we can store results of the following optimisation process with our Querydef, or stored procedure, we can substantially improve retrieval time.

Query optimisation process.

1. Check the syntax of the Sql statement
2. Apply any relevant optimisation rules
3. Check which available indexes will improve query speed
4. Divide the query into Sub queries if needed for performance improvement

Although this may all seem a bit of a generalisation I hope the rules outlined above may provide developers with a different outlook on application design. In the coming issues I plan to delve deeper into the Subject of SQL optimisation in a client server environment.

[HOME] [TABLE OF CONTENTS] [SEARCH]