[Solved] How to improve performance of this query [closed]

by


Few ideas below

  1. Remove as many left joins as possible. Use inner joins where-ever possible.

  2. Remove sub-query to get ISerialNumber. Get this later in wrapper query.

  3. Create indexes on columns in the WHERE clause if not already existing.

  4. Do not use this way of date comparison. Imagine this conversion for every row in your result set.

    CONVERT(VARCHAR(8), a.CreationDate, 112) BETWEEN (' + (@StartDate) +
') AND (' + (@EndDate) + ')

Instead, convert your @startDate and @EndDate variables to datetime and compare with a.CreationDate

  1. Calculate these before running this query into another table variables.
    Use those variables in the query.

    (SELECT ParamValue FROM LMG.dbo.MultiValue(''' + @JurisdictionId + ''','','',1))

SELECT ParamValue FROM LMG.dbo.MultiValue(''' + @StateOfConvictionId + ''','','',1)

  2. Check Actual execution query plan in SQL Server Management Studio. The step which is taking more cost (in percentage) will need to be taken care of.

3

solved How to improve performance of this query [closed]