3

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

3
  • 1
    The following takes 3 seconds for me. 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? Commented Mar 25, 2016 at 10:49
  • 1
    And guessing the string length is max 400 bytes per row even if nvarchar and making a generous allowance for @sUrlHeader that means an extra 49 MB of data acrosss 121919 rows - are you on an extremely slow connection? Commented Mar 25, 2016 at 11:00
  • @MartinSmith the connection is good, and I don't think the connection is a problem here as I get similar results on local test DB. Commented Mar 25, 2016 at 11:03

1 Answer 1

6

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.