CODEWITHSUNDEEP
sql-server-2005t-sql
aspmvcdeveloper
aspmvcdeveloper

Reputation: 125

Optimize query in TSQL 2005

I have to optimize this query can some help me fine tune it so it will return data faster?

Currently the output is taking somewhere around 26 to 35 seconds. I also created index based on attachment table following is my query and index:

SELECT DISTINCT o.organizationlevel, o.organizationid, o.organizationname, o.organizationcode,
      o.organizationcode + ' - ' + o.organizationname AS 'codeplusname'
 FROM Organization o
 JOIN Correspondence c ON c.organizationid = o.organizationid
 JOIN UserProfile up ON up.userprofileid = c.operatorid
WHERE c.status = '4'
  --AND c.correspondence > 0
  AND o.organizationlevel = 1
  AND (up.site = 'ALL' OR
                  up.site = up.site)
  --AND (@Dept = 'ALL' OR @Dept = up.department)
  AND EXISTS (SELECT 1 FROM Attachment a
               WHERE a.contextid = c.correspondenceid
                 AND a.context = 'correspondence'
                 AND ( a.attachmentname like '%.rtf' or  a.attachmentname like '%.doc'))
ORDER BY o.organizationcode

I can't just change anything in db due to permission issues, any help would be much appreciated.

Upvotes: 3

Views: 106

Answers (2)

Twelfth
Twelfth

Reputation: 7180

I believe your headache is coming from this part in specific...like in a where exists can be your performance bottleneck.

 AND EXISTS (SELECT 1 FROM Attachment a
           WHERE a.contextid = c.correspondenceid
             AND a.context = 'correspondence'
             AND ( a.attachmentname like '%.rtf' or  a.attachmentname like '%.doc'))

This can be written as a join instead.

SELECT DISTINCT o.organizationlevel, o.organizationid, o.organizationname, o.organizationcode,
  o.organizationcode + ' - ' + o.organizationname AS 'codeplusname'
FROM Organization o
JOIN Correspondence c ON c.organizationid = o.organizationid
JOIN UserProfile up ON up.userprofileid = c.operatorid
left join article a on a.contextid = c.correspondenceid
             AND a.context = 'correspondence'
             and right(attachmentname,4) in ('.doc','.rtf')

....

This eliminates both the like and the where exists. put your where clause at the bottom.it's a left join, so a.anycolumn is null means the record does not exist and a.anycolumn is not null means a record was found. Where a.anycolumn is not null will be the equivalent of a true in the where exists logic.

Edit to add: Another thought for you...I'm unsure what you are trying to do here...

AND (up.site = 'ALL' OR up.site = up.site)

so where up.site = 'All' or 1=1? is the or really needed?

and quickly on right...Right(column,integer) gives you the characters from the right of the string (I used a 4, so it'll take the 4 right chars of the column specified). I've found it far faster than a like statement runs.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

This is always going to return true so you can eliminate it (and maybe the join to up)

    AND (up.site = 'ALL' OR up.site = up.site)

If you can live with dirty reads then with (nolock)

And I would try Attachement as a join. Might not help but worth a try. Like is relatively expensive and if it is doing that in a loop where it could it once that would really help.

    Join Attachment a
     on a.contextid = c.correspondenceid
     AND a.context = 'correspondence'
     AND ( a.attachmentname like '%.rtf' or  a.attachmentname like '%.doc'))

I know there are some people on SO that insist that exists is always faster than a join. And yes it is often faster than a join but not always.

Another approach is the create a #temp table using 

    CREATE TABLE #Temp (contextid INT PRIMARY KEY CLUSTERED);
    insert into #temp
    Select  distinct contextid
    from atachment 
     where context = 'correspondence'
     AND ( attachmentname like '%.rtf' or  attachmentname like '%.doc')) 
    order by contextid;
    go
    select ... 
    from correspondence c
    join #Temp 
       on #Temp.contextid = c.correspondenceid 
    go
    drop table #temp

Especially if productID is the primary key or part of the primary key on correspondence creating the PK on #temp will help.

That way you can be sure that like expression is only evaluated once. If the like is the expensive part and in a loop then it could be tanking the query. I use this a lot where I have a fairly expensive core query and I need to those results to pick up reference data from multiple tables. If you do a lot of joins some times the query optimizer goes stupid. But if you give the query optimizer PK to PK then it does not get stupid and is fast. The down side is it takes about 0.5 seconds to create and populate the #temp.

Upvotes: 0

Related Questions