Why is my SQL Query almost Dead?

Even with Visual Basic 4.0 a lot of people still need to ask the question "Why do my data access queries take longer to return than fossilisation?". The reason for this is still due to a lack of understanding of what the DAO/RDO/RAO/ODBC mix is and how to make use of it. What a number VB developers need is a simple guide to performing optimised SQL Queries in VB, and an indication of when should they use each of the data access components.

Which Components should I use?

This is a question which really should be asked before you start development using any of the database components in VB 4.0. What you need to consider is the following points:

Where is my data located?

Having your data located on the local machine (or a simple network file share) almost makes it a no brainer to decide what you will use. In this case go right ahead and use DAO. The object layer was designed with the stand-alone machine access and makes the programming aspects very easy. If your data is sitting on a server database (like SQL Server) then you should look at using RDO.

Will the database have to support a large number of simple transactions?

Your immediate response is probably "What do you mean by simple?". The return of simple results sets, the use of stored procedures or sequential SQL queries based on user provided results, all qualify in my definition of 'Simple'. In this case make use of RDO. It will give you the ability to handle the returns of stored procedures without the gymnastics required in DAO. It will also allow you to make use of query analysis tools like the SQL Enterprise Manager to tune the performance of your remote SQL.

Do I have to handle complex queries or a 3-tier logic?

If this is the case then you should be using RAO. The Remote Automation Objects will allow you to place complex business logic at the server end. The performance in this case is also improved because there is no need for the database result sets to 'hit the wire' on the way back to the client. It also allows you to create procedural programs based around SQL (which, as anyone who has done SQL programming will tell you, is a fast track to a stressful life) that provide a more functional return than a stored procedure can handle.

I need to have a high level of security?

It's a 50/50 call on RDO & RAO here. RDO will allow you to make use of the full security environment in the server (technically this is very cool under SQL Server as it supports the Windows NT integrated domain security model and allows automated logons to the server, if the client has been validated in the domain). RDO will also allow you to make use of the peculiar security models of some leading database servers like Oracle.

In the case of RAO it provides the developer with the ability to implement any security model they feel like. It would be possible for a developer to create an RAO that integrated with a kerberos security system and provide encrypted data returns to the calling application. Basically RAO gives you the ultimate flexibility in the control of the system access.

Optimisations

Once you have selected your database access method then you need to keep your eye on the basic optimisations that you can perform. These fall into two main categories:

• Standard data access optimisations
• Access method specific optimisations

Here are some optimisations you can look into (they are not presented in any given order):

• In general you should try to ensure that you perform your SQL queries on the interactive SQL tool and have a look at the execution plan. This will tell you if you are doing sub-optimal operations (like table scans or if you need another index). When using DAO you will need to use a product like Total Access Analysis by FMS to have a look at the way the query effects the Jet engine.
• Where possible you should define your queries to use forward scrolling read only result sets. This allows the server to perform the fastest possible result set handling, and ensures that your query creates the smallest footprint on the server.
• Use stored procedures where possible. These execute far faster than dynamic SQL, which must be compiled and executed on the server.
• Break your SQL query into smaller sub queries to help the engine perform a faster return. Remember that the more processing your system needs to do, the more effect your query will have on the other users (this can be especially useful on synchronous engines). The Jet engine is still synchronous - which means that it can do only one thing at a time. Even though VB 4.0 32 Bit now supports multi-threading, the Jet engine cannot take advantage of that.
• Under RDO try to make use of rdoPreparedStatement object and then perform subsequent queries by changing parameters and using the Refresh method. When you do this, however, the caveat to remember is that if you want to create a new object, the RDO layer does not delete the old one and so you can find yourself rapidly filling up available memory if you don't delete old objects (this is pretty much true of all objects - it's a good rule to delete when you are finished except for performance reasons).
• When using ODBC make sure that you have the parameters in the ODBC.INI file set correctly. I spoke to a developer just recently who had the debug traces turned on and that caused his query to take 4 times as long. You may also incur the wroth of the DBA, by filling up the tempdb table with trace/debug information on some platforms.
• If you can have the UI support it, make use of the TOP predicate to allow users to check result sets before performing the entire query for reports or extractions. Many of us are familiar with users who will select options - then go to print preview to see if the report results are correct. Generally each time they do this they will generate a new SQL query.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]