CODEWITHSUNDEEP
sqlsql-servert-sql
user2279126
user2279126

Reputation:

How to improve the performance of a SQL query even after adding indexes?

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.

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.

enter image description here

Upvotes: 1

Views: 143

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

Remove the profiles table as it is not needed and change the WHERE clause to

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:

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:

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.

Do you need every field in the result - if you could loose one or to you maybe could reduce the number of tables further.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

First, if this is not a stored procedure, make it one. That's a lot of text for sql server to complile.

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.

Upvotes: 1

Related Questions