I've following query into which I'm filling temporary table from multiple select statements which are using multiple like conditions but I'm unable to optimize the query.
if object_id('tempdb..#tempTable') is not null
drop table #tempTable
select totalProcessed=(select COUNT(*) as totalProcessed
from DetailItem di
inner join DownloadFile df
on di.DownloadFileID = df.DownLoadID
where
(CONVERT(date, di.CompletionTime)) = '2018-01-31' and
di.DetailItemName<>'99999999'
and df.CompanyID = 164
and (df.filename like '%OA73030%' or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%')),
totalErrored=(select COUNT(*) as totalErrored
from DetailItem di
inner join DownloadFile df
on di.DownloadFileID = df.DownLoadID
where
(di.CompletionDetail like '%Not found%'or di.CompletionDetail like '%error%') and
CONVERT(date, di.CompletionTime) ='2018-01-31'
and di.DetailItemName<>'99999999'
and df.CompanyID = 164
and (df.FileName like '%OA73030%' or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%')),
totalSuccess=(select COUNT(*) as totalSuccess
from DetailItem di
inner join DownloadFile df
on di.DownloadFileID = df.DownLoadID
where
(di.CompletionDetail not like '%error%' and di.CompletionDetail not like '%Not Found%') and
CONVERT(date, di.CompletionTime) ='2018-01-31'
and di.DetailItemName<>'99999999'
and df.CompanyID = 164
and (df.FileName like '%OA73030%' or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%')) into #tempTable
select totalProcessed, totalErrored, totalSuccess,cast(round(((totalSuccess*100.0)/totalProcessed),2) as decimal(5,2)) as '%ProcessedSuccessfully',
(totalProcessed-totalSuccess)as 'TotalRe-Processed(2ndBD)',0 as 'TotalSuccessfullyProcessedOutOfRe-order(3rdBD)',0 as 'TotalSLAMissed'
from #tempTable
I assume that search values comes dynamically and no way to predict of what going to be selected. Then, you cannot do a lot with constructions like:
WHERE df.filename like '%OA73030%' or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%'
Since there is no way to rewrite it to:
WHERE df.filename like IN ('%OA73030%','%OA73035%','%OA77030%','%OA77035%')
However, you entire logic of your query can be very much optimized this way:
SELECT COUNT(*) AS totalProcessed
, COUNT(CASE WHEN di.CompletionDetail LIKE '%Not found%' OR di.CompletionDetail LIKE '%error%' THEN 1 END) AS totalErrored
, COUNT(CASE WHEN di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%' THEN 1 END) AS totalErrored
, COUNT(CASE WHEN di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%' THEN 1 END) AS totalSuccess
FROM DetailItem di
INNER JOIN DownloadFile df ON di.DownloadFileID = df.DownLoadID
WHERE di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'
AND di.DetailItemName <> '99999999'
AND df.CompanyID = 164
AND ( df.filename LIKE '%OA73030%'
OR df.filename LIKE '%OA73035%'
OR df.FileName LIKE '%OA77030%'
OR df.filename LIKE '%OA77035%' );
In this case the data engine have to search data only once for all aggregates instead of four times.
Next to that, your approach of trimming time is not SARGable:
( CONVERT(DATE, di.CompletionTime)) = '2018-01-31'
and it has to be rewritten to:
di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'
Otherwise possible index on CompletionTime will not be used
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加