I am trying to execute the following sql query but it takes 22 seconds to execute. the number of returned items is 554192. I need to make this faster and have already put indexes in all the tables involved.
我正在尝试执行以下sql查询,但执行需要22秒。返回项目的数量是554192.我需要更快地完成此操作,并且已经将索引放在所有涉及的表中。
SELECT mc.name AS MediaName,
lcc.name AS Country,
i.overridedate AS Date,
oi.rating,
bl1.firstname + ' ' + bl1.surname AS Byline,
b.id BatchNo,
i.numinbatch ItemNumberInBatch,
bah.changedatutc AS BatchDate,
pri.code AS IssueNo,
pri.name AS Issue,
lm.neptunemessageid AS MessageNo,
lmt.name AS MessageType,
bl2.firstname + ' ' + bl2.surname AS SourceFullName,
lst.name AS SourceTypeDesc
FROM profiles P
INNER JOIN profileresults PR
ON P.id = PR.profileid
INNER JOIN items i
ON PR.itemid = I.id
INNER JOIN batches b
ON b.id = i.batchid
INNER JOIN itemorganisations oi
ON i.id = oi.itemid
INNER JOIN lookup_mediachannels mc
ON i.mediachannelid = mc.id
LEFT OUTER JOIN lookup_cities lc
ON lc.id = mc.cityid
LEFT OUTER JOIN lookup_countries lcc
ON lcc.id = mc.countryid
LEFT OUTER JOIN itembylines ib
ON ib.itemid = i.id
LEFT OUTER JOIN bylines bl1
ON bl1.id = ib.bylineid
LEFT OUTER JOIN batchactionhistory bah
ON b.id = bah.batchid
INNER JOIN itemorganisationissues ioi
ON ioi.itemorganisationid = oi.id
INNER JOIN projectissues pri
ON pri.id = ioi.issueid
LEFT OUTER JOIN itemorganisationmessages iom
ON iom.itemorganisationid = oi.id
LEFT OUTER JOIN lookup_messages lm
ON iom.messageid = lm.id
LEFT OUTER JOIN lookup_messagetypes lmt
ON lmt.id = lm.messagetypeid
LEFT OUTER JOIN itemorganisationsources ios
ON ios.itemorganisationid = oi.id
LEFT OUTER JOIN bylines bl2
ON bl2.id = ios.bylineid
LEFT OUTER JOIN lookup_sourcetypes lst
ON lst.id = ios.sourcetypeid
WHERE p.id = @profileID
AND b.statusid IN ( 6, 7 )
AND bah.batchactionid = 6
AND i.statusid = 2
AND i.isrelevant = 1
when looking at the execution plan I can see an step which is costing 42%. Is there any way I could get this to a lower threshold or any way that I can improve the performance of the whole query.
在查看执行计划时,我可以看到一个耗资42%的步骤。有什么方法可以让我达到更低的阈值或任何方式,我可以提高整个查询的性能。
2 个解决方案
#1
2
Remove the profiles table as it is not needed and change the WHERE clause to
删除不需要的配置文件表,并将WHERE子句更改为
WHERE PR.profileid = @profileID
You have a left outer join on the batchactionhistory table but also have a condition in your WHERE clause which turns it back into an inner join. Change you code to this:
您在batchactionhistory表上有一个左外连接,但在WHERE子句中也有一个条件将其转回内部连接。将您的代码更改为:
LEFT OUTER JOIN batchactionhistory bah
ON b.id = bah.batchid
AND bah.batchactionid = 6
You don't need the batches table as it is used to join other tables which could be joined directly and to show the id in you SELECT which is also available in other tables. Make the following changes:
您不需要批处理表,因为它用于连接可以直接连接的其他表,并在您的SELECT中显示ID,这也可以在其他表中使用。进行以下更改:
i.batchidid AS BatchNo,
LEFT OUTER JOIN batchactionhistory bah
ON i.batchidid = bah.batchid
Are any of the fields that are used in joins or the WHERE clause from tables that contain large amounts of data but are not indexed. If so try adding an index on at time to the largest table.
连接中使用的任何字段或包含大量数据但未编制索引的表的WHERE子句。如果是这样,请尝试在最大的表上添加索引。
Do you need every field in the result - if you could loose one or to you maybe could reduce the number of tables further.
您是否需要结果中的每个字段 - 如果您可以松开一个或者您可能会进一步减少表的数量。
#2
1
First, if this is not a stored procedure, make it one. That's a lot of text for sql server to complile.
首先,如果这不是存储过程,请将其设为一个。这是sql server要编译的很多文本。
Next, my experience is that "worst practices" are occasionally a good idea. Specifically, I have been able to improve performance by splitting large queries into a couple or three small ones and assembling the results.
接下来,我的经验是“最糟糕的做法”偶尔也是一个好主意。具体来说,我已经能够通过将大型查询分成几个或三个小查询并组合结果来提高性能。
If this query is associated with a .net, coldfusion, java, etc application, you might be able to do the split/re-assemble in your application code. If not, a temporary table might come in handy.
如果此查询与.net,coldfusion,java等应用程序相关联,则可以在应用程序代码中进行拆分/重组。如果没有,临时表可能会派上用场。
#1
2
Remove the profiles table as it is not needed and change the WHERE clause to
删除不需要的配置文件表,并将WHERE子句更改为
WHERE PR.profileid = @profileID
You have a left outer join on the batchactionhistory table but also have a condition in your WHERE clause which turns it back into an inner join. Change you code to this:
您在batchactionhistory表上有一个左外连接,但在WHERE子句中也有一个条件将其转回内部连接。将您的代码更改为:
LEFT OUTER JOIN batchactionhistory bah
ON b.id = bah.batchid
AND bah.batchactionid = 6
You don't need the batches table as it is used to join other tables which could be joined directly and to show the id in you SELECT which is also available in other tables. Make the following changes:
您不需要批处理表,因为它用于连接可以直接连接的其他表,并在您的SELECT中显示ID,这也可以在其他表中使用。进行以下更改:
i.batchidid AS BatchNo,
LEFT OUTER JOIN batchactionhistory bah
ON i.batchidid = bah.batchid
Are any of the fields that are used in joins or the WHERE clause from tables that contain large amounts of data but are not indexed. If so try adding an index on at time to the largest table.
连接中使用的任何字段或包含大量数据但未编制索引的表的WHERE子句。如果是这样,请尝试在最大的表上添加索引。
Do you need every field in the result - if you could loose one or to you maybe could reduce the number of tables further.
您是否需要结果中的每个字段 - 如果您可以松开一个或者您可能会进一步减少表的数量。
#2
1
First, if this is not a stored procedure, make it one. That's a lot of text for sql server to complile.
首先,如果这不是存储过程,请将其设为一个。这是sql server要编译的很多文本。
Next, my experience is that "worst practices" are occasionally a good idea. Specifically, I have been able to improve performance by splitting large queries into a couple or three small ones and assembling the results.
接下来,我的经验是“最糟糕的做法”偶尔也是一个好主意。具体来说,我已经能够通过将大型查询分成几个或三个小查询并组合结果来提高性能。
If this query is associated with a .net, coldfusion, java, etc application, you might be able to do the split/re-assemble in your application code. If not, a temporary table might come in handy.
如果此查询与.net,coldfusion,java等应用程序相关联,则可以在应用程序代码中进行拆分/重组。如果没有,临时表可能会派上用场。