September 2, 2009 at 7:41 am
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
September 2, 2009 at 7:45 am
Can you post both query plans ?
In the meantime try using the "OPTIMIZE FOR" query hint , see if that helps
September 2, 2009 at 7:45 am
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
September 2, 2009 at 7:55 am
What is the datatype of ReadingID in the table?
Does it match the datatype of the SP parameter?
September 2, 2009 at 11:20 am
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!!
September 2, 2009 at 11:41 am
Captain Scarlett:
ReadingID is a BIGINT - same as param (when used)
Thanks
September 2, 2009 at 12:14 pm
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?
September 2, 2009 at 12:49 pm
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
September 2, 2009 at 1:07 pm
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?
September 2, 2009 at 1:16 pm
When I add the local variables instead of using constants, then the TSQL is bad too! ...interesting.
September 2, 2009 at 1:30 pm
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?
September 2, 2009 at 1:33 pm
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
September 2, 2009 at 1:35 pm
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
September 3, 2009 at 9:26 am
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
September 3, 2009 at 9:37 am
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