adrian78
asked on
Optimize SQL Server 2005 Query
Is there a way to optimize / re-write the following query:
SELECT name, organization, max(status) as status, sum(total) as total FROM
(
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = 'November 03 2011' AND i1.stamp = 'October 31 2011 AND choices.startdate <= 'October 31 2011' AND choices.enddate >= 'November 03 2011'
UNION
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = choices.enddate AND i1.stamp = 'October 31 2011' AND choices.startdate <= 'October 31 2011' AND choices.enddate < 'November 03 2011' AND choices.enddate >= 'October 31 2011'
UNION
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = 'November 03 2011' AND i1.stamp = choices.startdate AND choices.startdate > 'October 31 2011' AND choices.startdate <= 'November 03 2011' AND choices.enddate >= 'November 03 2011'
UNION SELECT 0 as SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = choices.enddate AND i1.stamp = choices.startdate AND choices.startdate > 'October 31 2011' AND choices.enddate < 'November 03 2011'
) as totals GROUP BY name, organization ORDER BY name, total DESC
Thanks in advance!
SELECT name, organization, max(status) as status, sum(total) as total FROM
(
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = 'November 03 2011' AND i1.stamp = 'October 31 2011 AND choices.startdate <= 'October 31 2011' AND choices.enddate >= 'November 03 2011'
UNION
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = choices.enddate AND i1.stamp = 'October 31 2011' AND choices.startdate <= 'October 31 2011' AND choices.enddate < 'November 03 2011' AND choices.enddate >= 'October 31 2011'
UNION
SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = 'November 03 2011' AND i1.stamp = choices.startdate AND choices.startdate > 'October 31 2011' AND choices.startdate <= 'November 03 2011' AND choices.enddate >= 'November 03 2011'
UNION SELECT 0 as SELECT
dbo.myusers.name,
dbo.choices.status,
i2.s1*dbo.rsystem.s1 + i2.s2*dbo.rsystem.s2 + i2.s3*dbo.rsystem.s3 - i1.s1*dbo.rsystem.s1 - i1.s2*dbo.rsystem.s2 - i1.s3*dbo.rsystem.s3 AS total
FROM dbo.accounts
INNER JOIN dbo.myusers ON dbo.accounts.id = dbo.myusers.accountid
INNER JOIN dbo.choices ON dbo.myusers.id = dbo.choices.myuserid
INNER JOIN dbo.s ON dbo.choices.choiceid = dbo.s.id
INNER JOIN s_history i1 ON dbo.choices.choiceid = i1.id
INNER JOIN s_history i2 ON dbo.choices.choiceid = i2.id
INNER JOIN dbo.rsystem ON dbo.choices.rsystemid = dbo.rsystem.id
WHERE dbo.accounts.id=39821
AND i2.stamp = choices.enddate AND i1.stamp = choices.startdate AND choices.startdate > 'October 31 2011' AND choices.enddate < 'November 03 2011'
) as totals GROUP BY name, organization ORDER BY name, total DESC
Thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Outer query stays the same ... altho I'm not sure what column in the inner query that "organization" in the outer query refers to.
SELECT name, organization, max(status) as status, sum(total) as total FROM (\
... query as above ...
) AS totals
GROUP BY name, organization
ORDER BY name, total DESC
SELECT name, organization, max(status) as status, sum(total) as total FROM (\
... query as above ...
) AS totals
GROUP BY name, organization
ORDER BY name, total DESC
ASKER
Sorry I re-wrote the query as I wasn't allowed to use the real db field names for security reasons... you can remove that 'SELECT 0' part.
ASKER
Thanks, Scott. I'll give that a try soon and get back to you!
can you tell us what it is trying to achieve...
are the union's done for a real purpose (deduplication?)
or just simplicity...
do you have indexes on the joins and where clause columns?
how many rows are in the tables ?
selected?
output?
are the union's done for a real purpose (deduplication?)
or just simplicity...
do you have indexes on the joins and where clause columns?
how many rows are in the tables ?
selected?
output?
ASKER
The unions are done to join records that are related to 4 different date ranges.
ASKER
Sorry... rather sum up four different date ranges.
yes but since you use union rather than union all...
the statement will remove any duplicate rows found in the set ...
therefore your result may not be what you desire....
can you explain the relationship types (1:1 or 1:m) especially for the choices / History joins
the statement will remove any duplicate rows found in the set ...
therefore your result may not be what you desire....
can you explain the relationship types (1:1 or 1:m) especially for the choices / History joins
ASKER
Thanks for your help guys. I need the duplicates to be removed so that's not a worry.
UNION SELECT 0 as SELECT ?
can you tell us what it is trying to achieve...
are the union's done for a real purpose (deduplication?)
or just simplicity...
do you have indexes on the joins and where clause columns?
how many rows are in the tables ?
selected?
output?