|
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
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". |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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: 8,795 times
last updated: 22 Sep '15, 13:14
Can you send the plan to first.last@sap.com.
Me, neither:(
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?]
For either Chris or myself it may also help to include the fast plans for the two [sub-/separated-] queries as well
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.
All three plans emailed to Chris.
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:)
none of the queries have an ORDER BY - it must be something else influencing the optimizers decision.