Sql-server – Query Time is doubled because of a column in select

performancequery-performancesql serversql-server-2012

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

enter image description here

With MapUrl Column – 121919 Rows in 7 Minutes (So more than double for more rows) https://gist.github.com/mdawood1991/33817f95aad40d42631b
enter image description here

Best Answer

The time difference is probably due to:

  1. The larger amount of data that needs to be returned to the client; and
  2. The FORMAT function is relatively slow

You may be able to avoid using FORMAT by using a T-SQL expression instead, for example:

REPLACE(CONVERT(char(11), dtutcDateTime, 106), SPACE(1), '-') + 
SPACE(1) + 
CONVERT(char(8), @dt, 108);

-- Returns 25-Mar-2016 23:45:19

See:

You should also avoid scalar T-SQL functions in general, for performance reasons. Both plans use a UDF called xPT_ConvertTimeToDDHHMMSS. Scalar T-SQL functions are executed per row, with an overhead similar to that of running a complete query (each time). With 121,861 function calls, that overhead will be adding up. Use an in-line function or T-SQL intrinsics instead.

Also check that the variable @sUrlHeader is not a LOB type (e.g. varchar(max)) if it does not need to be. Using varchar(8000) or below may be significantly faster.

You can check the raw performance of the query (discounting the effect of a client that is slow to accept the results) by running it in SSMS with the Discard Results option set:

Query Options

...or by selecting the query into a temporary table:

SELECT
  OverspeedReportId = ol.iVehicleMonitoringId,
  AssetId = iAssetId,
  Registration = sReference,
  CategoryId = iCategoryId,
  CategoryName = sCategoryName,
  SiteId = iSiteId,
  SiteName = sSiteName,
  OverspeedDate = FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss'),
  DistanceTraveledSinceLastOverSpeed = DistanceCoveredKM,
  TimeDifferenceDDDHHMMSS = SUBSTRING(dbo.xPT_ConvertTimeToDDHHMMSS(DiffSeconds,'s'),1,12),
  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'
INTO #Results -- NEW!
FROM #overspeedLogs ol
LEFT JOIN VehicleGISAddressLog gis
    ON gis.iVehicleMonitoringId = ol.iVehicleMonitoringId
ORDER BY
    ol.iAssetId, 
    ol.dtUtcDateTime;

For the most comprehensive collection of performance data, run the queries directly from SQL Sentry Plan Explorer. Click the "Post to SQLPerformance.com" toolbar button to upload the complete session for expert analysis on that site.

If you are unable to do that, consider adding STATISTICS IO output to your question, at least.

Related Question