2 sep. queries are fast except when executed together via UNION ALL

Question

2 sep. queries are fast except when executed together via UNION ALL

p1nball

SSC Rookie

Points: 37
More actions
November 22, 2010 at 12:55 pm

#229454

I have 2 queries that run fast on their own (3 seconds and sub-second) but when I use a UNION ALL between them to return as a single result set, the query never finishes.
Any thoughts/suggestions on this.

Viewing 7 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Look at the different execution plans and see what the difference is.

You might end up having to insert into a temp table and then select from that, but most likely you'll find something in the execution plan that can be fixed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

In addition to GSquared's recommendation, which I'd check first...

Are the result sets large-ish (say 10k rows each or more?) independently? Are you doing more with the UNION ALL than you did with either of the originals (say, sorting the big one with a clause in the little one)? Are these now being pushed to a table where you were merely pushing to your display in the originals?

The reason I ask is you may need to check wait types if you're now pushing these to disk or trying to do something different I/O wise. This could include tempdb utilization, log files, etc.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

p1nball SSC Rookie Points: 37 More actions · Answer 3

GSquared (11/22/2010)
Look at the different execution plans and see what the difference is.

I can't see the execution plan since it never finishes. That said, I will try your suggestion with the Estimated Execution Plan.

You might end up having to insert into a temp table and then select from that, but most likely you'll find something in the execution plan that can be fixed.

This will not work for me since I want to turn this into a view.

Thanks for the reply.

p1nball SSC Rookie Points: 37 More actions · Answer 4

Craig Farrell (11/22/2010)
Are the result sets large-ish (say 10k rows each or more?) independently? Are you doing more with the UNION ALL than you did with either of the originals (say, sorting the big one with a clause in the little one)? Are these now being pushed to a table where you were merely pushing to your display in the originals?

Unfortunately the answer is no to all 3 questions.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Does the union version consistently run indefinitely? Or have you only tried once or twice?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

p1nball (11/22/2010)
Unfortunately the answer is no to all 3 questions.

Hrm. Okay, couple of things to try.

First, set the UnionAll to MAXDOP 1. Get rid of possible parallelism issues, just to be sure. It might take a little longer but it can't be worse than never finishing. 😀

Next, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Testing purposes only, but let's see if for some reason it's contending with itself. If this flies you've got lock contention.

Next, failing these fixes, crank open two new query windows and run each one once. Go to sys.processes and check out cpu/reads/etc. Find out what they do solo.

Then, kick it off for a nice 4 hour tour, and then start prodding sys.processes and find out your wait types on your spid. Let's see why it's spinning forever.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA