I have a Stored Procedure which is calculating some data and inserting it into a Temp Table.
Then I am selecting the Data from the Temp Table.
Select
AssetId = iAssetId,
.....
SpeedKM = fSpeed,
[Address] = sState +', '+ sDistrict +', ' +sPoi +', ' + sRoad +', '+sPoi,
MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
+CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
+'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
+'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
+'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
+'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
[Locate] = 'Locate'
FROM #overspeedLogs ol
ORDER BY ol.iAssetId, ol.dtUtcDateTime
Now this Query takes around 1:45 Minutes to execute.
But when I remove the column
MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
+CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
+'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
+'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
+'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
+'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
The stored procedure only takes 35 seconds
,
There are 4 Converts and 1 Date Format function, which maybe causing this issue - but without changing the logic on the Front End (which is an RDLC report) is there a way to increase the performance of this?
Changing Format to Convert still has similar result - still takes double or more the time. The variable @sUrlHeader is VARCHAR(500).
Execution plans
Without MapUrl Column - 121919 Rows in 2 minutes
https://gist.github.com/mdawood1991/580fadf9031824aa22df
With MapUrl Column - 121919 Rows in 7 Minutes
(So more than double for more rows) https://gist.github.com/mdawood1991/33817f95aad40d42631b
SELECT MAX(X) FROM (SELECT TOP 121919 FORMAT( DATEADD(DAY,CRYPT_GEN_RANDOM(2)+0,GETDATE()), 'dd-MMM-yyyy HH:mm:ss') FROM master..spt_values V1, master..spt_values V2)T(X)
. Can you provide a full repro that we can run?@sUrlHeader
that means an extra 49 MB of data acrosss 121919 rows - are you on an extremely slow connection?