All about prepared statements. When to use them to go faster!

Hello friends,

Are you ready to learn about prepared statements? 

If you are a software developer you will have noticed that a T-SQL statement (a Query) can be prepared.

Who knows exactly what that means?
If I prepare a query then it runs faster?

Today we will answer to all these questions and give some examples.
We are interested in using them when they make us go faster!

READY?

A bit of theory, key concepts!

Not only Microsoft SQL Server support prepared statement. Prepared statements is infact a feature of many DMBS like Oracle, DB2, MySQL and PostgreSQL.

The main idea is to split the execution of a statement in two parts. During the first part called preparation the T-SQL will be parsed, analyzed and optimized. During the last part called execution parameters will be binded and the command will be executed.

When is this feature useful?

This feature is useful when the same command must be executed many times.

There are unfortunately some limitations to consider.

Some Limitations

• Sql server will optimize the command regardless of the value of the parameters.
• Secondly, the prepare and unprepare procedure has a cost.

For this reason, preparing a Query to run it a few times does not increase performance.
How often do you need to run a prepared query to benefit from it? well we will see it in the example paragraph.

 

From the developer's point of view

Now let's put ourselves in the shoes of a developer who is building an application that interacts with a SQL Server database. Suppose we want to prepare a Query.

 

Depending on the tecnology used connect to the database engine we will have a different way to prepare a statement.

 

For example:

 

Using ADO in Delphi we will have a prepared property to be set to True:

 
ADOQuery := TADOQuery.Create(Self);
ADOQuery.Connection := ADOConn;
ADOQuery.SQL.Text := 'UPDATE TABLE SET Price = @1 WHERE ID = @2';
ADOQuery.Prepared := true;  
 

Using instead ODBC in C+ we will have a function called SQLPrepare.

 
SQLPrepare(hstmt, "UPDATE TABLE SET Price = ? WHERE ID = ?", SQL_NTS); 

How a prepared command is translated into SQL?

Ok so today you are a developer and you are writing some lines of code to get data from a table you have inside a SQL Server database.

Suppose you are using Delphi language with ADO to connect to your database.

I wrote this typical example where we must read the descr field from the ORDRIG table.
Since we must execute the same query more than once we decide to prepare the Query setting the property AdoQuery.prepared := true;

What happens? Wich request ADO do to the SQL Server engine?
Let's look to the picture below:

 

We have executed our query 4 times. Since we prepared the statement, ADO translate this request in the way as SQL server expect it.

 

• For the first Query use the sp_prepexec command that prepare and execute

 

 
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,N'@P1 int,@P2 int',

         N'SELECT DESCR FROM ORDRIG WHERE IDORDTES = @P1 AND IDARTICO = @P2',40,858
select @p1 

Note that the sp_prepexec return a parameter (@p1), an handle that identify a statement in an unique way and that will need to be used in the execute.

 

• For all the upcoming (3) queries use the sp_execute command.

 
exec sp_execute 1,41,859

where 1 is @p1

 

This is the way in which prepared statements are managed by SQL Server.

 

 

 

Note that for the sake of completeness we could writtein the same request using the sp_prepare and not then sp_prepexec:

 
declare @p1 int
set @p1=0
exec sp_prepare @p1 output,N'@P1 int,@P2 int',

     N'SELECT DESCR FROM ORDRIG WHERE IDORDTES = @P1 AND IDARTICO = @P2'
 
exec sp_execute 1,40,858
exec sp_execute 1,41,859
exec sp_unprepare @P1;

From a performance point of view i want to tell you once again: with this feature queries the optimizer doesn't take care of the value of the parameters to produce the execution plan and this is a limitation.

 

Let's talk about performance

This is what the theory says: 

Since prepare and unprepare operations require time so it is a good idea to prepare a query only if you will execute it frequently.  

But, it's really true?

A second affirmation: 

Preepared queries are (always) faster than unprepared queries

Again, it's true? Well, let's start saying: It depends! ....It depends from many factors.

We need to run some benchmark!

First benchmark

I run a benchmark where i execute the same statement 1000, 10000, 20000, 100000 and 500000 times. One time with the query prepared and another time without preparing the query.

This is the first statement:

 
 SELECT DESCR FROM ORDRIG WHERE IDORDTES = @IDORDTES AND IDARTICO = @IDARTICO 

And these are the results!

 
1000 iterations     PREPARED: 00:00:07   UNPREPARED: 00:00:02
10000 iterations    PREPARED: 00:00:43   UNPREPARED: 00:00:14
50000 iterations    PREPARED: 00:01:57   UNPREPARED: 00:01:20
100000 iterations   PREPARED: 00:03:05   UNPREPARED: 00:02:59
500000 iterations   PREPARED: 00:15:20   UNPREPARED: 00:13:54

Hei! What is wrong? Unprepared query are faster than prepared! 

Why? Where is the tricks?

Our simple statements has a Trivial plan and there is not anything to prepare!

Now i run another benchmark where i run a more complicated statement .

Another benchmark, prepared vs. unprepared queries.

This time i choose two statements and i repeat the execution 1000, 5000, 10000, 20000, 100000, 200000 and 400000 times, with an d without prepare the query.

First Query:

 
SELECT T.ANNDOC, T.NUMDOC, SUM(R.QTA1)
FROM ORDRIG R
JOIN ORDTES T ON R.IDORDTES = T.ID
WHERE
R.IDORDTES = :IDORDTES AND
R.IDARTICO = :IDARTICO AND
T.IDTIPIDOC = 77
GROUP BY T.ANNDOC, T.NUMDOC 

Second:

 
SELECT SUM(QTA1) FROM ORDRIG
WHERE
IDORDTES = :IDORDTES AND
IDARTICO = :IDARTICO

Here the results:

The first query (blue and orange) effectively after 200.000 execution the prepared query is more fast that the unprepared one.

For the second query (grey and yellow) performance are more or less equal.

Recap!

If you want to use the prepared query i suggest you to try our procedure in both the cases with o with/out prepare the query. 

This is because may happens that performance may not change.

My strong suspicion is that the preparation of the query without consider also the value of the parameters is sometimes a big penalization!

That's all for today,
As usully, i hope you enjoyed this post! In this case show it to your friends and/or colleague, please!

Luca

Previous post:Why does my transaction log growth? simple and clear!