| UNION and UNION ALL discrepancy [message #337913] |
Fri, 01 August 2008 09:47  |
bijou
Messages: 6
Registered: August 2008
Location: London
|
Junior Member |
|
|
Hello,
I am having trouble with a stored procedure that selects data (using sql statements connected with "UNION ALL") and then outputs the data to a file.
- select with "UNION ALL" takes 6 minutes
- output of 84,669 rows to file takes 36 minutes
If the stored prod is changed to use a "UNION" clause ...
- select with "UNION" takes 21 minutes
- output of 84,422 rows to file takes 2 minutes
Why does the output to file when using "UNION ALL" take so much longer than the output to file when using "UNION" ?
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: UNION and UNION ALL discrepancy [message #337963 is a reply to message #337955] |
Fri, 01 August 2008 14:12  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| joy_division wrote on Fri, 01 August 2008 19:59 | What do you mean that the SELECT takes 6 minutes but the output takes 36 minutes.
|
TOAD I bet. Display first 100 rows is regarded a "select"
|
|
|
|
| Re: UNION and UNION ALL discrepancy [message #338126 is a reply to message #337963] |
Sun, 03 August 2008 21:45 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A UNION performs a SORT, which means all of the data is loaded into TEMP. This takes a lot of thinking time, but once the sort is complete, the data can be output rapidly because it is all sitting in TEMP.
A UNION ALL outputs rows as they are found. If your SQL is scanning non-matching rows and then filtering them out, then there may be a wait between rows as it scans and discards unwanted rows.
This could also be observed with an index scan that discards no rows; the cost of scanning an index and retrieving rows from all over the disk is more than a straight scan of a sorted result set in TEMP.
In short, the only thing that will rival the output speed of a TEMP result set is a Full Table Scan without a WHERE clause.
Ross Leishman
|
|
|
|
| Re: UNION and UNION ALL discrepancy [message #338203 is a reply to message #337963] |
Mon, 04 August 2008 03:39 |
bijou
Messages: 6
Registered: August 2008
Location: London
|
Junior Member |
|
|
Joy_division / Frank,
What I meant by "The select takes 6 mins and the output takes 36 mins" is this:
From the time the select starts executing till the time the first row of output is written = 6 mins. From that instant, it takes another 36 mins for the rest of the output to be written.
|
|
|
|
|
|
|
|
| Re: UNION and UNION ALL discrepancy [message #338967 is a reply to message #338210] |
Wed, 06 August 2008 06:10 |
bijou
Messages: 6
Registered: August 2008
Location: London
|
Junior Member |
|
|
I appreciate all the comments I have had on this issue. It is clear that the i/o cannot be optimised any further if UNION ALL is to be used.
I would like to investigate the possibility of improving the efficiency of the SQL if possible.
I have posted the query plan for the SQL in question (in a readable format .csv file) - I would welcome any recommendations for performance improvement.
|
|
|
|
| Re: UNION and UNION ALL discrepancy [message #339604 is a reply to message #338967] |
Thu, 07 August 2008 21:52 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Run each of the components of the UNION ALL individually. Find out if one of them is less efficient than the others.
Post the SQL of that ONE here. Run a SQL Trace and post the TKPROF output here too. Enclose it in CODE tags to make it display properly - we don't like opening attachments.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
