Post reply

Prepared Statements

  • August 25, 2016 at 1:41 am

    #329679

    Morning All,

    I have inherited an application that makes heavy use of prepared statements.

    How do they perform / compare to unprepared statements where the parameter values are passed in along with the call itself? How does the optimiser handle this placeholder parameters?

    Cheers All,

    Alex

  • August 25, 2016 at 2:33 am

    #1897404

    Hi,

    You can have a look at the following links:

    https://technet.microsoft.com/en-us/library/ms175528(v=sql.105).aspx

    http://www.sqlskills.com/blogs/kimberly/prepared-statements-and-caching/

    I hope these help.

  • August 25, 2016 at 2:42 am

    #1897406

    Cheers Courtney

  • August 25, 2016 at 3:36 am

    #1897417

    Generally, prepared statements are roughly the same as stored procedures, so they're handled very well indeed. The optimizer can create an execution plan that is reusable across multiple iterations of the statement, regardless of values passed. The parameters can be sniffed, sampled, to get more accurate execution plans (and yeah, parameter sniffing is a good thing most of the time). In short, prepared statements are an excellent way to go. They're safer than inline values since you're still dealing with parameters and can avoid injection attacks. There just isn't much to complain about with these things.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 25, 2016 at 3:50 am

    #1897419

    Cheers Grant.

    My problem with these so far (this is really my first time using them in anger rather than in theory) is I am finding it hard to see what the parameters being passed are. So when a problem is reported ("xyz is slow, is hanging, has crashed", etc.) - it's hard to recreate.

    Is there a way to identify the parameters being passed in -- all I can see in a trace, or within XE's is @P1, @P2, etc

    Alex

  • August 25, 2016 at 3:59 am

    #1897421

    Experiment around with the exevents. I'm pretty sure there's a way to see those, but I could be wrong. I don't have an immediate answer to hand.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply