如何在添加索引后提高SQL查询的性能?

时间:2022-07-07 04:17:01

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%的步骤。有什么方法可以让我达到更低的阈值或任何方式,我可以提高整个查询的性能。

如何在添加索引后提高SQL查询的性能?

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等应用程序相关联,则可以在应用程序代码中进行拆分/重组。如果没有,临时表可能会派上用场。