Optimization - SQL Server Forums

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)

Optimization

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-12 : 10:01:12

Fady writes "Is there any way to optimize the following query??

SELECT DISTINCT TOTAL.RepGroupDivisionID, TOTAL.RepGroupDivisionName, TOTAL.VendorClassID, TOTAL.VCDescription, TOTAL.Sequence, TOTAL.TotalStores, SERV.TotalStoresServiced
FROM
(
SELECT Q1.VendorClassID, Q1.VCDescription, Q1.Sequence, Q1.RepGroupDivisionID, Q1.RepGroupDivisionName, Q2.TotalStores
FROM
(
SELECT DISTINCT VC.VendorClassID, VC.VCDescription, VC.Sequence, RGD.RepGroupDivisionID, RGD.RepGroupDivisionName
FROM RepGroupDivisions RGD, VendorClass VC, Departments D, Vendors V
WHERE VC.VendorClassID = V.VendorClassID
AND VC.DepartmentID = D.DepartmentID
AND RGD.RepGroupID IN ( 12 )

AND VendorID IN (
SELECT RGDHVHS.VendorID
FROM RepGroupDivisionHasVendorsHasSheets RGDHVHS
where RGDHVHS.RepGroupID IN ( 12 )
)
AND RGD.RepGroupDivisionID IN
(
SELECT RepGroupDivisionID FROM RepGroupDivisionHasStores RGDHS
JOIN Stores S ON S.StoreID = RGDHS.StoreID AND ( S.OpeningDate IS NULL OR S.OpeningDate <='2007-01-19 00:00:00.000')
AND ( S.ClosingDate IS NULL OR S.ClosingDate >'2007-02-02 23:59:59.997')
)
) AS Q1
JOIN
(
SELECT RGD.RepGroupDivisionID, RGD.RepGroupDivisionName, COUNT (DISTINCT RGDHS.StoreID)AS TotalStores
FROM RepGroupDivisions RGD
JOIN RepGroupDivisionHasStores RGDHS ON RGDHS.RepGroupDivisionID = RGD.RepGroupDivisionID
JOIN Stores S ON S.StoreID = RGDHS.StoreID
AND RGD.RepGroupID IN ( 12 )
AND ( S.OpeningDate IS NULL OR S.OpeningDate <='2007-01-19 00:00:00.000')
AND ( S.ClosingDate IS NULL OR S.ClosingDate >'2007-02-02 23:59:59.997')
GROUP BY RGD.RepGroupDivisionID,RGD.RepGroupDivisionName
) AS Q2
ON Q1.RepGroupDivisionID = Q2.RepGroupDivisionID
) AS TOTAL
LEFT JOIN
(
SELECT RGD.RepGroupDivisionID, VC.VendorClassID, VC.VCDescription, COUNT (DISTINCT S.StoreID) as TotalStoresServiced
FROM RR_Questionnaires
JOIN Vendors ON RR_Questionnaires.VendorID = Vendors.VendorID
JOIN VendorHasDepartments ON Vendors.VendorID = VendorHasDepartments.VendorID
JOIN Departments DP ON VendorHasDepartments.DepartmentID = DP.DepartmentID JOIN VendorClass VC ON VC.VendorClassID = Vendors.VendorClassID
JOIN Reps ON RR_Questionnaires.RepID = Reps.RepID
JOIN RepGroupDivisions RGD ON Reps.RepGroupDivisionID = RGD.RepGroupDivisionID JOIN RepGroups ON Reps.RepGroupID = RepGroups.RepGroupID
JOIN Stores S ON RR_Questionnaires.StoreID = S.StoreID
JOIN RepGroupDivisionHasStores RGDHS ON RGDHS.StoreID = S.StoreID WHERE RR_Questionnaires.QuestionnaireStartDateTime BETWEEN '2007-01-19 00:00:00.000' AND '2007-02-02 23:59:59.997'
AND RR_Questionnaires.TotalDuration >= 0
AND RGD.RepGroupID IN ( 12 )
AND ( S.OpeningDate IS NULL OR S.OpeningDate <='2007-01-19 00:00:00.000')
AND ( S.ClosingDate IS NULL OR S.ClosingDate >'2007-02-02 23:59:59.997')
AND EXISTS (
SELECT DISTINCT RGDHVHS.VendorID
From RepGroupDivisionHasVendorsHasSheets RGDHVHS, Vendors V
where RGDHVHS.VendorID = V.VendorID
AND RepGroupID IN ( 12 )
)
GROUP BY RGD.RepGroupDivisionID,VC.VendorClassID,VC.VCDescription
)AS SERV
ON SERV.RepGroupDivisionID = TOTAL.RepGroupDivisionID AND SERV.VendorClassID = TOTAL.VendorClassID
ORDER BY TOTAL.Sequence,TOTAL.VCDescription, TOTAL.VendorClassID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 10:20:54

Very hard to try when not all columns are prefixed with table name.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 10:23:23

But it seems possible to do.

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-02-12 : 11:34:41

Please post the matching query plan.....so that we can spot the "performance hot spots"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:42:31

The INNER JOIN thingy is almost exactly as the LEFT JOIN thingy.
I would thing you can save time and speed by store the result in a temporary table and do a self LEFT JOIN with complete criteria.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources