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
 General SQL Server Forums
 New to SQL Server Programming
 Is there a better way (unions)?

Author  Topic 

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-06-29 : 14:15:12
I've got a huge select statement that's a bunch of smaller select statements all unioned together. Is there a better way to build a select statement? I guess it's build this way to easily add information to the query as needed, but it takes an awful long time to run.

Any suggestions?

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-06-29 : 14:40:22
There may not be a better way, but without posting your code,table structures and indexes there's no way we can know.

Jim
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-29 : 14:59:48
I Agree with Jim. But one thing about the limited info you did provide is I hope that those are UNION ALL statements. UNION will DISTINCT each of the individual results first which will definately slow things down if you don't need that.

Be One with the Optimizer
TG
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-06-29 : 17:56:13
Well, that's the problem. All I have access to is the select statement. And it actually is using UNION instead of UNION ALL. Also, it's one big inline select statement (vb script) instead of a stored proc.

I'm under the impression that just making it a stored procedure will speed things up quite a bit. The select statement is some 400 lines long. Think it will?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-29 : 18:45:25
I'll probably regret this but...
I can almost guarantee that the statement can be vastly improved whether you keep it inline or make it an SP. Capture the statement using Sql Profiler and post it here.

Be One with the Optimizer
TG
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-01 : 16:49:17
Wish I could, I don't have Enterprise Manager. Plus, I was wrong, the query formatted correctly is at 1,815 lines long.

It's huge, but if you're willing, I could send it to you in a .txt and anything you think that will help I'll make examples of and post them in this thread for others to see.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-07-01 : 16:54:58
quote:
Originally posted by TG

I'll probably regret this but...


See

no good deed goes unpunished

My advice....make the guy who wrote it, perf tune it

EDIT: Oh, and there is now way it's that long. SQL Server would choke...there is an upper limit of the number of chars it can accept I believe...and even if it did....I would guess it would never come home.


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-01 : 17:26:25
It's actually not a difficult query, here's a rough example...

select 

from   (select ...
        from   ...
        where  ...
        group by ...
        union
        select ...
        from   ...
        where  ...
        group by ...
        union
        select ...
        from   ...
        where  ...
        group by ...
        union 
        ...
        )
where   

group by

order by


