Post reply

sp_executesql Question

  • February 24, 2004 at 1:00 pm

    #161509

    I have a developer using a third-part reporting software.  The software uses the sp_executesql stored procedure to obtain results rather than a direct query.  I've only seen this used by MS Access when it does queries; I really never paid much attention to it.  With some of the limited research I've done, it appears as though using this may be an efficient way to run queries since it is able to re-use execution plans.  However, in this particular case, using this option versus direct sql, there is a dramatic decrease in performance. 

     

    The example is running a query selecting on an clustered index and returning 3 columns.  The table has 62 million rows.  When I run it in query analyzer, it returns the results in less than a second.  Using the sp_executesql (even in query analyzer), it takes up to 15 seconds.  When I show the execution plan, it appears that it is trying to query using the primary key column, which in this case is different than the clustered index I mentioned. 

     

    Any ideas?

  • February 24, 2004 at 1:50 pm

    #495410

    Without knowing your query, table structure and indexes it will be difficult to tell what is going on. As always with dynamic sql I'd like to refer you to http://www.sommarskog.se/dynamic_sql.html.

    Did you run both alternatives under same conditions?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 25, 2004 at 5:15 am

    #495513

    The basis of sp_executesql is that it is calling a Stored Procedure in your database, somewhere.  Stored Procedures are "precompiled". That is, the execution plan for the Stored Procedure was created when the Stored Procedure itself was created.

    What you're calling "direct SQL" sounds like what I call "embedded SQL".  That is, the SQL is in your program as a "string" and you tell the program's connection to the database to execute the "string" of SQL.  And it does, except that the "string" version is NOT precompiled.  So, the SQL Server has to do extra work and create statistics, etc. 

    As a result, executing "embedded SQL" will almost always take longer than using sp_executesql to "call" a precompiled Stored Procedure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 25, 2004 at 9:36 am

    #495611

    Is your Primary key not the same as the clustered index that the ADHOC query is selecting?  If not, I wonder if you should take a look at why query optimizer is opting for a different index.  Try running the sp_executesql with an index hint to force it to use the clustered index.  My guess is you will see the performance returned.  Perhaps you should consider manually updating your statistics as well.

  • February 25, 2004 at 9:47 am

    #495615

    Yes, Jeff, I agree that's the way it's supposed to work.  But, I think what's happening in this particular case, since the table is so large, SQL Server in it's infinite wisdom is assuming the best way to scan the table is to use a primary key.  In my case the column I am using in the where clause is not the primary key; although it is a clustered index.  To test my theory, I used the same queries, one using query analyzer (embedded) and, also using query analyzer, the sp_Executesql statement, on the same table in a different database having only a few thousand entries.  In that case, the performace was the same and the execution plan showed use of the clustered index rather than the primary key.

  • February 25, 2004 at 9:54 am

    #495616

    Thanks, Steve, I'll try both.

  • February 27, 2004 at 6:47 am

    #496173

    Problem appears solved.  The developer is using JBDC and another person in our department researched that angle and came up with this solution link:   

    http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&threadm=060201c2cee4%24246fd790%248ef82ecf%40TK2MSFTNGXA04

    This appears to cause the problem in query analyzer as well. Removing the conversion to nvarchar allows the sp_execute to operate correctly. 

    I apologize for not including the syntax originally.  It's listed below for reference.

     

    So,

    exec sp_executesql N'SELECT A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM Claim_Export_Summary A0 WHERE A0.dcn = @P1 ', N'@P1 nvarchar(40) ', N'04014100150271'

    runs slow, whereas

    exec sp_executesql N'SELECT A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM Claim_Export_Summary A0 WHERE A0.dcn = @P1 ', N'@P1 varchar(40) ', N'04014100150271'

    is FAST !!!!

  • February 28, 2004 at 12:47 pm

    #496341

    Maybe it's just me and I really am going blind... would someone tell me what the difference is between the two lines that pnewhart put in his last post?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 28, 2004 at 1:20 pm

    #496344

    Well, the first one is in the upper line, while the second one is in the lower line

    Looks like a copy'n'paste issue

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 29, 2004 at 11:27 pm

    #496387

    The REAL difference is the: nvarchar(40) in the first statement while the second is: varchar(40)


    Butch

  • March 1, 2004 at 12:38 am

    #496392

    Very good catch

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2004 at 6:39 am

    #496433

    That was the issue.  The n' prefixes on the second line were also not necessary, I realized this after I posted.

  • March 1, 2004 at 8:48 pm

    #496574

    First, thanks for pointing out the difference there, Butch.  Guess I better get some glasses!

    And, thank you pnewhart, for pointing out just exactly how slow  UniCode (the 'N' stuff) can be.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 13 posts - 1 through 12 (of 12 total)

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