'Preparing' SQL Statements And Specifying SQL Statement Time-outs: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
(Updated out-of-date information)
Line 18: Line 18:


It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.
It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.
====Utilising 'Prepared' Statements====
To use Prepared Statements, call one of the Methods of an instance of the <code>TDataBase</code> Class (e.g. <code>DBAccess.GDBAccessObj</code>) that has got an <code>APrepareCommand</code> or <code>APrepareSelectCommand</code> Argument. (Those Arguments always default to false.)


===Customising SQL Statement Time-outs===
===Customising SQL Statement Time-outs===
Line 30: Line 34:
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.
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.


It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.
====Utilising Customised SQL Statement Time-outs====
To customise SQL statement time-outs, call one of the Methods of an instance of the <code>TDataBase</code> Class (e.g. <code>DBAccess.GDBAccessObj</code>) that has got an <code>ACommandTimeout</code> or <code>ASelectCommandTimeout</code> Argument. (Those Arguments always default to -1, which means the default timeout of 20 seconds will be used.)


==Prerequisite for Prepared SQL Statements and SQL Statement Time-outs: Using the <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> Method==
Pass in an <code>int</code> value higher than 20 into one of those Arguments to increase the SQL statement time-out.
Call the special <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> Method to execute the query. It has got the optional Arguments <code>APrepareSelectCommand</code> and <code>ASelectCommandTimeout</code>.
* Pass in <code>true</code> into <code>APrepareSelectCommand</code> to make the query a 'prepared' query.
* Pass in an <code>int</code> value higher than 20 into <code>ASelectCommandTimeout</code> 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 <code>AFillDataTable</code> Argument.


Also, it will provide you with an instance of the <code>TDataAdapterCanceller</code> Class in the out Argument <code>ADataAdapterCanceller</code>. See [[Stopping of Running DB Queries]] for details.
==Notes==
 
It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.
Read the XML Documentation of the <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> 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 <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> 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 <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> Method===
At the time of writing (March 2015) no other Methods of the <code>TDataBase</code> Class can prepare a SQL query or can alter a SQL statement time-out. This means that our 'Typed DataStore' Methods (eg. <code>PPartnerAccess.LoadXXX</code>) can't utilise those features either.
 
==Historic Information: How this was done before [[Co-ordinated_DB_Access_(Thread-safe_DB_Access) |Co-ordinated DB Access]] got implemented==
While it has previously been possible to call the public <code>PrepareNextCommand</code> and the public <code>SetTimeoutForNextCommand</code> Methods of the <code>TDataBase</code> Class, that option had to be removed as the calling of these Methods is not working reliably in the light of multi-threaded DB Access (they each set a Field of the <code>TDataBase</code> Class to a certain value, and that value could get picked up by a Method that executes on another thread than the thread that set the values!).
 
To achieve thread-safety the <code>DBAccess.GDBAccessObj.SelectUsingDataAdapter</code> Method was written. This Method guarantees thread-safety as it 'wraps' the calls to the now private Methods <code>PrepareNextCommand</code> and <code>SetTimeoutForNextCommand</code> in a thread-safe scope.
 
Should the options for using Prepared Statements and custom SQL Statement time-outs be extended to other means of DB access, similar 'wrapper' Methods would need to be written.




==Questions?==
==Questions?==
In case you have questions regarding the features that get explained on this page please contact ChristianK, the developer of those features.
In case you have questions regarding the features that get explained on this page please contact ChristianK, the developer of those features.

Revision as of 14:22, 20 July 2020

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 calculation 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!
The 'prepare' step itself is always performed before an SQL query is executed. As you don't want that step to be repeated each time if you are executing SQL statements in a loop you'll need to 'prepare' the statement before you enter the loop.
As long as the cursor is not marked as 'finished or closed', the cached execution plan continues to be valid.
If 'finished/closed' SQL queries are called again, they are treated as 'unprepared' SQL queries and therefore are significantly slower, as each time the 'prepare' step must be performed again.
Therefore all SQL queries that are called within a loop or with changing parameters should always be 'prepared' statements with their 'finish' clause after the point where the loop has ended.

query->prepare()
start loop

query->execute()

end loop
query->finish()

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.

It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.

Utilising 'Prepared' Statements

To use Prepared Statements, call one of the Methods of an instance of the TDataBase Class (e.g. DBAccess.GDBAccessObj) that has got an APrepareCommand or APrepareSelectCommand Argument. (Those Arguments always default to false.)


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'.
    There are of course plenty of ways to optimize queries - next to splitting the query into smaller parts - and make the DB’s optimizer choose the right method
    such as using indexes, optimizer hints or simply put the joined tables in the right order.

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.

Utilising Customised SQL Statement Time-outs

To customise SQL statement time-outs, call one of the Methods of an instance of the TDataBase Class (e.g. DBAccess.GDBAccessObj) that has got an ACommandTimeout or ASelectCommandTimeout Argument. (Those Arguments always default to -1, which means the default timeout of 20 seconds will be used.)

Pass in an int value higher than 20 into one of those Arguments to increase the SQL statement time-out.


Notes

It should be noted that there is no inter-relation between 'prepared' statements and custom SQL Statement time-outs.


Questions?

In case you have questions regarding the features that get explained on this page please contact ChristianK, the developer of those features.