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.

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.

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

9 Comments. Leave new

  • 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
  • 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
  • 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

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":""}