asked on

Need to Fine-Tune SQL Query

I have a query that times out when more than three sendingUserID's are submitted.
I am looking for a way to modify the query so that it is more efficient.

SELECT  a.[CorpUser], a.[sendingUserID], 
       Sum([Client Submission]) AS [Client Submission], 
       Sum([Phone Interview])   AS [Phone Interview], 
       Sum([1st Interview])     AS [1st Interview], 
	   Sum([2nd Interview])     AS [2nd Interview], 
       Sum([3rd Interview])     AS [3rd Interview], 
       Sum([4th Interview])     AS [4th Interview], 
       Sum([Offer Extended])    AS [Offer Extended], 
       Sum([Client Submission] + [Phone Interview] + [1st Interview] + [2nd Interview] + [3rd Interview] + [4th Interview] + [Offer Extended]) as Total
FROM   (SELECT co.[name] as CorpUser, j.[sendingUserID], j.[jobOrderID],
               CASE 
                 WHEN j.[status] = 'Client Submission' THEN 1 
                 ELSE 0 
               END AS [Client Submission], 
               CASE 
                 WHEN j.[status] = 'Phone Interview' THEN 1 
                 ELSE 0 
               END AS [Phone Interview], 
               CASE 
                 WHEN j.[status] = '1st Interview' THEN 1 
                 ELSE 0 
               END AS [1st Interview], 
			   CASE 
                 WHEN j.[status] = '2nd Interview' THEN 1 
                 ELSE 0 
               END AS [2nd Interview],
               CASE 
                 WHEN j.[status] = '3rd Interview' THEN 1 
                 ELSE 0 
               END AS [3rd Interview], 
               CASE 
                 WHEN j.[status] = '4th Interview' THEN 1 
                 ELSE 0 
               END AS [4th Interview], 
               CASE 
                 WHEN j.[status] = 'Offer Extended' THEN 1 
                 ELSE 0 
               END AS [Offer Extended] 
        FROM   [JobSubmission] j
			  LEFT OUTER JOIN [dbo].[Placement] p ON j.jobOrderID = p.jobOrderID
			  LEFT OUTER JOIN [dbo].[Candidate] ca ON j.[candidateID] = ca.[userID]
			  LEFT OUTER JOIN [CorporateUser] co ON co.userID = j.sendingUserID
			  LEFT OUTER JOIN [dbo].[JobOrder] jo ON j.jobOrderID = jo.jobOrderID
        WHERE  (j.[sendingUserID] IN ( 12345 )) 
		and jo.isOpen =1
		) AS a 		
GROUP  BY  a.[sendingUserID], a.[CorpUser]
ORDER BY a.[CorpUser]

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

In addition to what Scott mentioned above, I couldn't see any columns selected from tables Placement and Candidate..
If not required, you can remove those tables from join.. Apart from that, we would need to see Execution plan to confirm whether the query is tuned out or not..

			  LEFT OUTER JOIN [dbo].[Placement] p ON j.jobOrderID = p.jobOrderID
			  LEFT OUTER JOIN [dbo].[Candidate] ca ON j.[candidateID] = ca.[userID]

Open in new window

Tony303

Have a look at the Execution Plan and see if there are any Table Scans. If there are, those tables ought to have a non clustered index at the very least added to it.