UNION ALL taking far longer than individual queries

[justin_willey]

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:

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

v16.0.0.2076

---

UPDATE

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".

[chris_keating]

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

[VolkerBarth]

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

Me, neither:(

[Former Member]

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?]

[Former Member]

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

[justin_willey]

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.

[justin_willey]

All three plans emailed to Chris.

[VolkerBarth]

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:)

[justin_willey]

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