optimizing query 1

[TerraSamba]

Guys, I have a query that is executing somewhat slowly ...
Here is the query:

SELECT @strPeriod AS Period,
__QCWhs_W1.WhsShortName,
__QCInput.strWhsProdCode,
__QCInput.strCPostOrig,
SUM(__QCInput.decUnitsOrig) AS decUnitsOrigSum,
SUM(__QCInput.decValuesOrig) AS decValuesOrigSum

INTO dbo.__QCInput_W1
FROM __QCWhs_W1
INNER JOIN
__QCInput ON __QCWhs_W1.sinWhsCodeID = __QCInput.sinWhsCodeID
LEFT OUTER JOIN
__QCPeriods_W1 ON __QCInput.sinPeriodID = __QCPeriods_W1.sinInputPeriodID
GROUP BY
__QCInput.strWhsProdCode,
__QCInput.strCPostOrig,
__QCWhs_W1.WhsShortName

__QCPeriods is a small table (15 recs) and so is __QCWhs_W1 (10 recs). __QCInput is somewhat bigger, about 12 million recs. The result will contain about 3.2 million records.

Does anyone have some optimisation tips? __QCInput is well indexed. But I want to know if I can get more speed moving the joins to the where clause or something ...

Help appreciated !!!

The gap between theory and practice is not as wide in theory as it is in practice.

 

[zhavic]

Have you also indexed __QCWhs_W1 table ( on sinWhsCodeID column )
and __QCPeriods_W1 table ( on sinPeriodID column ) ?

Also try to change tables __QCWhs_W1 and __QCInput
( SQL server has its own optimization, but try it )

SELECT @strPeriod AS Period,
__QCWhs_W1.WhsShortName,
__QCInput.strWhsProdCode,
__QCInput.strCPostOrig,
SUM(__QCInput.decUnitsOrig) AS decUnitsOrigSum,
SUM(__QCInput.decValuesOrig) AS decValuesOrigSum

INTO dbo.__QCInput_W1
FROM __QCInput
INNER JOIN __QCWhs_W1 ON __QCWhs_W1.sinWhsCodeID = __QCInput.sinWhsCodeID
LEFT OUTER JOIN
__QCPeriods_W1 ON __QCInput.sinPeriodID = __QCPeriods_W1.sinInputPeriodID
GROUP BY
__QCInput.strWhsProdCode,
__QCInput.strCPostOrig,
__QCWhs_W1.WhsShortName



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[TerraSamba]

The extra indexes made no difference, but the table-order change saves me about 30 secs !

Great thanx.

I have added an extra where clause to exclude the null values, this together with your tip has finally put this query down from about 5 mins to less then 2 mins. Which is more than I had expected. Great, thanx Zhavic.

The gap between theory and practice is not as wide in theory as it is in practice.

 

[clarkin]

http://www.sql-server-performance.com

has a lot of very good performance tips - worth a browse.
What was the final order of your tables? Biggest, small, small?

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[clarkin]

addendum: specifically check out this one -

http://www.sql-server-performance.com/tuning_joins.asp

as I think your main bottleneck is your JOINs

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[TerraSamba]

Yep: final is :Select from BIGONE
inner join on small
inner join on small

It is impressive how much time it saves, just changing the order and playing around. It now executes within a minute,
and I will not be touching it for a while.

I will have a look that the performance tips. Thanx clarkin

The gap between theory and practice is not as wide in theory as it is in practice.