Making my statement faster....? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Making my statement faster....?

Author

Topic

slboytoy
Starting Member

30 Posts

Posted - 2005-01-03 : 11:26:45

This is all new to me, (sql) and now I am trying to read up on how to make Selects more efficent and faster.

This is one of my main statements that takes most of the time (68%)

SELECT     CONVERT(varchar(4),HDR.MarketingClockNumber), HDR.PlantNumber, HDR.QuoteNumber, HDR.EffectiveStartDate, HDR.CustomerNumber, 
                      	HDR.CustomerName, (ITM.NetUnitPrice + SUM(ISNULL(ITX.Adder, 0))) * ITM.Quantity * ITM.DiscountMultiplier AS QuoteNetValue, 
		HDR.YourInquiry, ITM.LineItem  
FROM         DataWarehouse.dbo.QuoteHDR HDR  LEFT OUTER JOIN DataWarehouse.dbo.QuoteITM ITM ON HDR.PlantNumber = ITM.PlantNumber AND HDR.QuoteNumber = ITM.QuoteNumber LEFT OUTER JOIN
                     		DataWarehouse.dbo.QuoteITX ITX ON HDR.PlantNumber = ITX.PlantNumber AND HDR.QuoteNumber = ITX.QuoteNumber AND ITM.LineItem = ITX.ItemNumber
	WHERE     (HDR.PlantNumber = '12' OR
                      HDR.PlantNumber = '18' OR
                      HDR.PlantNumber = '19' OR
                      HDR.PlantNumber = '22' OR
                      HDR.PlantNumber = '23') AND    (ITX.PlantNumber = '12' OR
                      ITX.PlantNumber = '18' OR
                      ITX.PlantNumber = '19' OR
                      ITX.PlantNumber = '22' OR
                      ITX.PlantNumber = '23') AND   (ITX.PlantNumber = '12' OR
                      ITM.PlantNumber = '18' OR
                      ITM.PlantNumber = '19' OR
                      ITM.PlantNumber = '22' OR
                      ITM.PlantNumber = '23') AND (HDR.EffectiveStartDate BETWEEN CONVERT(DATETIME, '2004-10-25 00:00:00', 102) 
		AND CONVERT(DATETIME, '2005-6-30 00:00:00', 102)) 
		AND (HDR.QuoteNumber NOT IN
	                          (SELECT QuoteNumber  FROM QuoteFeedback.dbo.QuoteTracking   ) )  GROUP BY HDR.MarketingClockNumber, HDR.PlantNumber, HDR.QuoteNumber, HDR.EffectiveStartDate, HDR.CustomerNumber, 
		                      HDR.CustomerName, HDR.YourInquiry, ITM.DiscountMultiplier, ITM.Quantity, ITM.LineItem, ITM.NetUnitPrice

For indexes, i have clustered, Plant Number, QuoteNumber, and LineItem. Here is the Execution Plan.

QuoteHDR has 0.2 Million
QuoteITM has 0.5 Million
QuoteITX has 1.5 Million

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 11:42:01

try this. you probably don't need to check plant numbers in all three tables as they are the same.
could you do an inner joins instead of left joins?


WHERE
(HDR.PlantNumber = '12' OR
 HDR.PlantNumber = '18' OR
 HDR.PlantNumber = '19' OR
 HDR.PlantNumber = '22' OR
 HDR.PlantNumber = '23') 
 AND (HDR.EffectiveStartDate BETWEEN '2004.10.25' AND '2005.06.30') AND (not exists (SELECT QuoteNumber FROM QuoteFeedback.dbo.QuoteTracking where HDR.QuoteNumber = QuoteNumber ) )

Go with the flow & have fun! Else fight the flow

slboytoy
Starting Member

30 Posts

Posted - 2005-01-03 : 14:55:31

When I added to check all three plant numbers, it dropped my time by 3 seconds. Not really sure why it makes a difference, to me it looked like it would work harder, that's why I didn't put it. But for some reason, it works.

Sadly I have to use left joins because sometimes, they will have a quote, with no money. So it will be in HDR, but not ITX, or ITM.
Right now it's around 7 seconds to do my search.

I just wanted to see if I was doing things right, if I was going in the right dirrection with setting up searches and stuff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 14:58:38

well the look of your exec plan is ok to me...
is there a way you could use less rows in QuoteITX?

Go with the flow & have fun! Else fight the flow

slboytoy
Starting Member

30 Posts

Posted - 2005-01-03 : 15:37:51

By 'Less Rows', do you mean, to filter out/match up the data better, or to physcially have less rows? To match up, I only have two columns (plant number and Quote number), and I can't delete any data, i have to use all the rows. Thanks for lookin at it though, just wanted to make sure i'm going the right way.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 15:54:12

i mean filter them out

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources