December 1, 2011 at 1:08 pm
I've got a query that one of our developers is writing which is his model for a view.
The query takes minutes to run. During his investigation as to why, he would execute the first half of the query (before the union) seperatly and it would return results right away. Same thing with the query portion after the union.
But with the union in, running the entire query, we're talking about 5 minutes for a result.
I quickly looked at the Execution Plan and it's not too pretty.
My use of Union's is pretty limited so I'm not exactly sure what to tell him to do other than insert his data into a table var instead of the unions (which ran nice a quick) so he's got a solution, however, I'd like to know a little more about why adding in this union throws the entire thing out of whack.
declare @ReaderId int
declare @FilterAssigned bit
set @readerId = 100;
set @FilterAssigned = 0;
select
[Status] = esc.statusname,
[Parent] = null,
[Criteria] = ' = ''' + rtrim(esc.StatusCode) + '''',
[Count] = sum(isnull(rq.[SubItemCount], 0)),
[PickList] = rtrim(ESC.StatusDesc) + ' - [ ' + Cast(sum(isnull(rq.[SubItemCount], 0)) as varchar(5)) + ' Cases ] ',
[Institution] = 'CATEGORY',
[Modality] = '',
[TagText] = ''
from
EXAM_STATUS_CODES esc
left outer join view_ReadingQueue as rq
on
(
rq.Parent = esc.statusname
)
where
esc.UseInMainList = 1
and
(-- Get Non-Peer Categories without regard to Assignments
(esc.statusname != 'PEER' And @FilterAssigned = 0)
OR -- Get Non-Peer Categories Assigned to this Doc
(esc.statusname != 'PEER' And @FilterAssigned = 1 And rq.AssignedReaderID = @ReaderID)
OR -- Get PEER total count Not Read by this Doc
(esc.statusname = 'PEER' And rq.AssignedReaderID != @ReaderID)
)
group by esc.statusname, esc.StatusCode, ESC.StatusDesc
union
select
[Status] = 'SubItem',
[Parent] = rq.[Parent],
[Criteria] = ' = ''' + rtrim(rq.StatusCode) + '''',
[Count] = rq.subItemCount,
[PickList] = rtrim(rq.Institution) + ' - ' + rq.Modality + ' [ ' + Cast(rq.SubItemCount as varchar(5)) + ' Cases ] ',
[Institution] = rq.Institution,
[Modality] = rq.modality,
[TagText] = ''
from
view_ReadingQueue as rq
where
(-- Get Non-Peer Subitems without regard to Assignments
(rq.[Parent] != 'PEER' And @FilterAssigned = 0)
OR -- Get Non-Peer SubItems Assigned to this Doc
(rq.[Parent] != 'PEER' And @FilterAssigned = 1 And rq.AssignedReaderID = @ReaderID)
OR -- Get PEER subitems Not Read by this Doc
(rq.[Parent] = 'PEER' And rq.AssignedReaderID != @ReaderID)
)
order by [Parent]
December 1, 2011 at 1:14 pm
A UNION tells SQL Server to exclude any duplicate rows, which in turn requires grouping internally.
If you are 100% sure no duplicates will occur, then use UNION ALL.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 1, 2011 at 1:22 pm
Jason Selburg (12/1/2011)
A UNION tells SQL Server to exclude any duplicate rows, which in turn requires grouping internally.If you are 100% sure no duplicates will occur, then use UNION ALL.
+1 on switching to Union All. If it helps think of plain UNION like adding a SELECT DISTINCT wrapper on the result set.
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
December 1, 2011 at 1:43 pm
Went down that road as well, and it had the same results with Union All
December 1, 2011 at 1:49 pm
Same results as what? The union? The queries without the union?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 1:58 pm
Sorry, both Union and Union All take 5 plus minutes to run.
December 1, 2011 at 2:06 pm
brian 20008 (12/1/2011)
Sorry, both Union and Union All take 5 plus minutes to run.
Can we see the .sqlplans for the two queries un-unioned and the unioned version?
Something's just not right there, unless there's other operations in the script after the union.
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
December 1, 2011 at 2:06 pm
Execution plans please. Queries too if possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 2:24 pm
I'll have to post the text execution plan later as it'll kill our production server for 5 minutes or so while the query executes.
December 1, 2011 at 2:26 pm
As for the queries, those are in my original post
December 1, 2011 at 2:28 pm
where's the link to Gails articles about catch all queries?
( -- Get Non-Peer Categories without regard to Assignments
(esc.statusname != 'PEER' And @FilterAssigned = 0)
OR -- Get Non-Peer Categories Assigned to this Doc
(esc.statusname != 'PEER' And @FilterAssigned = 1 And rq.AssignedReaderID = @ReaderID)
OR -- Get PEER total count Not Read by this Doc
(esc.statusname = 'PEER' And rq.AssignedReaderID != @ReaderID)
)
Lowell
December 1, 2011 at 2:36 pm
brian 20008 (12/1/2011)
I'll have to post the text execution plan later as it'll kill our production server for 5 minutes or so while the query executes.
Not text. The XML plans please (actual, not estimated)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 2:39 pm
I'll post them up after hours. Our doctors would kill me if I hang the production server that long during business hours.
My development server doesn't have as large a dataset
December 1, 2011 at 2:45 pm
Lowell (12/1/2011)
where's the link to Gails articles about catch all queries?
( -- Get Non-Peer Categories without regard to Assignments
(esc.statusname != 'PEER' And @FilterAssigned = 0)
OR -- Get Non-Peer Categories Assigned to this Doc
(esc.statusname != 'PEER' And @FilterAssigned = 1 And rq.AssignedReaderID = @ReaderID)
OR -- Get PEER total count Not Read by this Doc
(esc.statusname = 'PEER' And rq.AssignedReaderID != @ReaderID)
)
It's not exactly a catch-all, though there are subcomponents. The @FilteredAssigned could be dynamic'd, but you'd still be carrying the OR based on statusname. Since the individual queries aren't being discussed as a problem directly I was concentrating more on the direct issue, though it probably wouldn't hurt long-term.
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
December 1, 2011 at 2:56 pm
Agreed, it just seems to be with the overall execution.
The indivudual queries (prior and post the union statement) run quick. It's only when everything is run and the union is performed that everything slows to a crawl.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply