September 7, 2010 at 6:37 pm
Hi all,
Following is a small portion of a larger procedure that recently timed out. There are two queries listed below. The second query runs almost instantly. The only difference between the two queries is that the first uses an int variable in the where clause whereas the second is hard coded.
I've included the execution plans for both (as attachments) and the main difference is a hash match for the second, faster query.
Also, the "select count(*)" isn't actually being done (a few sums and group bys are in the actual query... I just took those out to simplify the code).
Two questions:
1. Why does the execution plan change?
2. Any suggestions to improve the performance (tuning advisor doesn't recommend anything)?
--slow
declare @so_no int select @so_no = 31324
select count(*) from wms_order_det_hist wh
join so_tasks t on wh.add_seq = t.id
where wh.so_no = @so_no
and wh.seq not in
(select x.seq from WMS_ORDER_DET_HIST x where x.add_seq = 0)
and so_ref not like '% VS'
and isnull(t.qty_picked,0) > isnull(t.qty_shipped,0)
and wh.pick_date in
(select max(wph.Pick_date) from wms_order_det_hist wph
where wph.so_no = wh.so_no and wph.so_ref = wh.so_ref and wph.add_seq = wh.add_seq)
--quick
select count(*) from wms_order_det_hist wh
join so_tasks t on wh.add_seq = t.id
where wh.so_no = 31324
and wh.seq not in
(select x.seq from WMS_ORDER_DET_HIST x where x.add_seq = 0)
and so_ref not like '% VS'
and isnull(t.qty_picked,0) > isnull(t.qty_shipped,0)
and wh.pick_date in
(select max(wph.Pick_date) from wms_order_det_hist wph
where wph.so_no = wh.so_no and wph.so_ref = wh.so_ref and wph.add_seq = wh.add_seq)
September 7, 2010 at 11:36 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Pics of the exec plans don't contain enough info.
Probably parameter sniffing (or lack thereof)
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 7:04 am
For detailed answers, please do as Gail requested.
For a quick answer, I'd look at this:
and so_ref not like '% VS'
That will lead to serious slow performance since the only way to satisfy it is to scan the table or index. No seeks will be possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2010 at 9:55 am
Gail, thanks. Attached are the execution plans and table definitions. I have whittled all but the one table out of the query and removed the "so_ref not like '% VS' " clause. The newly simplified queries follow at the end of the post (there are now three variations)
I read your article on parameter sniffing and created a sub-proc for the bit of code that is suffering.
Execution plan now matches the fast "hard coded" query and the query time has gone from greater than 1 minute to < 1 second.
Interesting. How would one guess at this inefficiency to begin with?
--DDL
CREATE TABLE [dbo].[WMS_ORDER_DET_HIST](
[SO_NO] [int] NOT NULL,
[SO_REF] [varchar](10) NOT NULL,
[WHS] [varchar](3) NOT NULL,
[SEQ] [int] NOT NULL,
[ADD_SEQ] [int] NOT NULL,
[PICK_DATE] [datetime] NOT NULL,
[QTY_ORD] [money] NULL,
[QTY_PICKED] [money] NULL,
[SERIAL_REQUIRED] [bit] NULL,
[COMMENT_TYPE] [varchar](1) NULL,
[PICK_COMMENT] [text] NULL,
[QA_COMMENT] [text] NULL,
[ITEM_CHECKED] [bit] NULL,
[TALLY_NUMBER] [int] NULL,
[PIPE_ITEM] [bit] NULL,
[VIEW_ONLY] [bit] NULL,
CONSTRAINT [PK_WMS_ORDER_DET_HIST] PRIMARY KEY CLUSTERED
(
[SO_NO] ASC,
[SO_REF] ASC,
[WHS] ASC,
[SEQ] ASC,
[ADD_SEQ] ASC,
[PICK_DATE] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [WMS_ORDER_DET_HIST_IX1] ON [dbo].[WMS_ORDER_DET_HIST]
(
[ADD_SEQ] ASC,
[SEQ] ASC
) ON [PRIMARY]
GO
--Slow (1.08 minutes)
declare @so_no int select @so_no = 31324
select count(wh.seq) from wms_order_det_hist wh
where wh.so_no = @so_no
and wh.seq not in
(select x.seq from WMS_ORDER_DET_HIST x where x.add_seq = 0)
and wh.pick_date in
(select max(wph.Pick_date) from wms_order_det_hist wph
where wph.so_no = wh.so_no and wph.so_ref = wh.so_ref and wph.add_seq = wh.add_seq)
--Fast (< 1 second)
select count(wh.seq) from wms_order_det_hist wh
where wh.so_no = 31324
and wh.seq not in
(select x.seq from WMS_ORDER_DET_HIST x where x.add_seq = 0)
and wh.pick_date in
(select max(wph.Pick_date) from wms_order_det_hist wph
where wph.so_no = wh.so_no and wph.so_ref = wh.so_ref and wph.add_seq = wh.add_seq)
--Fast as a proc (< 1 second)
create proc sp_Proc @so_no int as
select count(wh.seq) from wms_order_det_hist wh
where wh.so_no = @so_no
and wh.seq not in
(select x.seq from WMS_ORDER_DET_HIST x where x.add_seq = 0)
and wh.pick_date in
(select max(wph.Pick_date) from wms_order_det_hist wph
where wph.so_no = wh.so_no and wph.so_ref = wh.so_ref and wph.add_seq = wh.add_seq)
go
declare @so_no int select @so_no = 31324
exec sp_Proc @so_no
September 8, 2010 at 10:00 am
Grant, thanks... I'll review.
September 8, 2010 at 11:53 am
geerobg (9/8/2010)
Gail, thanks. Attached are the execution plans and table definitions. I have whittled all but the one table out of the query and removed the "so_ref not like '% VS' " clause. The newly simplified queries follow at the end of the post (there are now three variations)I read your article on parameter sniffing and created a sub-proc for the bit of code that is suffering.
Execution plan now matches the fast "hard coded" query and the query time has gone from greater than 1 minute to < 1 second.
Excellent. Do you still want some help with the query, or is it fine now?
Interesting. How would one guess at this inefficiency to begin with?
Experience 😀
Also known as 'been there, done that, got my fingers burnt and learnt from it'
Look at the exec plans, look at the estimated and actual row counts. On the slow query, you'll likely notice that there's a big difference. That's one of the key indicators that the plan may not be optimal.
Though, be aware of this: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 4:28 pm
GilaMonster (9/8/2010)
Excellent. Do you still want some help with the query, or is it fine now?
No thanks, it's fine now.
GilaMonster (9/8/2010)
Experience 😀Also known as 'been there, done that, got my fingers burnt and learnt from it'
Look at the exec plans, look at the estimated and actual row counts. On the slow query, you'll likely notice that there's a big difference. That's one of the key indicators that the plan may not be optimal.
Though, be aware of this: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
I suppose I need to work more on the "learnt" and less of the "burnt".
Thank you for the help... Parameter Sniffing huh? Ha.
September 8, 2010 at 5:13 pm
I'd also recommend that you pick up a copy of Grant's book to help you better understand.
September 8, 2010 at 6:50 pm
Steve Jones - Editor (9/8/2010)
I'd also recommend that you pick up a copy of Grant's book to help you better understand.
Indeed... in fact both of them.
Cheers.
September 8, 2010 at 11:12 pm
geerobg (9/8/2010)
I suppose I need to work more on the "learnt" and less of the "burnt".Thank you for the help... Parameter Sniffing huh? Ha.
If you're interested, that param sniffing article I linked to is part 2 of 3. The other 2 parts may be of interest too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply