question

Katie 1 avatar image
Katie 1 asked

Alternative to UNION ALL

![alt text][1]![alt text][2]All, Following is the query, and if run by chunks as select it returns data in no time, but when run as of whole it its taking more than 20 secons, even to produce the data on the ssms . How else can we tune this query . SELECT M.corporateCode, M.MeterAsset as 'meterAssetId', M.operatingDate as 'operatingDate', SUM(selected) as 'selected', SUM(goodreading) as 'goodreadings', SUM(badreading) as 'badreadings' FROM ( --good readings select CONVERT (VARCHAR, r.startDate, 101) as 'operatingDate', r.gs_assetIOD as 'MeterAsset', corporateCode = (select gs_customerKey from SCHEMA1.Asset where gs_customerKey is not null and gs_assetIOD=a.gs_assetIOD and (gs_startDate <= r.startDate and (gs_endDate is null OR gs_endDate > r.startDate ))), b.gs_selected as 'selected', 1 as 'goodreading', 0 as 'badreading' from SCHEMA1.MeterReading r join SCHEMA1.IntervalBlock b on r.meterReadingID=b.meterReadingID join SCHEMA3.AssetIndex a on r.gs_assetIOD=a.gs_assetIOD where a.gs_assetKey<> 'DEFAULT_ASSET' UNION ALL -- bad readings with good asset SELECT CONVERT (VARCHAR, r.gs_readingDate, 101) as 'operatingDate', r.gs_assetIOD as 'MeterAsset', corporateCode = (select gs_customerKey from SCHEMA1.Asset where gs_customerKey is not null and gs_assetIOD=a.gs_assetIOD and (gs_startDate <= r.gs_readingDate and (gs_endDate is null OR gs_endDate > r.gs_readingDate ))), 0 as 'selected', 0 as 'goodreading', 1 as 'badreading' from SCHEMA3.ErrorReading r join SCHEMA2.Meter_Data_Header h on h.meterReadingID=r.errorReadingId join SCHEMA3.AssetIndex a on r.gs_assetIOD=a.gs_assetIOD WHERE a.gs_assetKey<>'-999999' UNION ALL --extra bad readings with unknown asset SELECT CONVERT (VARCHAR, r.gs_readingDate, 101) as 'operatingDate', r.gs_assetIOD as 'MeterAsset', h.AssetID as 'corporateCode', 0 as 'selected', 0 as 'goodreading', 1 as 'badreading' from SCHEMA3.ErrorReading r join SCHEMA2.Meter_Data_Header h on h.meterReadingID=r.errorReadingId join SCHEMA3.AssetIndex a on r.gs_assetIOD=a.gs_assetIOD WHERE a.gs_assetKey='-999999' and h.AssetID is not null ) M where M.corporateCode is not null GROUP BY M.corporateCode, M.MeterAsset, M.operatingDate [1]: http://ask.sqlservercentral.com//upfiles/exec_1.jpg [2]: http://ask.sqlservercentral.com//upfiles/exec.png
sql-server-2008sqlqueryunion-all
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

suggest including the execution plan (actual).
0 Likes 0 ·
How can i add? can i attach a file here
0 Likes 0 ·
I believe it lets you attach a image, so if you can fit the plan into a screen shot that should suffice.
0 Likes 0 ·
I dont see a way to attach it :(
0 Likes 0 ·
When you edit your question there should be the default icon for "attach picture".
0 Likes 0 ·
Show more comments
Mark avatar image
Mark answered
It looks like the last two queries are using the same tables, so you could combine them into one using CASE in the select columns.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Your last two queries could be tweaked to hit the tables only once i.e. something like SELECT M.corporateCode, M.MeterAsset as 'meterAssetId', M.operatingDate as 'operatingDate', SUM(selected) as 'selected', SUM(goodreading) as 'goodreadings', SUM(badreading) as 'badreadings' FROM ( --good readings select CONVERT (VARCHAR, r.startDate, 101) as 'operatingDate', r.gs_assetIOD as 'MeterAsset', corporateCode = (select gs_customerKey from SCHEMA1.Asset where gs_customerKey is not null and gs_assetIOD=a.gs_assetIOD and (gs_startDate r.startDate ))), b.gs_selected as 'selected', 1 as 'goodreading', 0 as 'badreading' from SCHEMA1.MeterReading r join SCHEMA1.IntervalBlock b on r.meterReadingID=b.meterReadingID join SCHEMA3.AssetIndex a on r.gs_assetIOD=a.gs_assetIOD where a.gs_assetKey 'DEFAULT_ASSET' UNION ALL -- bad readings with good asset SELECT CONVERT (VARCHAR, r.gs_readingDate, 101) as 'operatingDate', r.gs_assetIOD as 'MeterAsset', corporateCode = CASE WHEN a.gs_assetKey='-999999' THEN h.AssetID ELSE (select gs_customerKey from SCHEMA1.Asset where gs_customerKey is not null and gs_assetIOD=a.gs_assetIOD and (gs_startDate r.gs_readingDate ))) END, 0 as 'selected', 0 as 'goodreading', 1 as 'badreading' from SCHEMA3.ErrorReading r join SCHEMA2.Meter_Data_Header h on h.meterReadingID=r.errorReadingId join SCHEMA3.AssetIndex a on r.gs_assetIOD=a.gs_assetIOD ) M where M.corporateCode is not null GROUP BY M.corporateCode, M.MeterAsset, M.operatingDate Since, you did not provide the sample data and your output, I cannot be certain of this code fulfilling your needs and there still could be more room for improvement. But this indeed will get you going. Moreover, Since you are doing the grouping, your last NULLability check on Corporatecode may not be required.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
An alternative might be to put all the results of those three inner tables into a single temporary table, and then use that for performing the aggregate functions.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.