在输出中使用count函数进行视图优化

Question

我直接在SQL Server Management Studio中使用以下视图。它工作得很好，但我找不出为什么它太慢了。

我验证了磁盘IO、CPU和服务器是否有足够的RAM。内部表有适当的索引。

你能帮我以一种优化的方式重写/审核它吗？

SELECT instances.id, 
       instances.unlisted, 
       instances.unread, 
       instances.folderid, 
       instances.partitionfactor, 
       instances.draftid, 
       instances.targetletterid, 
       instances.targetpendingletterid, 
       instances.flag, 
       instances.comments, 
       instances.viewdate, 
       drafts.attachmentcount                AS [DraftAttachmentCount], 
       drafts.bodyfilecount                  AS [DraftBodyFileCount], 
       drafts.[date], 
       drafts.sendertitle, 
       drafts.[browsetoreceiverlist], 
       drafts.[priority], 
       drafts.[priorityid], 
       drafts.subject, 
       drafts.[toreceiverlist], 
       drafts.[ccreceiverlist], 
       drafts.[security], 
       drafts.[securityid], 
       drafts.[body], 
       destinationSender.[fulltitle]         AS [DestinationSenderTitle], 
       incompleteDrafts.frozenid, 
       incompleteDrafts.frozenaction, 
       ( CASE WHEN Isnull(instances.[notecount], 0) = 0 THEN '' ELSE 'Note' END 
         + CASE 
         WHEN Isnull(instances.[alertcount], 0) = 0 THEN '' ELSE 'Alert' END + 
         CASE 
         WHEN Isnull([categorycount], 0) = 0 THEN '' ELSE 'Category' END ) 
                                             [AttachedObjects], 
       drafts.[guid]                         DraftGuid, 
       (SELECT Count(*) 
        FROM   dbo.myperfixdraftdestinationreceivers 
        WHERE  draftid = drafts.id)          [DraftDestinationReceivers], 
       (SELECT Count(*) 
        FROM   dbo.myperfixdraftdestinationreceivers 
        WHERE  draftid = drafts.id 
               AND ( receivertype = 4 
                      OR receivertype = 5 )) [ExternalReceivers] 
FROM   myperfixdraftinstances instances 
       LEFT JOIN myperfixdrafts drafts 
              ON ( drafts.[partitionfactor] = instances.[partitionfactor] 
                   AND drafts.[id] = instances.[draftid] ) 
       LEFT JOIN com_staff destinationSender 
              ON ( destinationSender.[id] = drafts.[destinationsenderid] ) 
       LEFT JOIN myperfixincompletesavedrafts incompleteDrafts 
              ON ( instances.id = incompleteDrafts.draftinstanceid ) 

go 

Answer 1

我的猜测是相关子查询将是罪魁祸首。你试过移除它吗？

还有你需要做外连接的原因吗？您从草稿中返回了很多值，但仅将其外部连接到实例。你有很多没有草稿的实例吗？

如果是这样的话，您可以通过执行内部连接来改进它，然后在您查看所有没有草稿的实例的查询和上面的具有草稿上的内部连接的查询之间执行“联合所有”。

我的第一个尝试是保留外部连接，但删除相关子查询：

with drafts AS
(
SELECT
       d.[id]
       d.attachmentcount, 
       d.bodyfilecount, 
       d.[date], 
       d.sendertitle, 
       d.[browsetoreceiverlist], 
       d.[priority], 
       d.[priorityid], 
       d.subject, 
       d.[toreceiverlist], 
       d.[ccreceiverlist], 
       d.[security], 
       d.[securityid], 
       d.[body],
       r.[DraftDestinationReceivers],
       r.[ExternalReceivers]
from myperfixdrafts d
left join 
    (SELECT draftid,Count(*) [DraftDestinationReceivers],sum(iif(receivertype in (4,5),1,0)) [ExternalReceivers] from dbo.myperfixdraftdestinationreceivers group by draftid) r
on d.[id]=r.draftid
)
SELECT instances.id, 
       instances.unlisted, 
       instances.unread, 
       instances.folderid, 
       instances.partitionfactor, 
       instances.draftid, 
       instances.targetletterid, 
       instances.targetpendingletterid, 
       instances.flag, 
       instances.comments, 
       instances.viewdate, 
       drafts.attachmentcount                AS [DraftAttachmentCount], 
       drafts.bodyfilecount                  AS [DraftBodyFileCount], 
       drafts.[date], 
       drafts.sendertitle, 
       drafts.[browsetoreceiverlist], 
       drafts.[priority], 
       drafts.[priorityid], 
       drafts.subject, 
       drafts.[toreceiverlist], 
       drafts.[ccreceiverlist], 
       drafts.[security], 
       drafts.[securityid], 
       drafts.[body], 
       destinationSender.[fulltitle]         AS [DestinationSenderTitle], 
       incompleteDrafts.frozenid, 
       incompleteDrafts.frozenaction, 
       ( CASE WHEN Isnull(instances.[notecount], 0) = 0 THEN '' ELSE 'Note' END 
         + CASE 
         WHEN Isnull(instances.[alertcount], 0) = 0 THEN '' ELSE 'Alert' END + 
         CASE 
         WHEN Isnull([categorycount], 0) = 0 THEN '' ELSE 'Category' END ) 
                                             [AttachedObjects], 
       drafts.[guid]                         DraftGuid, 
       drafts.[DraftDestinationReceivers], 
       drafts.[ExternalReceivers] 
FROM   myperfixdraftinstances instances 
       LEFT JOIN  drafts 
              ON ( drafts.[partitionfactor] = instances.[partitionfactor] 
                   AND drafts.[id] = instances.[draftid] ) 
       LEFT JOIN com_staff destinationSender 
              ON ( destinationSender.[id] = drafts.[destinationsenderid] ) 
       LEFT JOIN myperfixincompletesavedrafts incompleteDrafts 
              ON ( instances.id = incompleteDrafts.draftinstanceid )