Left Outer Join Query Extremely Slow - SQL Server

Question

Huge issue with an SQL Server Query!

So the query below utilizes JOIN commands in the query, but takes no less than 15-20 minutes to run! No idea how to better optimize this code :(

Any ideas on how to fix this issue?

SELECT dbo.DB_Timetrack.[JOB #], dbo.DB_Timetrack.[Last Name], dbo.DB_Timetrack.[First Name], dbo.DB_Timetrack.HOURS, dbo.DB_Timetrack.BEGDATE, dbo.DB_Timetrack.LOCATNID, 
                         dbo.DB_Timetrack.DESCR, dbo.DB_Timetrack.CODE, dbo.DB_Timetrack.TYPE, 
                         CASE WHEN dbo.DB_Job_status_open_and_closed.WS_Job_Number != '' THEN dbo.DB_Timetrack.Hours ELSE 0 END AS HoursWon, dbo.DB_Timetrack.NUMBERONLY, 
                         dbo.DB_Job_status_open_and_closed.WS_Job_Number_Only, dbo.DB_Job_status_open_and_closed.CREATDDT, DATEDIFF(Day, dbo.DB_Timetrack.BEGDATE, 
                         dbo.DB_Job_status_open_and_closed.CREATDDT) AS Date_Difference, DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID.new_GPSalespersonName, 
                         CASE WHEN DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID.new_GPSalesRepCode <> '' THEN DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID.new_GPSalesRepCode ELSE dbo.DB_Job_status_open_and_closed.Salesperson
                          COLLATE Latin1_General_CI_AI END AS SalespersonID

FROM            dbo.DB_Timetrack LEFT OUTER JOIN
                         DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID ON dbo.DB_Timetrack.NUMBERONLY COLLATE Latin1_General_CI_AI = DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID.Job_Num_Only LEFT OUTER JOIN
                         dbo.DB_Job_status_open_and_closed ON dbo.DB_Timetrack.NUMBERONLY = dbo.DB_Job_status_open_and_closed.WS_Job_Number_Only

WHERE        (dbo.DB_Timetrack.HOURS > 0) AND (dbo.DB_Timetrack.DESCR <> '') AND (dbo.DB_Timetrack.BEGDATE > CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

Any suggestions would be greatly appreciated!

To ask the obvious, do you have indices setup on the join columns? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 29, 2016 at 15:20
Your code looks much more like SQL Server than MySQL. Are you sure you have to question tagged correctly? — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2016 at 15:21
400K rows. Huge amounts of data. And Indeed I do. Tried creating multiple variances of indexes, but to no avail — F.Chari
– F.Chari, Commented Aug 29, 2016 at 15:23
@GordonLinoff Thanks for the catch. I have corrected the tagging issue — F.Chari
– F.Chari, Commented Aug 29, 2016 at 15:26

TheGameiswar · Accepted Answer · 2016-08-29 15:46:30Z

2

This is basically your query:

SELECT . . .
FROM dbo.DB_Timetrack LEFT OUTER JOIN
     DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID
     ON dbo.DB_Timetrack.NUMBERONLY COLLATE Latin1_General_CI_AI = DBCRM_MSCRM.dbo.DB_Opportunity_SalespersonID.Job_Num_Only LEFT OUTER JOIN
     dbo.DB_Job_status_open_and_closed
     ON dbo.DB_Timetrack.NUMBERONLY = dbo.DB_Job_status_open_and_closed.WS_Job_Number_Only
WHERE (dbo.DB_Timetrack.HOURS > 0) AND
      (dbo.DB_Timetrack.DESCR <> '') AND
      (dbo.DB_Timetrack.BEGDATE > CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

1.) First, the collation statement is possibly killing any use of indexes.

2.) You need to be sure that you have indexes on DB_Job_status_open_and_closed(WS_Job_Number_Only) and DB_Opportunity_SalespersonID(Job_Num_Only).

3.) You might also find an index on DB_Timetrack(BEGDATE, HOURS, DESCR, NumberOnly) to be helpful (the last three columns can be just include if you are really using SQL Server).

edited Aug 29, 2016 at 15:46

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

answered Aug 29, 2016 at 15:26

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

F.Chari Over a year ago

Very detailed. Will give this a go, and see if performance can be improved, and will return here with my results! Thanks again

Collectives™ on Stack Overflow

Left Outer Join Query Extremely Slow - SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related