Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: sp_executesql not using indexes

    A colleague of mine is running the following code, which executes a SQL query twice. The first time as direct SQL, and the second time using sp_executesql:
    Code:
    DECLARE @p0 AS VarChar(3)
    SET @p0 = 'AEP'
    
    SELECT	TOP (250) [t0].[CheckMasterId], [t0].[AccountNumber], [t0].[CheckNumber], [t0].[Amount], [t0].[PaymentMethodId], [t0].[PayeeName], [t0].[ClientName], [t0].[ClaimantFirstName], [t0].[ClaimantMiddleName], [t0].[ClaimantLastName], [t0].[ClaimId], [t0].[ClaimNumber], [t0].[JurisdictionClaimNumber], [t0].[PaymentCategoryId], [t0].[PaymentCodeId], [t0].[StatusCode], [t0].[StatusReasonId], [t0].[CheckHoldingId], [t0].[CheckRegisterId], [t0].[SFPaymentId], [t0].[CreatedDate], [t0].[ModifiedDate]
    FROM [dbo].[CheckMaster] AS [t0]
    WHERE [t0].[ClientName] = @p0
    
    exec sp_executesql N'SELECT TOP (250) [t0].[CheckMasterId], [t0].[AccountNumber], [t0].[CheckNumber], [t0].[Amount], [t0].[PaymentMethodId], [t0].[PayeeName], [t0].[ClientName], [t0].[ClaimantFirstName], [t0].[ClaimantMiddleName], [t0].[ClaimantLastName], [t0].[ClaimId], [t0].[ClaimNumber], [t0].[JurisdictionClaimNumber], [t0].[PaymentCategoryId], [t0].[PaymentCodeId], [t0].[StatusCode], [t0].[StatusReasonId], [t0].[CheckHoldingId], [t0].[CheckRegisterId], [t0].[SFPaymentId], [t0].[CreatedDate], [t0].[ModifiedDate]
    FROM [dbo].[CheckMaster] AS [t0]
    WHERE [t0].[ClientName] = @p0',N'@p0 varchar(3)',@p0='AEP'
    What he is seeing is that the sp_executesql version takes 40 times as long to process than the direct SQL version. The direct version is using indexes on the table, while the sp_executesql version is using a table scan.
    Any ideas why sp_executesql would not use the indexes?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am having a difficult time reproducing this on 2005, but I don't have great test data available to me. is this on 2000 or 2005? Or even 2008?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Client name is nonclustered and results in a lookup right?

    Not a unique index? Are there values for ClientName that would not be optimal to seek on the NCI and lookup on the CI?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I tried non clustered and clustered on 1/2 million rows and I got seeks all 4 times.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    judging from the select list, I doubt covering indexes are an option here. I tried to replicate the effect on my terst box with this setup:
    Code:
    drop table test1
    create table test1
    (col1 int,
     col2 varchar(3),
     col3 datetime,
     col4 varchar(255))
    
    insert into test1
    select row_number() over (order by o.object_id), 
    	char((row_number() over (order by o.object_id) - 1)%26 + 65) + char((row_number() over (order by oo.object_id) - 1)%26 + 65) + char((row_number() over (order by ooo.object_id) - 1)%26 + 65),
    	o.create_date,
    	o.name + oo.name + ooo.name
    from sys.objects o ,
    	sys.objects oo,
    	sys.objects ooo
    
    create index ind_test on test1 (col2)
    but I get index seeks on both cases
    Code:
    DECLARE @p0 AS varchar(4)
    SET @p0 = 'AEP'
    
    SELECT	TOP (250) col1, col2, col3, col4
    FROM [dbo].[test1] AS [t0]
    WHERE [t0].[col2] = @p0
    
    exec sp_executesql N'SELECT	TOP (250) col1, col2, col3, col4
    FROM [dbo].[test1] AS [t0]
    WHERE [t0].[col2] = @p0',N'@p0 varchar(4)',@p0='AEP'

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Here's my ugly setup code. Funnily enough, I get a seek for the sp_executesql and scan for the other. Deliberately trying to screw with the optimiser BTW.

    Code:
    USE tempdb
    go
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.CheckMaster')) BEGIN
        DROP TABLE dbo.CheckMaster
    END
    
    CREATE TABLE dbo.CheckMaster
        (
              CheckMasterId            INT IDENTITY    NOT NULL
            , [AccountNumber]        INT                NULL        DEFAULT (0)
            , [CheckNumber]            INT                NULL        DEFAULT (0)
            , [Amount]                DECIMAL            NULL        DEFAULT (0)
            , [PaymentMethodId]        INT                NULL        DEFAULT (0)
            , [PayeeName]            VARCHAR(3)        NULL        DEFAULT ('SSS')
            , [ClientName]            VARCHAR(3)        NOT NULL
            , [ClaimantFirstName]    VARCHAR(30)        NULL
            , [ClaimantMiddleName]    VARCHAR(30)        NULL
            , [ClaimantLastName]    VARCHAR(30)        NULL
    --        , [ClaimId], [ClaimNumber], [JurisdictionClaimNumber], [PaymentCategoryId], [PaymentCodeId], [StatusCode], [StatusReasonId], [CheckHoldingId], [CheckRegisterId], [SFPaymentId], [CreatedDate], [ModifiedDate]
            , CONSTRAINT pk_CheckMaster_u_c PRIMARY KEY CLUSTERED (CheckMasterId) WITH (FILLFACTOR = 100)
        )    
    go
    
    IF EXISTS (SELECT NULL FROM sys.indexes WHERE name = N'ix_CheckMaster_ClientName_nu_nc') BEGIN
        DROP INDEX dbo.CheckMaster.ix_CheckMaster_ClientName_nu_nc
    END
    
    CREATE NONCLUSTERED INDEX ix_CheckMaster_ClientName_nu_nc
    ON dbo.CheckMaster (ClientName ASC)
    --INCLUDE (c_2_name)
    --WITH 
    --    (
    --        FILLFACTOR = 100
    --    )    
    
    INSERT dbo.CheckMaster(ClientName, ClaimantFirstName, [ClaimantMiddleName], [ClaimantLastName])
    SELECT    TOP 300000 'pfl', REPLICATE('x', 30), REPLICATE('x', 30), REPLICATE('x', 30)
    FROM    dbforums.dbo.numbers AS a
    CROSS JOIN 
            dbforums.dbo.numbers
    GO 
    
    INSERT dbo.CheckMaster(ClientName, ClaimantFirstName, [ClaimantMiddleName], [ClaimantLastName])
    SELECT    'AEP', REPLICATE('x', 30), REPLICATE('x', 30), REPLICATE('x', 30)
    GO
    Code:
    DECLARE @p0 AS VarChar(3)
    SET @p0 = 'AEP'
    
    SET STATISTICS IO ON
    
    SELECT    TOP (250) [t0].[CheckMasterId], [t0].[AccountNumber], [t0].[CheckNumber], [t0].[Amount], [t0].[PaymentMethodId], [t0].[PayeeName], [t0].[ClientName], [t0].[ClaimantFirstName], [t0].[ClaimantMiddleName], [t0].[ClaimantLastName]--, [t0].[ClaimId], [t0].[ClaimNumber], [t0].[JurisdictionClaimNumber], [t0].[PaymentCategoryId], [t0].[PaymentCodeId], [t0].[StatusCode], [t0].[StatusReasonId], [t0].[CheckHoldingId], [t0].[CheckRegisterId], [t0].[SFPaymentId], [t0].[CreatedDate], [t0].[ModifiedDate]
    FROM [dbo].[CheckMaster] AS [t0]
    WHERE [t0].[ClientName] = @p0
    
    exec sp_executesql N'SELECT TOP (250) [t0].[CheckMasterId], [t0].[AccountNumber], [t0].[CheckNumber], [t0].[Amount], [t0].[PaymentMethodId], [t0].[PayeeName], [t0].[ClientName], [t0].[ClaimantFirstName], [t0].[ClaimantMiddleName], [t0].[ClaimantLastName] --, [t0].[ClaimId], [t0].[ClaimNumber], [t0].[JurisdictionClaimNumber], [t0].[PaymentCategoryId], [t0].[PaymentCodeId], [t0].[StatusCode], [t0].[StatusReasonId], [t0].[CheckHoldingId], [t0].[CheckRegisterId], [t0].[SFPaymentId], [t0].[CreatedDate], [t0].[ModifiedDate]
    FROM [dbo].[CheckMaster] AS [t0]
    WHERE [t0].[ClientName] = @p0',N'@p0 varchar(3)',@p0='AEP'
    
    SET STATISTICS IO OFF
    Code:
    Table 'CheckMaster'. Scan count 1, logical reads 5379, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    Table 'CheckMaster'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Could it be that ClientName in the table is defined as NVARCHAR or NCHAR? Also, what is the SQL build # that the tests are running on? Other than checking statistics and the data types, - I'm speechless for right now
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would have thought that the implicit conversion in that case would be to the less exact datatype (in this case, the varchar). I have fought this going that way (an nvarchar argument querying a varchar field in a table), but not this way.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by rdjabarov
    I'm speechless


    George
    Home | Blog

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, that's an incomplete quote!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Robert - even whiting out some of the words in a quote to produce something completely new is considered fair game here
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2008
    Posts
    2
    I know this was a few months ago, but did anyone discover a cause or resolution? I am having similar difficulties with sp_executesql.

    Is there a way of using query hints in dynamic sql?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I reckon it's got something to do with query plans not being cached.

    sp_executesql will recompile on each run and never save a query plan, whereas the other option will!
    George
    Home | Blog

  14. #14
    Join Date
    Dec 2008
    Posts
    2
    I thought the point of using sp_executesql over EXEC by itself was that it does save a query plan...

    Can it be solved with query hints? Can you add a WITH(INDEX(0)) to sp_executesql?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by BoL
    sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
    Seems your right, apologies for the red-herring!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •