Link to home
Start Free TrialLog in
Avatar of adrian78
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!
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

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?

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of adrian78
adrian78

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.
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?

The unions are done to join records that are related to 4 different date ranges.
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

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