sql-server-2005 - 在SQL 2005中优化查询

Question

我必须优化此查询，这可以帮助我进行微调，以便更快地返回数据吗？

目前，输出大约需要26到35秒。我还基于附件表创建了索引，以下是我的查询和索引:

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

由于权限问题，我不能只更改db中的任何内容，我们将不胜感激。

Answer 1

我相信您的头痛来自特定部分……就像存在可能成为您的性能瓶颈一样。

 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'))

可以将其写为联接。

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')

....

这样就消除了类似内容和存在的地方。将您的where子句放在底部。它是一个左联接，因此a.anycolumn为null表示该记录不存在，而a.anycolumn不为null表示已找到一条记录。 a.anycolumn不为null的位置将等于where存在逻辑中的true。

编辑添加:
您的另一个想法...我不确定您要在这里做什么...

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

所以哪里up.site ='All'或1 = 1？还是真的需要？

并迅速在right ... Right(column，integer)处给您字符串右边的字符(我使用了4，所以它将采用指定列的4个右字符)。我发现它比执行like语句快得多。