UNION ALL normally seems very efficient, but in this case while the two individual queries take two or three seconds each, the UNION ALL takes over 4 minutes.

Looking at the plans (and if I am reading them right) - the optimizer seems to be taking the same approach for the individual queries and all the extra time seems to be in the Work Table DT (Derived Table) section of the 1st (left hand) query:

alt text

I was going to upload the plans but I don't see the attach file (paper clip?) button any more :)



Looking at this further - is this all about the time taken to return the first row? The single queries both have a very fast First Row Run Time at the SELECT level, where as the UNION is the very high. Does the UNION have to fetch all the rows before returning anything?

Further UPDATE

The issue is the time to return all rows. The first query, run separately, returns its first row almost immediately, as does the second. However it takes several minutes to return ALL the rows of the first query (it shouldn't, but that's a different problem). The UNION ALL merely reflects this.

If OPTION (optimization_goal='first-row') is added the end of the UNION statement then the first rows are returned almost immediately. So the only difference really is that the optimizer is doing things differently depending on whether the first query is part of a UNION or not - the actual time taken to return all the rows is much the same.

NB The database has the default setting for "optimization_goal" of "all-rows".

asked 22 Sep '15, 10:31

Justin%20Willey's gravatar image

Justin Willey
accept rate: 21%

edited 22 Sep '15, 12:40

Can you send the plan to first.last@sap.com.

(22 Sep '15, 10:38) Chris Keating

I don't see the attach file (paper clip?) button any more :)

Me, neither:(

(22 Sep '15, 11:09) Volker Barth

FWIW the engine seems to be spending a lot of time in te RED DT node actually. [are there subplans accessed inside that node maybe?]

(22 Sep '15, 11:26) Nick Elson S...
Replies hidden

For either Chris or myself it may also help to include the fast plans for the two [sub-/separated-] queries as well

(22 Sep '15, 11:28) Nick Elson S...
Replies hidden

You are quite right Nick - I was looking at the wrong figure. The Derived Table is where all the time is used - I've corrected the question. There are 8 sub-queries.

(22 Sep '15, 11:32) Justin Willey

All three plans emailed to Chris.

(22 Sep '15, 11:33) Justin Willey

Speaking of "first-row": Does your query have an ORDER BY clause? (And if so, may this have an influence on whether the individual queries vs. the UNION ALL can return first rows fast - even if they are not asked to do so given your default optimization_goal setting...?)

Just WAGs, y'know:)

(22 Sep '15, 12:53) Volker Barth
Replies hidden

none of the queries have an ORDER BY - it must be something else influencing the optimizers decision.

(22 Sep '15, 13:14) Justin Willey
showing 5 of 8 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Sep '15, 10:31

question was seen: 2,466 times

last updated: 22 Sep '15, 13:14