November 23, 2010 at 3:01 pm
:angry:
I have a very frustrating problem. I have a query that runs terribly slow when using one date range, then runs very quickly using another date range.
The query is below. When I run the query and set it to search records 169 days back, the query runs in minutes. However when I run the query 170 or more days back, the query takes an hour to finish. What gives? The table has many index's which I reindex'd recently. Table is not fragmented at the moment. I have also updated statistics on all the tables in that database. Execution plan looks decent, no table scans...
What am I missing here?
SELECT
o.o_num
,MAX(t.t_num)
FROM
orders o (nolock)
JOIN track t (nolock) on o.o_num = t.o_num and t.t_status = 'CANCEL REQUEST'
WHERE
t.t_out is not null
AND t.t_in >= getdate() -169<--*********this statement right here ***************
AND o.o_type IN (9, 30)
AND o.trn_id IN (24, 45, 25, 0)
AND NOT EXISTS (SELECT 1 from track tr (nolock) where tr.o_num = o.o_num and tr.t_status
IN ('CANCEL/BILL','CANCEL/NO BILL'))
GROUP BY
o.o_num
November 23, 2010 at 3:28 pm
It's rare to get a serious issue still in the 2k environments, so to confirm, you meant to post in the SQL 7/2k forum, right? If so, which are you using? The reason I ask is because there's a few things in 2k5 (partitioning, for example) that might help to explain this
You mention you've checked the execution plan. Is it the actual, or the estimated? Can you please save the execution plans for the slow and fast queries (the actuals), and attach them here? You'll find a method for 2k in my sig, in the 3rd link.
The query itself doesn't scream anything obvious, it's time to look at the DDL and execution plans and confirm they're actually similar.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 3:51 pm
Oh, sorry I forgot to include, this is being run on SQL 2000 SP3A. It's the actual execution plan.
Here is the quick plan:
|--Parallelism(Gather Streams)
|--Stream Aggregate(GROUP BY:([o].[o_num]) DEFINE:([Expr1004]=MAX([t].[t_num])))
|--Sort(ORDER BY:([o].[o_num] ASC))
|--Hash Match(Left Anti Semi Join, HASH:([o].[o_num])=(
.[o_num]))
|--Bitmap(HASH:([o].[o_num]), DEFINE:([Bitmap1009]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[o_num]) WITH PREFETCH)
| |--Filter(WHERE:([t].[t_out]<>NULL))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([WINTRACK_NLA].[dbo].[track] AS [t]))
| | |--Hash Match(Inner Join, HASH:([Bmk1001])=([Bmk1001]), RESIDUAL:([Bmk1001]=[Bmk1001]))
| | |--Bitmap(HASH:([Bmk1001]), DEFINE:([Bitmap1008]))
| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1001]))
| | | |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS [t]), SEEK:([t].[t_status]='CANCEL REQUEST') ORDERED FORWARD)
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1001]), WHERE:(PROBE([Bitmap1008])=TRUE))
| | |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_XND2_NC] AS [t]), SEEK:([t].[t_in] >= getdate()-'May 31 1900 12:00AM') ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), SEEK:([o].[o_num]=[t].[o_num]), WHERE:((Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25
|--Parallelism(Repartition Streams, PARTITION COLUMNS:(
.[o_num]), WHERE:(PROBE([Bitmap1009])=TRUE))
|--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS
), SEEK:(
.[t_status]='CANCEL/BILL' OR
.[t_status]='CANCEL/NO BILL') ORDERED FORWARD)
Here is the bad plan: (all I changed was 150 days back to 180)
|--Parallelism(Gather Streams)
|--Stream Aggregate(GROUP BY:([o].[o_num]) DEFINE:([Expr1004]=MAX([t].[t_num])))
|--Sort(ORDER BY:([o].[o_num] ASC))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([o].[o_num]) WITH PREFETCH)
|--Hash Match(Left Anti Semi Join, HASH:([o].[o_num])=(
.[o_num]))
| |--Bitmap(HASH:([o].[o_num]), DEFINE:([Bitmap1008]))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
| | |--Clustered Index Scan(OBJECT:([WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), WHERE:((Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25) OR [o].[trn_id]=45) OR [o]
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:(
.[o_num]), WHERE:(PROBE([Bitmap1008])=TRUE))
| |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS
), SEEK:(
.[t_status]='CANCEL/BILL' OR
.[t_status]='CANCEL/NO BILL') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_PK_NC] AS [t]), SEEK:([t].[o_num]=[o].[o_num]), WHERE:(([t].[t_out]<>NULL AND [t].[t_in]>=getdate()-'Jun 30 1900 12:00AM') AND [t].[t_status]='CANCEL REQUEST')
November 23, 2010 at 3:59 pm
Heh, to make life easier, can you edit that post and wrap the plans in the code="plain" shortcuts you'll see on the left? Just highlight the section and click the wrapper, it'll do it for you.
Working through it anyway, see if anything stands out at the moment.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 4:02 pm
Of note:
| |--Clustered Index Seek(OBJECT[WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), SEEK[o].[o_num]=[t].[o_num]), WHERE(Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25
| | |--Clustered Index Scan(OBJECT[WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), WHERE(Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25) OR [o].[trn_id]=45) OR [o]
The bad plan as a scan when you add in trn_id 45. There seems to be multiple differences between the two actual queries at first glance, either that or the optimizer is taking them on in a different style.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 4:14 pm
Oops, sorry for the icons, next time I'll edit that properly.
Yes yes. I'm looking at the graphical plan and the clustered index scan on that bad plan produced over 1 million rows in 'row count'. The good plan it only produced a few thousand rows in 'row count'.
The code is identical. Nothing has been changed except for the getdate() -150 changed to getdate() -180.
If it is an optimizer issue, what can I do to get the optimizer to compile it properly?
Should I create statistics 100% on each of those tables? Should I drop some index's?
Thanks again for the help in advance.
November 23, 2010 at 4:19 pm
Frank Nadal (11/23/2010)
Oops, sorry for the icons, next time I'll edit that properly.Yes yes. I'm looking at the graphical plan and the clustered index scan on that bad plan produced over 1 million rows in 'row count'. The good plan it only produced a few thousand rows in 'row count'.
The code is identical. Nothing has been changed except for the getdate() -150 changed to getdate() -180.
If it is an optimizer issue, what can I do to get the optimizer to compile it properly?
Should I create statistics 100% on each of those tables? Should I drop some index's?
Thanks again for the help in advance.
I see part of the problem, the text is truncated. Can you check your query options in tools-options and doublecheck that you're not truncating lines at ~200 chars or so? Extend it to ~8800 characters for this. I forget the max offhand.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 4:30 pm
I changed the option in 'results' from 256 to 8000.
GOOD PLAN:
|--Parallelism(Gather Streams)
|--Stream Aggregate(GROUP BY:([o].[o_num]) DEFINE:([Expr1004]=MAX([t].[t_num])))
|--Sort(ORDER BY:([o].[o_num] ASC))
|--Hash Match(Left Anti Semi Join, HASH:([o].[o_num])=(
.[o_num]))
|--Bitmap(HASH:([o].[o_num]), DEFINE:([Bitmap1009]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[o_num]) WITH PREFETCH)
| |--Filter(WHERE:([t].[t_out]<>NULL))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([WINTRACK_NLA].[dbo].[track] AS [t]))
| | |--Hash Match(Inner Join, HASH:([Bmk1001])=([Bmk1001]), RESIDUAL:([Bmk1001]=[Bmk1001]))
| | |--Bitmap(HASH:([Bmk1001]), DEFINE:([Bitmap1008]))
| | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1001]))
| | | |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS [t]), SEEK:([t].[t_status]='CANCEL REQUEST') ORDERED FORWARD)
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1001]), WHERE:(PROBE([Bitmap1008])=TRUE))
| | |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_XND2_NC] AS [t]), SEEK:([t].[t_in] >= getdate()-'May 31 1900 12:00AM') ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), SEEK:([o].[o_num]=[t].[o_num]), WHERE:((Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25) OR [o].[trn_id]=45) OR [o].[trn_id]=24)) ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION COLUMNS:(
.[o_num]), WHERE:(PROBE([Bitmap1009])=TRUE))
|--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS
), SEEK:(
.[t_status]='CANCEL/BILL' OR
.[t_status]='CANCEL/NO BILL') ORDERED FORWARD)
BAD PLAN:
|--Parallelism(Gather Streams)
|--Stream Aggregate(GROUP BY:([o].[o_num]) DEFINE:([Expr1004]=MAX([t].[t_num])))
|--Sort(ORDER BY:([o].[o_num] ASC))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([o].[o_num]) WITH PREFETCH)
|--Hash Match(Left Anti Semi Join, HASH:([o].[o_num])=(
.[o_num]))
| |--Bitmap(HASH:([o].[o_num]), DEFINE:([Bitmap1008]))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([o].[o_num]))
| | |--Clustered Index Scan(OBJECT:([WINTRACK_NLA].[dbo].[orders].[ORDERS_PK_C] AS [o]), WHERE:((Convert([o].[o_type])=30 OR Convert([o].[o_type])=9) AND ((([o].[trn_id]=0 OR [o].[trn_id]=25) OR [o].[trn_id]=45) OR [o].[trn_id]=24)))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:(
.[o_num]), WHERE:(PROBE([Bitmap1008])=TRUE))
| |--Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_NC_STATUS] AS
), SEEK:(
.[t_status]='CANCEL/BILL' OR
.[t_status]='CANCEL/NO BILL') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([WINTRACK_NLA].[dbo].[track].[TRACK_PK_NC] AS [t]), SEEK:([t].[o_num]=[o].[o_num]), WHERE:(([t].[t_out]<>NULL AND [t].[t_in]>=getdate()-'Jun 30 1900 12:00AM') AND [t].[t_status]='CANCEL REQUEST') ORDERED FORWARD)
November 23, 2010 at 4:32 pm
GRRR I highlighted the sql results and clicked on "plain" on the left and it still inserted those stupid faces. Sorry.
November 23, 2010 at 4:41 pm
Frank Nadal (11/23/2010)
GRRR I highlighted the sql results and clicked on "plain" on the left and it still inserted those stupid faces. Sorry.
Don't worry about that as much. I wanted the plain so the tabbing didn't get fubar'd. 🙂 I'll take a run through this in a couple. Ending up at the office late tonight monitoring a process but I've got to get it finished and kick it off first.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 4:45 pm
Thanks so much. There is obviously a different style of processing going on when looking at the two plans. Let me know what you find.
November 23, 2010 at 5:42 pm
It's all about the orders table. If you'll notice, the 'good plan' does a seek on the track index, then does a lookup and then does clustered seek against orders. (Start with the deepest tabbing if you're not used to looking at these. You could also bring up your own graphicals.)
The bad one goes straight after orders as a scan. You've hit the selectivity margin for the optimizer.
You've got a couple of options here. None are fun.
There's not a lot you can do once you've broken selectivity, except perhaps build tighter and more exhaustive non-clustered indexing. This is where I'd start. I'd need to see the entire DDL for orders and all the indexing you've got against that structure if you need help with that.
You can try flipping the join between orders and track (put track first), and then use an OPTION (FORCE ORDER). This may get you to be able to seek in reverse. Doubtful, but worth a flying leap.
Next option is to live with it. I know, not the preference but sometimes it's what happens.
Next option (and the last option) is one that will usually get me shot until I can prove it to whomever has to work with the code after me. You know your range for dates is ~160 days to create the scanned plan. If you get a parameter that gives you more than (conservativesly) 140 days, break it into a loop of 140 day increments, drop it to a tablevar/temptable (depending on data volume), and return that set after the loop.
This is a special case that I call Cursor Optimization. If it's got a real name I don't know. The reason I call it cursor optimization is because it's actually faster to go in and retrieve results on a partial basis than it is to do it all at once because of optimization choices.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 6:15 pm
Frank Nadal (11/23/2010)
GRRR I highlighted the sql results and clicked on "plain" on the left and it still inserted those stupid faces. Sorry.
I'll bet that those faces are a ": (" (without the space). Can't do anything about it, so don't worry about it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 6:26 pm
I've got two suggestions to try. Please get back and let us know how they work for you.
The first one moves the correlated subquery in the where clause to a LEFT JOIN. The NOT IN is handled by a where column IS NULL - meaning no match. A join should give a lot better performance than a where clause that has to be evaluated for every row.
SELECT o.o_num,
MAX(t.t_num)
FROM orders o
JOIN track t
ON o.o_num = t.o_num
AND t.t_status = 'CANCEL REQUEST'
LEFT JOIN track tr
ON tr.o_num = o.o_num
AND tr.t_status IN ('CANCEL/BILL','CANCEL/NO BILL')
WHERE t.t_out is not null
AND t.t_in >= getdate() -169 --*********this statement right here ***************
AND o.o_type IN (9, 30)
AND o.trn_id IN (24, 45, 25, 0)
AND tr.o_num IS NULL
GROUP BY o.o_num;
This second one simply adds the OPTION (RECOMPILE) query hint to the query, forcing the optimizer to make a new query plan. I don't think that this will help out in this case, but it's worth a try.
SELECT o.o_num,
MAX(t.t_num)
FROM orders o
JOIN track t
ON o.o_num = t.o_num
AND t.t_status = 'CANCEL REQUEST'
WHERE t.t_out is not null
AND t.t_in >= getdate() -169 --*********this statement right here ***************
AND o.o_type IN (9, 30)
AND o.trn_id IN (24, 45, 25, 0)
AND NOT EXISTS (SELECT 1
FROM track tr
WHERE tr.o_num = o.o_num
AND tr.t_status IN ('CANCEL/BILL','CANCEL/NO BILL'))
GROUP BY o.o_num
OPTION (RECOMPILE);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 8:37 pm
Awesome. Tomorrow morning I will begin implementing these suggestions. I will then post the results of my findings. Thanks so much.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply