'Preparing' SQL Statements And Specifying SQL Statement Time-outs

Overview

This page deals with the advanced DB access topics of Prepared Statements and custom SQL Statement time-outs. Please note that both are used and needed only in special circumstances.

Prepared Statements

'Preparing' a SQL query lets the software engineer optimise the performance of frequently issued queries.

What a RDBMS does when it encounters a 'Prepared' SQL query is that it 'caches' the 'query execution plan' and uses this cached information in subsequent calls. Since the creation of a query execution plan takes time, and since spending that time is no longer needed in subsequent runs of the same query, it makes subsequent runs of the same SQL query faster. While the caching of the 'query execution plan' takes a bit of time when the SQL statement gets 'prepared', this is one-off and pays off with subsequent calls to the same query.

A SQL query that uses Parameters and gets 'prepared' is treated as 'prepared' even with varying values for the Parameters and hence fully benefits from the speed-up that a Prepared Statement can yield! This is in contrast to SQL queries whose query strings get constructed 'on-the-fly' (by string concatenation) and which don't use Parameters - 'preparing' such SQL queries does not only not yield better performance, but worsens performance as the caching of the 'query execution plan' takes a bit of time and that time is lost as there is no benefit to be reaped from the caching.

Prepared Statements are not supported by all RDBMS, but should simply be executed in a non-prepared way in case a RDBMS doesn't support it. PostgreSQL definitely supports Prepared Statements.

Customising SQL Statement Time-outs

Every DbCommand that is run against the DB has got a time-out associated with it. If the command execution takes longer than the time-out, the ADO.NET provider for the RDBMS that we are connected to will throw an Exception.

In case of the PostgreSQL RDBMS we use the Npgsql ADO.NET provider, and this throws an Exception when a command takes longer than the default, which is 20 seconds: Npgsql.NpgsqlException: ERROR: 57014: canceling statement due to statement timeout

When a single SQL query takes longer than 20 seconds on a machine where both the PetraServerConsole.exe and the RDBMS are located on the same machine (which is the normal deployment scenario),

• it points to either a rather inefficient SQL query (perhaps it needs to use one or more Indexes for better performance), or
• the SQL query is simply attempting to do 'too much in one go' and should better be split up into several SQL queries that achieve the same.

If you can't optimise the speed of the SQL query and can't break down what the SQL query does and you are running into Exceptions because of Command time-outs then the time-out should be manually increased for that one query. See below on how to do that.

Prerequisite for Prepared SQL Statements and SQL Statement Time-outs: Using the DBAccess.GDBAccessObj.SelectUsingDataAdapter Method

Call the special DBAccess.GDBAccessObj.SelectUsingDataAdapter Method to execute the query. It has got the optional Arguments APrepareSelectCommand and ASelectCommandTimeout.

• Pass in true into APrepareSelectCommand to make the query a 'prepared' query.
• Pass in an int value higher than 20 into ASelectCommandTimeout to increase the SQL statement time-out.

The Method will return the number of Rows successfully added or refreshed in the DataTable passed in with the AFillDataTable Argument.

Also, it will provide you with an instance of the TDataAdapterCanceller Class in the out Argument ADataAdapterCanceller. See Stopping of Running DB Queries for details.

Read the XML Documentation of the DBAccess.GDBAccessObj.SelectUsingDataAdapter Method and check where it is used so far for details on how to use this Method correctly!

Restrictions

Supported Only for Queries That Are SELECT Queries

The DBAccess.GDBAccessObj.SelectUsingDataAdapter Method can only be used for SELECT SQL queries, but not for other types of SQL commands, e.g. UPDATE or for scalar-type queries.

Only Available When Using the DBAccess.GDBAccessObj.SelectUsingDataAdapter Method

At the time of writing (March 2015) no other Methods of the TDataBase Class can prepare a SQL query or can alter a SQL statement time-out. This means that our 'Typed DataStore' Methods (eg. PPartnerAccess.LoadXXX) can't utilise those features either.