Each select in the sub select is doing the exact same query, just w/ small changes each time, then doing a union. I'll make another post w/ a better example of what's changing each time (have to come up with an example that shows what's going on)...

It's just very VERY long
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-01 : 18:02:15
Here's an example of the code, cut down to make it easier to understand...

notice how the conditions in all the from sections change slightly, and how these variables keep changing in the select statements:
'QAdefect', 'Prodclar', 'Proddefect', ReadyBuild_TimeStamp', 'AcceptCode_TimeStamp', 'QA_TimeStamp'


look further down for code example, had to edit it.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-07-01 : 18:47:27
Is the only that is changing is what column has the COUNT(i.tz_found_in_release) and the c.tz_time (I also don't see a C table alias)?? Maybe there are some subtle differences, but I'm not seeing them at first glance. Can you compact it down a bit?? Soemthing like: 
SELECT   r.tz_id,  
       r.tz_laststatechangedate, 
       Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
       r.tz_rm_target, 
       r.tz_projectid, 
       r.tz_issueid,  
       Count(i.tz_found_in_release)                   AS 'QAdefect', 
       Count(i.tz_found_in_release)                   AS 'Prodclar', 
       Count(i.tz_found_in_release)                   AS 'Proddefect', 
       r.tz_title, 
       c.tz_time                                      AS 'ReadyBuild_TimeStamp', 
       c.tz_time                                      AS 'AcceptCode_TimeStamp', 
       c.tz_time                                      AS 'QA_TimeStamp', 
       Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
       s.TZ_Name                                      AS 'TZ_Name', 
       r.tz_submitdate
FROM     zzz_releases r WITH (NOLOCK) 
       LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
         ON r.tz_state = s.tz_id 
       LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
         ON u.tz_id = r.tz_release_manager 
       LEFT OUTER JOIN tz_projectz p WITH (NOLOCK) 
         ON r.tz_projectid = p.tz_id 
WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
       AND r.HIGH_LEVEL IS NOT NULL 
GROUP BY r.tz_id,  
       r.tz_laststatechangedate, 
       Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
       r.tz_rm_target, 
       r.tz_projectid, 
       r.tz_issueid,  
       r.tz_title, 
       c.tz_time,
       Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
       s.TZ_Name                                      AS 'TZ_Name', 
       r.tz_submitdate
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-07-01 : 20:41:15
Makes no sense, the counts would ALWAYS be the same



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-07-02 : 11:38:36
quote:
Originally posted by X002548

Makes no sense, the counts would ALWAYS be the same
Agreed, but would they be any different in each of the other queries above?
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-02 : 11:42:31
crap, I forgot to change the FROM section when I was making it easier to read...

I'll repost w/ updated from section in a min...
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-02 : 12:17:19
here is the correct version, sorry

The count does change each time w/ the changes in the from section. Can't believe I forgot to include that... it's the main reason for the UNIONS.

notice how the conditions in all the from sections change slightly, and how these variables keep changing in the select statements:
'QAdefect', 'Prodclar', 'Proddefect', ReadyBuild_TimeStamp', 'AcceptCode_TimeStamp', 'QA_TimeStamp'

SELECT   all.tz_id, 
         all.tz_laststatechangedate, 
         all.HIGH_LEVEL,   
         Sum(all.[ActQAdefect])    AS 'ActQAdefect', 
         Sum(all.[Prodclar])       AS 'Prodclar', 
         Sum(all.[Proddefect])     AS 'Proddefect', 
         all.tz_title, 
         Max(all.ReadyBuild_TimeStamp)  AS 'ReadyBuild_TimeStamp', 
         Max(all.AcceptCode_TimeStamp)   AS 'AcceptCode_TimeStamp', 
         Max(all.QA_TimeStamp)           AS 'QA_TimeStamp', 
         all.TZ_description, 
         all.TZ_Name, 
         all.tz_submitdate, 
FROM     (SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projectz p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          UNION 
		  SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   Count(i.tz_found_in_release)                   AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN zzz_issues i WITH (NOLOCK) 
                     ON i.tz_found_in_release = r.tz_id 
                   LEFT OUTER JOIN ts_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN ts_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projects p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   Count(i.tz_found_in_release)                   AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          UNION  
		  SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0						                      AS 'QAdefect', 
                   Count(i.tz_found_in_release)                   AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projectz p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0						                      AS 'QAdefect', 
                   Count(i.tz_found_in_release)                   AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
	      UNION  
			SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   Count(i.tz_found_in_release)                   AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN tz_changes c WITH (NOLOCK) 
                     ON (r.ts_id = c.tz_caseid) 
                        AND (c.tz_priorint = 15 
                              OR c.tz_priorint = 56 
                              OR c.tz_priorint = 102) 
                        AND (c.tz_newint = 16 
                              OR c.tz_newint = 90) 
                        AND c.tz_fldid = 138 
                   LEFT OUTER JOIN zzz_issues i WITH (NOLOCK) 
                     ON i.tz_found_in_release = r.tz_id 
                        AND (i.tz_found_in = 19 
                              OR i.tz_found_in = 21 
                              OR i.tz_found_in = 22) 
                        AND (i.tz_severity <> 16 
                              OR i.tz_severity <> 17) 
                        AND i.tz_issuetype = 3 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projects p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   Count(i.tz_found_in_release)                   AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
			UNION 
			SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   c.tz_time                                      AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN tz_changes c WITH (NOLOCK) 
                     ON (r.tz_id = c.tz_caseid) 
                        AND (c.tz_priorint = 15 
                              OR c.tz_priorint = 56 
                              OR c.tz_priorint = 102) 
                        AND (c.tz_newint = 16 
                              OR c.tz_newint = 90) 
                        AND c.tz_fldid = 138 
                   LEFT OUTER JOIN zzz_issues i WITH (NOLOCK) 
                     ON i.tz_found_in_release = r.tz_id 
                        AND (i.tz_found_in = 19 
                              OR i.tz_found_in = 21 
                              OR i.tz_found_in = 22) 
                        AND (i.tz_severity <> 16 
                              OR i.tz_severity <> 17) 
                        AND i.tz_issuetype <> 3 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projects p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   c.tz_time                                      AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
		    UNION 
			SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   c.tz_time                                      AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r 
                   LEFT OUTER JOIN tz_changes c WITH (NOLOCK) 
                     ON (r.tz_id = c.tz_caseid) 
                        AND (c.tz_priorint = 12) 
                        AND c.tz_newint = 96 
                        AND c.tz_fldid = 138 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projects p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   c.tz_time                                      AS 'AcceptCode_TimeStamp', 
                   0                                              AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate	
		    UNION 
			SELECT   r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2))            AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   c.tz_time                                      AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate
          FROM     zzz_releases r WITH (NOLOCK) 
                   LEFT OUTER JOIN tz_states s WITH (NOLOCK) 
                     ON r.tz_state = s.tz_id 
                   LEFT OUTER JOIN tz_users u WITH (NOLOCK) 
                     ON u.tz_id = r.tz_release_manager 
                   LEFT OUTER JOIN tz_projectz p WITH (NOLOCK) 
                     ON r.tz_projectid = p.tz_id 
          WHERE    Isnumeric(r.HIGH_LEVEL) = 1 
                   AND r.HIGH_LEVEL IS NOT NULL 
          GROUP BY r.tz_id,  
                   r.tz_laststatechangedate, 
                   Cast(r.HIGH_LEVEL AS DECIMAL(10,2)) AS 'HIGH_LEVEL', 
                   r.tz_rm_target, 
                   r.tz_projectid, 
                   r.tz_issueid,  
                   0                                              AS 'QAdefect', 
                   0                                              AS 'Prodclar', 
                   0                                              AS 'Proddefect', 
                   r.tz_title, 
                   0                                              AS 'ReadyBuild_TimeStamp', 
                   0                                              AS 'AcceptCode_TimeStamp', 
                   c.tz_time                                      AS 'QA_TimeStamp', 
                   Cast(r.TZ_description AS VARCHAR(3000))        AS 'TZ_description', 
                   s.TZ_Name                                      AS 'TZ_Name', 
                   r.tz_submitdate)	AS all
WHERE    all.tz_state <> '16' 
         AND all.tz_state <> '17' 
         AND all.tz_state <> '90' 
         AND all.tz_state <> '72' 
         AND all.tz_projectid <> '23' 
         AND all.tz_issueid <> '' 
         AND all.tz_state <> '185' 
GROUP BY all.tz_id, 
         all.tz_laststatechangedate, 
         all.HIGH_LEVEL,   
		 all.tz_title,
		 all.TZ_description, 
         all.TZ_Name, 
         all.tz_submitdate
ORDER BY all.tz_issueid DESC 

ReadyBuild_TimeStamp', 'AcceptCode_TimeStamp', 'QA_TimeStamp'
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-07-02 : 13:27:56
Yeah, that makes more sense. Is this a view? If not, you might be able to gain some performace by using a temp table (indexed) by putting the results the "main" joins into it then using that temp table to join to the needed table without having to do all those joins over and over...?
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts
Posted - 2009-07-02 : 13:57:06
I was actually thinking that, but the query is used a lot and I'm unsure of how the creation/dropping of temp tables each time would effect performance.

I was thinking a view could work as well, but I wasn't sure how often a view refreshes itself... I've only used views with data that doesn't change often.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site