Union Problem

Question

Union Problem

Brian Joyce

SSC Rookie

Points: 43
More actions
June 8, 2006 at 5:06 am

#117933

Hi,
I've written a relatively in depth query that contains one union (and two select statements). When i run the select statements individually they take about 0.5 seconds each to return information. However when I run the statement with the union it takes about 3+ minutes to return the information. If anyone could enlighten me as to why this is happening it would be a big help. Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

UNION removes all exact duplicates from the concatenated recordset. This involves a sort. If you won't have or don't mind dupes, use UNION ALL which is a straight concetenation.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Brian Joyce SSC Rookie Points: 43 More actions · Answer 2

Thanks for the info and quick reply, however using the "Union all" command didn't speed up the return of the results set ... the problem continues.

bdohmen SSCrazy Points: 2996 More actions · Answer 3

Did you look at the execution plans for both the queries individually and the full query using the UNION statement? Can you post your table structure along with indexes and the queries you are trying to run?

Brian

stax68 SSChampion Points: 11711 More actions · Answer 4

any paralellism in the plans?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Brian Joyce SSC Rookie Points: 43 More actions · Answer 5

Really appreciate all this help guys. Unfortunately i can't really post the table structure as the foreign keys are handled by the front end (very odd i know ... and it only gets odderererer!!) and it'll take me ages to complie a structure

Through the process of elimination i've found that the problem lies with a particular column. If i exclude "left(convert(varchar,min(e.start_time), 108), 5)" (The e.start_time is just a datestamp) and replace it with a blank column e.g. ' ' it works fine. And as i stated earlier if i run the select statement on it's own (even with the problem column in) it runs in about 0.5 seconds.

Thanks for all your efforts on this ... if you're as stumped as me then no worries at all

brendt hess Hall of Fame Points: 3256 More actions · Answer 6

The best way to see what the issue is in this case would be to add in the functionality one step at a time. If you replace the blank column with min(e.start_time), does the process work well? If so, how about with the statement convert(varchar,min(e.start_time), 108)?

When things like this happen in procs that process large amounts of data, I have found three primary causes:

All available memory is consumed by the process, requiring swapping to disk to complete the process
A subtle query optimization bug (which usually leads to a different query plan), or
Blue socks

For the first issue, see if you can increase the available memory, or monitor the disk utilization both without the slow version running, and with the slow version running. If disk utilization spikes heavily during the slow version, you can test further, or decide how to reduce the amount of data in memory concurrently.

The second issue is entirely an MS issue - you can provide examples, but only MS can fix it. As an alternative, you can try different methods of extracting the information you are looking for (e.g. would datepart() get what you need?)

For the third issue - this is my term for problems that are intermittent, irreproducible, and unsolvable. These things only happen on alternate Thursdays when the user is wearing blue socks. This issue is unlikely to be a BS issue.

brendt hess Hall of Fame Points: 3256 More actions · Answer 7

One more thing to try - replace the LEFT statement by defining the varchar length, e.g.

convert(varchar(5), min(e.start_time), 108)

and see if that helps things.

stax68 SSChampion Points: 11711 More actions · Answer 8

where does the aggregation occur? Can you give us an outline of the query?

eg

select col1, min(x) from

(select col1, id from t3 where x

union

select.col1, max(id)-min(id) from t4 group by col1 having count(*) > 1

) v

group by col1

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Pam Brisjar SSChampion Points: 12094 More actions · Answer 9

"For the third issue - this is my term for problems that are intermittent, irreproducible, and unsolvable. These things only happen on alternate Thursdays when the user is wearing blue socks. This issue is unlikely to be a BS issue."

Mind if I borrow this?

brendt hess Hall of Fame Points: 3256 More actions · Answer 10

Feel free. It's in use at several businesses I used to work at. Spread the word...

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

I must have worked at those places as well!!!

I wasn't born stupid - I had to study.