Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

Last Updated 4 years ago

sp_prepare For Mediocre

You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)!

Great posts, Kendra!

Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector rather than the histogram for cardinality estimation.

Puddin’

Let’s create an index to make our query’s work easy!

CREATE INDEX ix_whatever ON dbo.Users (Reputation) INCLUDE (DisplayName);

1	CREATE INDEX ix_whatever ON dbo.Users (Reputation) INCLUDE (DisplayName);

Now, to mimic the behavior of a JDBC query:

DECLARE @out INT;
EXEC sys.sp_prepare @out OUTPUT,
                    N'@r INT',
                    N'SELECT COUNT(DisplayName) as records
                      FROM dbo.Users AS u
                      WHERE u.Reputation = @r;',
                       1;

EXEC sys.sp_execute @out, 1;

EXEC sys.sp_execute @out, 2;

EXEC sys.sp_execute @out, 6;

EXEC sys.sp_execute @out, 10;

EXEC sys.sp_unprepare @out;
GO

DECLARE @out INT;

EXEC sys.sp_prepare @out OUTPUT,

N'@r INT',

N'SELECT COUNT(DisplayName) as records

FROM dbo.Users AS u

WHERE u.Reputation = @r;',

EXEC sys.sp_execute @out, 1;

EXEC sys.sp_execute @out, 2;

EXEC sys.sp_execute @out, 6;

EXEC sys.sp_execute @out, 10;

EXEC sys.sp_unprepare @out;

The query plans for all of these have something in common. They have the exact same estimate!

You might be saying to yourself that the first parameter is sniffed, and you’d be wrong.

That estimate exactly matches the density vector estimate that I’d get with a local variable or optimize for unknown: SELECT (7250739 * 5.280389E-05)

You can validate things a bit by adding a recompile hint to the demo code.

DECLARE @out INT;
EXEC sys.sp_prepare @out OUTPUT,
                    N'@r INT',
                    N'SELECT COUNT(DisplayName) as records
                      FROM dbo.Users AS u
                      WHERE u.Reputation = @r
                      OPTION(RECOMPILE);',
                      1;

EXEC sys.sp_execute @out, 1;

EXEC sys.sp_execute @out, 2;

EXEC sys.sp_execute @out, 6;

EXEC sys.sp_execute @out, 10;

EXEC sys.sp_unprepare @out;
GO

DECLARE @out INT;

EXEC sys.sp_prepare @out OUTPUT,

N'@r INT',

N'SELECT COUNT(DisplayName) as records

FROM dbo.Users AS u

WHERE u.Reputation = @r

OPTION(RECOMPILE);',

EXEC sys.sp_execute @out, 1;

EXEC sys.sp_execute @out, 2;

EXEC sys.sp_execute @out, 6;

EXEC sys.sp_execute @out, 10;

EXEC sys.sp_unprepare @out;

The plans for all of the recompiled queries get different estimates, and no estimate matches the 382 estimate we saw from the first round.

Am I saying you should recompile all of your queries to get around this?

No, of course not. Query compilation isn’t what you should be spending your SQL Server licensing money on.

You may want to not use JDBC anymore, but…

How Is sp_executesql Different?

Well, sp_executesql “sniffs” parameters.

DECLARE @sql NVARCHAR(MAX ) = N''
SET @sql += N'SELECT COUNT(DisplayName) as records
                        FROM dbo.Users AS u
                        WHERE u.Reputation = @r;'

EXEC sys.sp_executesql @sql, N'@r INT', 1;

EXEC sys.sp_executesql @sql, N'@r INT', 2;

EXEC sys.sp_executesql @sql, N'@r INT', 6;

EXEC sys.sp_executesql @sql, N'@r INT', 10;
GO

DECLARE @sql NVARCHAR(MAX ) = N''

SET @sql += N'SELECT COUNT(DisplayName) as records

FROM dbo.Users AS u

WHERE u.Reputation = @r;'

EXEC sys.sp_executesql @sql, N'@r INT', 1;

EXEC sys.sp_executesql @sql, N'@r INT', 2;

EXEC sys.sp_executesql @sql, N'@r INT', 6;

EXEC sys.sp_executesql @sql, N'@r INT', 10;

If I run my demo queries in this order, the plan for Reputation = 1 gets cached and reused by all the other calls.

If I change the order so Reputation = 2 runs first, the plans change (after clearing the plan out of the cache, of course).

