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 2005 Forums
 SQL Server Administration (2005)
 Optimize SELECT UNION

Author  Topic 

CanadaDBA

583 Posts

Posted - 2010-11-04 : 09:47:23
There is a stored procedure in one of DBs (and the DB size is about 70 GB) which is as below.

SELECT ...
FROM {Inner Join of 10 tables and views}
GROUP BY ...

UNION

SELECT ...
FROM {Inner joins of 10 tables and views}
GROUP BY ...

UNION

SELECT ...
FROM {Inner joins of 10 tables and views}
GROUP BY ...

UNION

SELECT ...
FROM {Inner joins of 10 tables and views}
GROUP BY ...


I don't know the DB yet and have recently inherited it. Is there a better way to code the SP in order to optimize and increase its performance?

thanks,

Canada DBA

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-04 : 12:25:27
UNION ALL - include duplicates
<>
UNION - sort step to exclude ducplicates (both with a sub-seelct and across all items in the union)

If unique sub-answers, then go for "union all" at no cost. I would advocate "union all" as the default when coding.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-11-04 : 14:55:42
If the individual SELECTs are doing much the same thing then pre-selecting the relevant data into a temporary table and then doing the UNION / UNION ALLs might be faster (but SQL might be smart enough to see that they are the same and only do them once anyway)
Go to Top of Page

antony_dba
Starting Member

26 Posts

Posted - 2010-11-29 : 02:30:01
how to check the backup data(.bak)extension
is there any query for validating..please suggest it..........

kris
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-29 : 06:43:32
1. restore it to a different database/server
2. use the "verify files" option of the "backup" command.
3. use professional software like www.red-gate.com

4. don't post a query like this on the back of somebodyelses unrelated topic.
Go to Top of Page
   

- Advertisement -