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.
| 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. |
 |
|
|
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) |
|
|
|
antony_dba
Starting Member
26 Posts |
Posted - 2010-11-29 : 02:30:01
|
| how to check the backup data(.bak)extensionis there any query for validating..please suggest it..........kris |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-11-29 : 06:43:32
|
| 1. restore it to a different database/server2. use the "verify files" option of the "backup" command.3. use professional software like www.red-gate.com4. don't post a query like this on the back of somebodyelses unrelated topic. |
|
|
|
|
|
|
Optimize SELECT UNION