Speed up query 1

[jadams0173]

Hey all. I'm using sql 2000.

I have a query that I need to speed up. It only takes about 3 seconds to run it but I'm running in a cursor at the moment..I know that's bad.

It takes about 26 minutes to run and need to reduce that as much as possible. I'd post the execution plan but I'm not sure how to as it is pretty large.

There is a Hash match/right outer join and a Parallelism/repartion streams that each to 32% of the total time.

Here is the query.

[COLOR=blue]Set[/color] [COLOR=#FF00FF]Nocount[/color] [COLOR=blue]On[/color]

[COLOR=blue]Declare[/color] @Qty		[COLOR=blue]int[/color],
	   @ChildPN	[COLOR=blue]Varchar[/color](25),
	   @HLAPlanned [COLOR=blue]int[/color],
	   @LLARemain	[COLOR=blue]int[/color],
	   @Unallocated [COLOR=blue]int[/color]

[COLOR=blue]Set[/color] @Qty = 1[COLOR=green]--@pQty
[/color][COLOR=blue]Set[/color] @ChildPN = [COLOR=red]'123456R'[/color][COLOR=green]--@pChildPN
[/color]
[COLOR=green]--LLA Required (Sum HLA needs)
[/color][COLOR=blue]Select[/color] @HLAPlanned =  sum(([COLOR=#FF00FF]coalesce[/color](b.RelPlan,0)* @Qty + [COLOR=#FF00FF]coalesce[/color](a.ATR,0)*@Qty) + [COLOR=#FF00FF]coalesce[/color](a.WIP,0) + [COLOR=#FF00FF]coalesce[/color](OpenPicks,0) + [COLOR=#FF00FF]coalesce[/color](c.IndDmdQTY,0))
[COLOR=blue]FROM[/color] V_Parent_Child_No_Ind_demand_JH a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
 	(
		[COLOR=blue]Select[/color] Parent "ParentPN"
		[COLOR=blue]from[/color] V_Parent_Child_No_Ind_demand_JH
		[COLOR=blue]where[/color] item = @ChildPN
	) [COLOR=blue]d[/color]
	[COLOR=blue]on[/color] a.Item = [COLOR=blue]d[/color].ParentPN
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] ReleasePlan b
	[COLOR=blue]on[/color] a.Item = b.Item
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] IndDemand c
	[COLOR=blue]on[/color] a.item = c.ChildPN

[COLOR=green]--this is the remaing qty of child Pn's that haven't been consumed
[/color][COLOR=blue]Select[/color] @LLARemain = LLARemain [COLOR=blue]from[/color] LLAPlanned [COLOR=blue]Where[/color] ChildPN = @ChildPN

[COLOR=blue]Set[/color] @UnAllocated = @LLARemain - @HLAPlanned

[COLOR=blue]Update[/color] LLAPlanned
[COLOR=blue]Set[/color] LLARemain = @Unallocated
[COLOR=blue]Where[/color] ChildPN = @ChildPN

 

[gmmastros]

If I had to guess, I would say that most of your speed improvements will come from creating better indexes. Have you tried running this query through the database engine tuning advisor?

If you are using the old Query Analyzer, the steps will be similar, but slightly different.

Load the query in to a query window.
Click Query -> Analyze Query in Database Engine Tuning Advisor

If you can't use this tool for some reason, then can you post dome information about your tables?

Select Count(*) From V_Parent_Child_No_Ind_demand_JH
Select Count(*) From ReleasePlan
Select Count(*) From ReleasePlan
Select Count(*) From LLAPlanned


sp_helpIndex 'V_Parent_Child_No_Ind_demand_JH'
sp_helpIndex 'ReleasePlan'
sp_helpIndex 'ReleasePlan'
sp_helpIndex 'LLAPlanned'


Also, based on the name, it seems like V_Parent_Child_No_Ind_demand_JH is a view. Is this correct? Can you post the definition of this view?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey George! I ran the index tuning wizard and it dropped one of my existing indexs and added 2 more. The query then ran in 1:50 seconds and then in 1:35. I also updated the statistics on each table.

But for some reason when I execute this SP from vb6 it goes unresponsive and times out. I have the timeout set at 720 seconds.

here is the info you wanted even though I ran the tuner.
V_Parent_Child_No_Ind_demand_JH
-------------------------------
2035

ReleasePlan
-----------
319

IndDemand
-----------
30

LLAPlanned
-----------
780


sp_helpIndex 'ReleasePlan'
index_name index_description index_keys
PK__tblSchedRel__369C13AA clustered, unique, primary key located on PRIMARY IDCol

/*-----------------------------
sp_helpIndex 'IndDemand'
-----------------------------*/
index_name index_description index_keys
IDX_ChildPN nonclustered located on PRIMARY ChildPN
IndDemandMyID_PK nonclustered, unique, primary key located on PRIMARY IndID

/*-----------------------------
sp_helpIndex 'LLAPlanned'
-----------------------------*/
index_name index_description index_keys
IDX_ChildPN clustered, unique located on PRIMARY ChildPN

/*-----------------------------
sp_helpIndex 'V_Parent_Child_No_Ind_demand_JH'
-----------------------------*/
The object does not have any indexes.

Here is the definition of the view

[COLOR=blue]select[/color] [COLOR=#FF00FF]RTRIM[/color]([COLOR=#FF00FF]LTRIM[/color](Item)) "ITEM",
	[COLOR=#FF00FF]RTRIM[/color]([COLOR=#FF00FF]LTRIM[/color](PARENT)) "PARENT",
	DRWG ,
	relationship,
	[COLOR=#FF00FF]level[/color] ,
	[COLOR=blue]TYPE[/color],
	[COLOR=#FF00FF]coalesce[/color](SALES_PRICE,0) "SALES_PRICE",
	Rec_id,
	Quantity,
	Ind_Demand,
	COST_FMLY ,
	COM_TYP,
	[COLOR=#FF00FF]COALESCE[/color](ATR,0) "ATR",
	WIP,
	[COLOR=#FF00FF]COALESCE[/color](ALL_FG,0) "ALL_FG",
	[COLOR=#FF00FF]COALESCE[/color](OPEN_PICKS,0) "OpenPicks",
	Rel_Plan,
	[COLOR=#FF00FF]COALESCE[/color](STD_PRICE,0) "STD_PRICE",
	[COLOR=#FF00FF]COALESCE[/color](VAM,0) "VAM",
	IN_TRANSIT_HUB,
	HUB,
	FLEX_FG,
	FLEX_SFG,
	[03_PACK],
	[COLOR=#FF00FF]COALESCE[/color](LT,0) "LT",
	1 "SMT_SETUP",
	SMT_1ST,
	SMT_2ND,
	RATE_1ST,
	RATE_2ND
[COLOR=blue]from[/color] v_parent_child_JH 
[COLOR=blue]Where[/color] Ind_demand = [COLOR=red]'N'[/color]

And the definition of v_parent_child_JH is:

[COLOR=blue]select[/color] [COLOR=#FF00FF]rtrim[/color](Component) "ITEM" , [COLOR=#FF00FF]COALESCE[/color](A.PARENT,[COLOR=#FF00FF]RTRIM[/color](COMPONENT)) "PARENT", A.DRWG,
			Relationship = [COLOR=blue]Case[/color]
			[COLOR=blue]When[/color] [[COLOR=#FF00FF]Level[/color]] = [COLOR=red]'1'[/color] [COLOR=blue]then[/color] [COLOR=red]'Parent'[/color]
			[COLOR=blue]When[/color] [[COLOR=#FF00FF]Level[/color]] = [COLOR=red]'2'[/color] [COLOR=blue]then[/color] [COLOR=red]'Child'[/color]
			[COLOR=blue]end[/color],
	 Quantity,Rec_ID,[[COLOR=#FF00FF]Level[/color]],
[COLOR=blue]case[/color]
	[COLOR=blue]When[/color] [COLOR=#FF00FF]rtrim[/color](a.Component) = [COLOR=#FF00FF]rtrim[/color](b.Item) and a.level = [COLOR=red]'1'[/color] [COLOR=blue]then[/color] [COLOR=red]'Y'[/color] [COLOR=blue]else[/color] [COLOR=red]'N'[/color]
	[COLOR=blue]end[/color] "Ind_Demand", A.TYPE,A.SALES_PRICE,a.COST_FMLY,a.COM_TYP,A.STD_PRICE,A.VAM,
	A.ATR, A.LT , A.OPEN_PICKS,A.WIP, A.SMT_1ST,A.SMT_2ND,A.RATE_1ST,A.RATE_2ND,
	A.[03_PACK],A.IN_TRANSIT_HUB,A.FLEX_FG,A.FLEX_SFG,A.HUB,A.ALL_FG,A.REL_PLAN

[COLOR=blue]from[/color] Scheduler_Parent_Child a

	[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
			[COLOR=blue]select[/color] [COLOR=#FF00FF]rtrim[/color](Component) "Item"
			[COLOR=blue]from[/color] Scheduler_Parent_Child
			[COLOR=blue]Where[/color] [[COLOR=#FF00FF]Level[/color]] = [COLOR=red]'2'[/color]
			[COLOR=green]/*Where Case
[/color][COLOR=green]				When [Level] = 1 then 'Parent'
[/color][COLOR=green]				When [Level] = 2 then 'Child'
[/color][COLOR=green]				end = 'Child'*/[/color]
		) b
[COLOR=blue]on[/color] [COLOR=#FF00FF]rtrim[/color](a.Component) = [COLOR=#FF00FF]rtrim[/color](b.Item)

[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]rtrim[/color](Component),[COLOR=#FF00FF]COALESCE[/color](A.PARENT,[COLOR=#FF00FF]RTRIM[/color](COMPONENT)),A.DRWG,
			[COLOR=blue]Case[/color]
			[COLOR=blue]When[/color] [[COLOR=#FF00FF]Level[/color]] = [COLOR=red]'1'[/color] [COLOR=blue]then[/color] [COLOR=red]'Parent'[/color]
			[COLOR=blue]When[/color] [[COLOR=#FF00FF]Level[/color]] = [COLOR=red]'2'[/color] [COLOR=blue]then[/color] [COLOR=red]'Child'[/color]
			[COLOR=blue]end[/color] ,
Quantity,Rec_ID,[[COLOR=#FF00FF]Level[/color]],
[COLOR=blue]case[/color]
	[COLOR=blue]When[/color] [COLOR=#FF00FF]rtrim[/color](a.Component) = [COLOR=#FF00FF]rtrim[/color](b.Item) and a.level = [COLOR=red]'1'[/color] [COLOR=blue]then[/color] [COLOR=red]'Y'[/color] [COLOR=blue]else[/color] [COLOR=red]'N'[/color]
	[COLOR=blue]end[/color],
 	A.TYPE,A.SALES_PRICE,a.COST_FMLY,a.COM_TYP,A.STD_PRICE,A.VAM,
	A.ATR, A.LT,A.OPEN_PICKS,A.WIP, A.SMT_1ST,A.SMT_2ND,A.RATE_1ST,A.RATE_2ND,
	A.[03_PACK],A.IN_TRANSIT_HUB,A.FLEX_FG,A.FLEX_SFG,A.HUB,A.ALL_FG,A.REL_PLAN

 

[gmmastros]

It looks like that last view is going to cause your biggest problem. I would suggest that you devote your attention there.

Specifically this....

[tt][blue]on rtrim(a.Component) = rtrim(b.Item)[/blue][/tt]

and

[tt][blue]group by rtrim(Component), .....[/blue][/tt]

Try running the view as is (Select * From v_parent_child_JH ) and note the time. Then remove the RTrim's and try again. Is it any faster? Does it return the same data? I suspect the RTrims are not necessary.

What indexes do you have on the Scheduler_Parent_Child table?

sp_helpindex 'Scheduler_Parent_Child '




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

I removed every Rtrim in v_parent_child_JH. I got the same record count back and the speed was 0 seconds on both.

Here are the indexs on Scheduler_Parent_Child
index_name,index_description,index_keys
IDX_COMPONENT,nonclustered located on PRIMARY,Component
IDX_Level,nonclustered located on PRIMARY,Level
IDX_PARENT,nonclustered located on PRIMARY,Parent

Thanks for helping me with this.

 

[gmmastros]

Just curious, is the original query any faster now? Originally it was 3 seconds, then it got down to 1.5 seconds. What is it now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Now it is less than 1 second.

Now when I run it through with the cursor it runs in 1:30 seconds.

But when I run from VB it still times out and runs > 10 min. and then I kill it.