Now they all reuse that plan:

Why Is One better?

I put together this handy chart!

I’m not smart enough to get a formatted table like this into a web page.

I’m a bad DBA.

Thanks for reading!

UPDATE:

The admirable and honorable Joseph Gooch notes in the comments that you can configure this with the jTDS JDBC driver:

prepareSQL (default – 3 for SQL Server, 1 for Sybase)This parameter specifies the mechanism used for Prepared Statements.

Value Description

0 SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)

1 Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)

2 sp_executesql is used (fast)

3 sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)

Value	Description
`0`	SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)
`1`	Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)
`2`	sp_executesql is used (fast)
`3`	sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)

Though I’m not too thrilled that sp_prepare is called “faster”.

And! That similar options are available in the 6.1.6 preview of the Microsoft JDBC drivers.

“Full Stack” Means “Part Time” – or Why DBAs Might Wanna Learn DevOps

How to Deploy Your First Azure SQL DB Managed Instance Preview

11 Comments. Leave new

Joseph Gooch
March 15, 2018 12:38 pm

You can change this behavior in the JDBC drivers….
See #1 here , for MS Driver per 6.1.6-preview
https://github.com/Microsoft/mssql-jdbc/wiki/PreparedStatement-metadata-caching

For JTDS, you can set the prepareSQL= option in the connection string to 2, or something else like 1 or 0.
http://jtds.sourceforge.net/faq.html

Reply
- Erik Darling
  March 15, 2018 12:52 pm
  
  Joseph — that’s great to know! I’ll add that to the post.
  
  Reply
- Kendra Little
  November 10, 2023 2:34 pm
  
  @Joseph, thank you for this five and a half years later 🙂
  
  Reply
Martin Guth
March 23, 2018 12:49 am

Hi Erik,
great post as always! I am excited as this seems to be exactly an issue I spotted some days ago and didn’t really understand with a query plan having a ridiculous low cardinality estimate. Definitely something I am going to test soon. Just one question: Is setting 1 for the JDBC Driver (temproary stored procedures) the same thing as Tara suggested for Troubleshooting Parameter Sniffing issues on https://www.brentozar.com/archive/2018/03/troubleshooting-parameter-sniffing-issues-the-right-way-part-3/?

Reply
- Joseph Gooch
  March 23, 2018 6:06 am
  
  Since I got Brent into this… 🙂 I’ve read through the jTDS source. (TdsCore.java and JtdsConnection.java)
  
  From his code above:
  SET @sql += N’SELECT COUNT(DisplayName) as records
  FROM dbo.Users AS u
  WHERE u.Reputation = @r;’
  
  jTDS would build:
  
  create proc #jtds_0000xx @P0 int as
  SELECT COUNT(DisplayName) as records
  FROM dbo.Users AS u
  WHERE u.Reputation =@P0
  
  (where 0000xx is generated from a sequence number)
  And replace your executions with:
  EXEC #jtds_0000xx @P0=1
  
  Based on that it does look like what Tara suggested, without the recompile hint.
  
  Reply
alen teplitsky
April 18, 2018 9:23 am

This used to be the bane of my existence some weeks. An application would work and for no reason one day running thousands of these would suddenly be slow and a process would crawl to a halt.

Reply
Toby Ovod-Everett
April 18, 2018 11:33 am

In your JPG table, did you mean “Can call victim to parameter sniffing” or “Can fall victim to parameter sniffing”?

Reply
- Erik Darling
  April 18, 2018 1:20 pm
  
  Yeah, that’s a typo. Sorry about that!
  
  Reply
Steve G
April 18, 2018 1:42 pm

Is anyone else having heart palpitations over the description for option 0 (SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower))?? This sounds like a direct and definitely obscured invitation to SQL Injection.

Reply
- Erik Darling
  April 18, 2018 2:51 pm
  
  Steve – yeah, that doesn’t sound good. I don’t really have a way to test it though.
  
  Reply
Dave
August 5, 2020 3:19 am

I’ve been looking at this for PDO_SQLSRV PHP MS SQL driver. We switched on direct statement (does sp_execute) but MS advises otherwise saying sp_prepare is better see link?

https://docs.microsoft.com/en-us/sql/connect/php/direct-statement-execution-prepared-statement-execution-pdo-sqlsrv-driver?view=sql-server-ver15

Reply