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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-14 : 08:08:40
|
| kim writes "I am using a stored proc with very complex sql (selects from union selects, joins, EXTENSIVE use of case in select and order by and joins, etc.etc. In the union part of my proc, if I run the first part of the union I get 233 records in 26secs. the second part gives me 9 records in 4 seconds. When I try to run the whole union I have to kill it after 10 minutes. Any ideas on why. With such a small result set, and such short individual times, I just do not understand. I would expect 241 or less records in no more than 40 seconds." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-14 : 08:47:36
|
| w/o giving us specific information, of course it's impossible to help you at all.however, in general, remember that UNION will not return any duplicate rows, so it requires extra processing to ensure this. If this is not a factor in your query, then use UNION ALL, which is quite a bit faster, since it does NOT attempt to consolidate duplicates. In general, I use UNION ALL most of the time.if you want further help, post more specific info. By the way -- in my 5 years here, about 1 in 10 "complex" sql statements I've seen/troubleshooted actually truly needs to be complex; almost all of them are overcomplicated or convoluted and can be rewritten to be much shorter and more efficient.By the way -- DOn't just cut and paste your existing code and ask for help. If you post DDL for the relevant columns from the tables involved in your SQL, some sample data for those tables (enough to allow us to generate a meaningful resultset that covers all possible scenerios) and show us and explain the rational behind the desired results, then I'm confident someone here will be able to help.- Jeff |
 |
|
|
|
|
|
|
|
union performance