CODEWITHSUNDEEP
sqlsql-server
Prakash Kumar
Prakash Kumar

Reputation: 19

How to reduce execution time of SQL Select Query

TitemName and TshotName is the problem here

SELECT DISTINCT
    tJobs.* ,
    tCustomer.Name AS Customer_name ,
    (SELECT tEmployee.First + ' ' + tEmployee.Last
     FROM tEmployee
     WHERE tEmployee.EmployeeID = tJobs.AccountExecutiveID) AS AccountExecutive,
    (SELECT tEmployee.First + ' ' + tEmployee.Last
     FROM tEmployee
     WHERE tEmployee.EmployeeID = tJobs.AccountManagerID) AS AccountManager,
    dbo.RetrunUserFavourite(tJobs.JobNumber, 33369, 'Employee') AS Favorites,
    (SELECT COUNT(*)
     FROM tShots
     WHERE tShots.JobNumber = tJobs.JobNumber) AS shotcount,
    SUBSTRING((SELECT  ', ' + SKU + ', ' + Source + ', ' + ModelNumber
                            + ', ' + Description
               FROM tItems
               WHERE tItems.CustomerID = tCustomer.CustomerID
               FOR XML PATH('')), 3, 200000) titemName,
    SUBSTRING((SELECT  ', ' + ArtDirection + ', '
                            + REPLACE(CONVERT(VARCHAR(5), AdDate, 110), '-',
                                      '/')
               FROM tShots
               WHERE tShots.JobNumber = tJobs.JobNumber
               FOR XML PATH('')), 3, 200000) TshotName
FROM    
    tJobs
INNER JOIN 
    tCustomer ON tCustomer.CustomerID = tJobs.CustomerID
WHERE
    tCustomer.CustomerID = 68666

Upvotes: 1

Views: 142

Answers (2)

M.Ali
M.Ali

Reputation: 69574

You have awful lot of string manipulation going on is your query, anyway a slightly improved version of your query would look something like...

Select DISTINCT
        tJobs.*
      , tCustomer.Name              AS Customer_name
      , AE.First + ' ' + AE.Last    AS AccountExecutive
      , AM.First + ' ' + AM.Last    AS AccountManager
      , dbo.RetrunUserFavourite(tJobs.JobNumber,33369,'Employee')AS Favorites
      , TS.shotcount
      , SUBSTRING(( SELECT ', ' + SKU + ', ' + Source + ', ' + ModelNumber+ ', ' + Description 
                    FROM tItems 
                    where tItems.CustomerID=tCustomer.CustomerID 
                    FOR XML PATH('')), 3, 200000)titemName
     , SUBSTRING(( SELECT ', ' +  ArtDirection +', '+REPLACE(CONVERT(VARCHAR(5),AdDate,110), '-','/') 
                   FROM tShots 
                   where tShots.JobNumber=tJobs.JobNumber 
                   FOR XML PATH('')), 3, 200000)TshotName
From  tJobs 
inner join tCustomer on tCustomer.CustomerID = tJobs.CustomerID
Left join tEmployee AE ON AE.EmployeeID = tJobs.AccountExecutiveID
Left join tEmployee AM ON AM.EmployeeID = tJobs.AccountManagerID
Left join (
            SELECT JobNumber , Count(*) shotcount
            FROM tShots 
            GROUP BY JobNumber
          ) TS  ON TS.JobNumber = tJobs.JobNumber
WHERE tCustomer.CustomerID = 68666

A Couple of Pointers:

  1. Having sub-queries in your select statement makes it very inefficient, because the sub-query is executed for each row returned by the outer query, a more sensible way of doing it would be to use joins.

  2. You Also have a call to a User-Defined Scalar function dbo.RetrunUserFavourite() in your select , these scalar UDFs are also performance killers, again the same execution logic is applied here, they are also executed for each row returned by the outer query, a more sensible way would be to put the function logic/code inside a CTE and join your query to that CTE.

  3. These comma delimited lists that you are creating on the fly for last two columns will be slow, maybe an Inline-Table-Valued function can give better performance here.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I strongly agree with M Ali's comments. Two points I would make. The first is not with regard to performance. But, instead of substring() use:

  STUFF((SELECT ', ' + SKU + ', ' + Source + ', ' + ModelNumber+ ', ' + Description 
         FROM tItems 
         WHERE tItems.CustomerID = tCustomer.CustomerID 
         FOR XML PATH('')
        ), 1, 1, '') as titemName

That way, you don't need strange, meaningless numbers floating around the code.

Second, you may need indexes. Based on the highlighted performance problems, I would suggest:

tItems(CustomerID)

and:

tshots(JobNumber, ArtDirection, AdDate)

Upvotes: 1

Related Questions