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
I believe it lets you attach a image, so if you can fit the plan into a screen shot that should suffice.
When you edit your question there should be the default icon for "attach picture".
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.
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.
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.
