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
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:
- 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
- For all the upcoming (3) queries use the sp_execute command.
exec sp_execute 1,41,859
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;
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
Comments
Post a Comment