Stored Procedure performing a lot slower than TSQL

  • SQL 2005

    I have a simple TSQL statement to extract a subset or rows from a table with 434 million rows.

    The WHERE clause chooses a range of 'ReadingIDs' - there is a Non-Clusterd index with the ReadingID as the only column in that specific index.

    The table is partitioned on a different (clustered) index using a bigint field representing a date.

    When the TSQL is executed, it returns the complete rowset (around 600 rows) in 4 or 5 seconds.

    If I put the same code into a Stored Procedure and call the stored proc from a TSQL window, the same result set takes 14-15 minutes - yes minutes.

    Looking at the execution plan, the TSQL command uses the ReadingID as an index seek.

    Looking at the stored proc, the command uses an index scan on the clustered index.

    Here is what I have tried:

    Included (2) WITH RECOMPILE commands - no change.

    Rebuilt all indexes - no change.

    Removed all indexes, added back just the ReadingID (non clustered) index - TSQL good; stored proc does a Table Scan to retrieve the same rowset, not so good.

    Here is the (simplified) stored proc code - the TSQL just executes the 'SELECT' part for its test.

    CREATE procedure [dbo].[Get_Reads_IH]

    @start datetime = null,

    @end datetime = null,

    @unitid int = null,

    @port smallint = null,

    @startID bigint = null,

    @endID bigint = null,

    @IDCount bigint = null

    --WITH RECOMPILE

    as

    set nocount on

    set ansi_warnings off

    SELECT r.[unitid], r.[port number],r.[reading time], r.ReadingID, XInterval, a.xrefid, Inc1, Inc2, Inc3, Inc4, Inc5, Inc6, Inc7, Inc8, Inc9, Inc10, Inc11, null

    from [unit_readings] r WITH (NOLOCK)

    join [account_xref] a on r.[unitid] = a.[unitid] and r.[port number] = a.[port number]

    join tunits tu on a.[unitid] = tu.[unitid]

    join utypes mt on tu.unittype = mt.unittype

    where a.state = 1 and

    r.ReadingID >= 432225000 and

    r.ReadingID <= 432226000 --r.ReadingID >= @StartID and

    --r.ReadingID <= @endID --OPTION (RECOMPILE)
    -------------------------------------------------------------------------------------------
    -- END OF STORED PROC
    -------------------------------------------------------------------------------------------

    TSQL call to run stored procedure is:
    EXEC Get_Reads_IH @startID = 432225000, @endID = 432226000

    Params are noted to be useless at this stage - SP changed for testing.

    Any help is appreciated.
    Thanks
    Ian

  • Can you post both query plans ?

    In the meantime try using the "OPTIMIZE FOR" query hint , see if that helps



    Clear Sky SQL
    My Blog[/url]

  • Do a search for "parameter sniffing" in Bing/Google/whatever. Gail Shaw's blog has an article on it that's quite good: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • What is the datatype of ReadingID in the table?

    Does it match the datatype of the SP parameter?

  • 3 indexes on table unit_readings:

    non-clustered; unitID, [Port Number], [Reading Time]

    UNIQUE non-clustered; ReadingID

    UNIQUE clustered; UDay, UInterval, unitID, [Port Number]

    Table is partitioned on UDay.

    Attached are the 2 XML Execution plans.

    Thank you for looking!!

  • Captain Scarlett:

    ReadingID is a BIGINT - same as param (when used)

    Thanks

  • Ian Hockaday (9/2/2009)


    Captain Scarlett:

    ReadingID is a BIGINT - same as param (when used)

    Thanks

    Darn... Just a hunch, but I remember seeing a case (on SQL2000) where a parameter of Bigint for a column of Int caused a table scan.

    If you change the fast performing SQL to use variables, like this:-

    Declare @startID bigint, @endID bigint

    SET @startID = 432225000

    SET @endID = 432226000

    SELECT r.[unitid], r.[port number],...

    .

    .

    .

    WHERE

    r.ReadingID >= @StartID and

    r.ReadingID <= @endID

    Do you get the good plan or the bad plan?

  • To reiterate, what you're looking at is a classic example of parameter sniffing. At least, I'd bet 9:1 that's the problem. Take a look at Gail's blog entry on the subject. That'll clarify what the problem is, and how to solve it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (9/2/2009)


    To reiterate, what you're looking at is a classic example of parameter sniffing. At least, I'd bet 9:1 that's the problem. Take a look at Gail's blog entry on the subject. That'll clarify what the problem is, and how to solve it.

    One of the solutions on Gail's blog is to mark the proc for recompilation.

    The OP has tried this, but it didn't make any difference, so is this still a case of parameter sniffing?

  • When I add the local variables instead of using constants, then the TSQL is bad too! ...interesting.

  • Ian Hockaday (9/2/2009)


    When I add the local variables instead of using constants, then the TSQL is bad too! ...interesting.

    ... which is progress of a sort.

    Can you post the DDL for the tables?

  • Ian Scarlett (9/2/2009)


    GSquared (9/2/2009)


    To reiterate, what you're looking at is a classic example of parameter sniffing. At least, I'd bet 9:1 that's the problem. Take a look at Gail's blog entry on the subject. That'll clarify what the problem is, and how to solve it.

    One of the solutions on Gail's blog is to mark the proc for recompilation.

    The OP has tried this, but it didn't make any difference, so is this still a case of parameter sniffing?

    Yes. The fast version of the query is compiled with constants, and can thus be run against statistics for the table much more efficiently. Using variables/parameters means it has to use a less efficient, broader execution plan. That's one of the main points of parameter sniffing issues.

    Recompiling is just one of the possible solutions to one of the possible problems with sniffing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Assuming you're using SQL 2005 (from the forum this is posted in), you have the ability in Management Studio to save the execution plans as .sqlplan files. Please do so, then zip them and upload them to the forum. That's much better than .txt files. Can you do that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • After further review...

    It seems that a small change to the TSQL select statement will make the query much slower. Just adding one more (int) field to the output column list causes a major (problem) change in the execution plan.

    Below are the 2 code extracts that I ran; attached are the 2 execution plan captures (zipped) in graphical form this time.

    Thanks

    ianhoc

    --FAST--

    --

    DECLARE @startIDBIGINT,

    @endIDBIGINT

    SET@startID= 432225000

    SET@endID= 432226000

    select r.UnitID, r.[port number], r.ReadingID, IncXmitInterval, a.xrefid--, r.RecID

    from Unit_Readings r WITH (NOLOCK)

    join Account_Xref a on r.UnitID = a.UnitID and r.[port number] = a.[port number]

    join Tunits tu on r.UnitID = tu.UnitID

    join UTypes mt on tu.Unittype = mt.UnitTypeID

    where a.state = 1 and

    r.ReadingID >= @startID and

    r.ReadingID = @startID and

    r.ReadingID <= @endID

  • I don't see the execution plans.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 22 total)

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