Using Union ALL or Union kills performance on a stored proc

Question

Using Union ALL or Union kills performance on a stored proc

Charlottecb

Hall of Fame

Points: 3932
More actions
June 12, 2014 at 4:55 am

#292341

Hi All,
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Can anyone shed some light on this and is there a way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Many, Many thanks.
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
INNER JOIN [UT_Circuit] C ON A.[CircuitID] = C.[CircuitID]
WHEREISNULL(C.[ClientID], 0) = @ClientID
)
-- Also select the search area itself --
SELECT@SearchArea.STGeometryType() AS [GeometryType],
@SearchArea.STAsText() AS [WKT],
'Search Area' AS [Title],
0 AS [ID],
'' AS [BriefDetails],
'' AS [MapObjectType],
0 AS [ThematicValue]
UNION ALL
-- Select any pole objects that intersect the search area --
SELECT A.[Location].STGeometryType() AS [GeometryType],
A.[Location].STAsText() AS [WKT],
A.[PoleID] AS [ID],
'Span Reference:&nbsp' + ISNULL(A.[PoleNumber], 'Unknown Span Reference') + '&nbsp&nbsp&nbspRegion:&nbsp' + ISNULL(D.[RegionName], 'Unknown Region') + '&nbsp&nbsp&nbspSub-Area:&nbsp' + ISNULL(E.[SubAreaName], 'Unknown Sub-Area') + '&nbsp&nbsp&nbspPrimary:&nbsp' + ISNULL(F.[PrimaryName], 'Unknown Primary') + '&nbsp&nbsp&nbspFeeder:&nbsp' + ISNULL(G.[FeederName], 'Unknown Feeder') + 'cm&nbsp&nbsp&nbspCircuit Name:&nbsp' + ISNULL(B.[CircuitName], 'Unknown Circuit Name') + 'cm&nbsp&nbsp&nbspCircuit #:&nbsp' + ISNULL(B.[CircuitNumber], 'Unknown Circuit Number') + '&nbsp&nbsp&nbsp' + '<a href=' + 'ShowDetailPopup(' + ISNULL(CAST(A.[PoleID] AS VARCHAR(10)), '0') + ');' + '>Full Span Details</a>' AS [BriefDetails],
'Pole' AS [MapObjectType],
CASE ISNULL(Y.[SurveyCount], 0)
WHEN 0 THEN 2
ELSE ISNULL(X.[IsSpanClear], 0)
END AS [ThematicValue]
FROM[cte] X
INNER JOIN [UT_Pole] A ON A.[PoleID] = X.[PoleID]
INNER JOIN [UT_Circuit] B ON A.[CircuitID] = B.[CircuitID]
LEFT OUTER JOIN [EP_Client] C ON B.[ClientID] = C.[ClientID]
LEFT OUTER JOIN [UT_Region] D ON B.[RegionID] = D.[RegionID]
LEFT OUTER JOIN [UT_Sub_Area] E ON B.[SubAreaID] = E.[SubAreaID]
LEFT OUTER JOIN [UT_Primary] F ON B.[PrimaryID] = F.[PrimaryID]
LEFT OUTER JOIN [UT_Feeder] G ON B.[FeederID] = G.[FeederID]
LEFT OUTER JOIN [tblPickListItem] H ON B.[VoltageID] = H.[intID]
LEFT OUTER JOIN (SELECTA.[PoleID],
ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [SurveyCount]
FROM[UT_Surveyed_Pole] A
WHEREA.[SurveyStatusID] > 0
GROUP BY A.[PoleID]) Y ON Y.[PoleID] = A.[PoleID]
WHERE--ISNULL(A.[Location].STIsValid(), 0) = 1
X.[rownum] = 1
AND ISNULL(B.[ClientID], 0) = @ClientID
AND A.[Location].STIntersects(@SearchArea) = 1
UNION ALL
-- Select any tree objects that intersect the search area --
SELECT AA.[Location].STGeometryType() AS [GeometryType],
AA.[Location].STAsText() AS [WKT],
AA.[TreeID] AS [ID],
'Tree Reference:&nbsp' + ISNULL(AA.[ReferenceNumber], 'Unknown Tree Reference') + '&nbsp&nbsp&nbspRegion:&nbsp' + ISNULL(D.[RegionName], 'Unknown Region') + '&nbsp&nbsp&nbspSub-Area:&nbsp' + ISNULL(E.[SubAreaName], 'Unknown Sub-Area') + '&nbsp&nbsp&nbspPrimary:&nbsp' + ISNULL(F.[PrimaryName], 'Unknown Primary') + '&nbsp&nbsp&nbspFeeder:&nbsp' + ISNULL(G.[FeederName], 'Unknown Feeder') + 'cm&nbsp&nbsp&nbspCircuit Name:&nbsp' + ISNULL(B.[CircuitName], 'Unknown Circuit Name') + 'cm&nbsp&nbsp&nbspCircuit #:&nbsp' + ISNULL(B.[CircuitNumber], 'Unknown Circuit Number') + '&nbsp&nbsp&nbsp' + '<a href=' + 'ShowTreeDetailPopup(' + ISNULL(CAST(AA.[TreeID] AS VARCHAR(10)), '0') + ');' + '>Full Tree Details</a>' AS [BriefDetails],
'Tree' AS [MapObjectType],
0 AS [ThematicValue]
FROMUT_Tree AA
INNER JOIN [UT_Pole] A ON AA.[PoleID] = A.[PoleID]
INNER JOIN [UT_Circuit] B ON A.[CircuitID] = B.[CircuitID]
LEFT OUTER JOIN [EP_Client] C ON B.[ClientID] = C.[ClientID]
LEFT OUTER JOIN [UT_Region] D ON B.[RegionID] = D.[RegionID]
LEFT OUTER JOIN [UT_Sub_Area] E ON B.[SubAreaID] = E.[SubAreaID]
LEFT OUTER JOIN [UT_Primary] F ON B.[PrimaryID] = F.[PrimaryID]
LEFT OUTER JOIN [UT_Feeder] G ON B.[FeederID] = G.[FeederID]
LEFT OUTER JOIN [tblPickListItem] H ON AA.[SizeBandID] = H.[intID]
WHERE--ISNULL(AA.[Location].STIsValid(), 0) = 1
ISNULL(B.[ClientID], 0) = @ClientID
AND A.[Location].STIntersects(@SearchArea) = 1 -- Parent Pole intersects the search area --

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Union all is usually the preferred way, if that's causing problems you could try inserting each resultset into a table variable and querying the table variable.

To say anything useful about your query, we really need to see the execution plan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Thanks for the response...

I think I'll just return the 3 datasets and process them in a loop from the front-end...

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

You could execute the first result with "SELECT...INTO #temptable" into a temptable and subsequently adding the results of the 2nd and 3rd query to this temptable. Return the complete temptable to the frontend, so you don't need to loop there.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ScottPletcher SSC Guru Points: 100949 More actions · Answer 4

You also need to get rid of all the ISNULL()s in the WHERE clause. You should follow this simple rule:

Never use ISNULL() in a WHERE clause.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.