We help IT Professionals succeed at work.

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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
does it even work?

 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?

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT DISTINCT
      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')
        OR (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')
        OR (i2.stamp = choices.enddate AND i1.stamp = choices.startdate AND choices.startdate > 'October 31 2011' AND choices.enddate < 'November 03 2011'))
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

Commented:
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.

Author

Commented:
Thanks, Scott.  I'll give that a try soon and get back to you!
CERTIFIED EXPERT
Top Expert 2011

Commented:
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?

Author

Commented:
The unions are done to join records that are related to 4 different date ranges.

Author

Commented:
Sorry... rather sum up four different date ranges.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
Thanks for your help guys.  I need the duplicates to be removed so that's not a worry.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.