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

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!

Now, to mimic the behavior of a JDBC query:

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

Ze Bad Guess

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)

Cruddy

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

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

BIG MONEY

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.

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

Sniff sniff pass

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:

Look at you then

Why Is One better?

I put together this handy chart!

IT’S ONLY A PICTURE

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.

ValueDescription
0SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)
1Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)
2sp_executesql is used (fast)
3sp_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.

Previous Post
“Full Stack” Means “Part Time” – or Why DBAs Might Wanna Learn DevOps
Next Post
How to Deploy Your First Azure SQL DB Managed Instance Preview

10 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}