Huge issue with an SQL Server Query!
SQL服务器查询的大问题!
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 :(
因此,下面的查询使用查询中的连接命令,但运行时间不少于15-20分钟!不知道如何更好地优化这段代码:
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!
如有任何建议,我们将不胜感激!
1 个解决方案
#1
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.
1)。首先,排序规则语句可能会终止索引的使用。
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)
.
2)。您需要确保在DB_Job_status_open_and_closed(WS_Job_Number_Only)和db_ity_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).
3)。您可能还会发现DB_Timetrack上的索引(BEGDATE、HOURS、DESCR、NumberOnly)很有用(如果您真的在使用SQL Server,那么最后三列可以包含)。
#1
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.
1)。首先,排序规则语句可能会终止索引的使用。
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)
.
2)。您需要确保在DB_Job_status_open_and_closed(WS_Job_Number_Only)和db_ity_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).
3)。您可能还会发现DB_Timetrack上的索引(BEGDATE、HOURS、DESCR、NumberOnly)很有用(如果您真的在使用SQL Server,那么最后三列可以包含)。