如何優化：COUNT(*)>1

在不考慮索引的情況下，有沒有辦法重新編寫此查詢並更快地獲得相同的結果？

select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*)
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
having COUNT(*)>1

當我將它重寫為後續時，它甚至變得更糟。

;WITH R1 AS (

select ROW_NUMBER() OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo ORDER BY fo.OrderNo, fo.OrderItemSeqNo) AS N
      ,fo.OrderNo
      , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N = 2

或這個：

;WITH R1 AS (

select COUNT_BIG(*) OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo) AS N
      ,fo.OrderNo
      , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N > 1

它是數據倉庫中的事實表。索引在下圖中。

在這種情況下，索引視圖將使用比索引少得多的空間。我在類似的索引視圖中有這種情況——你可以看到下面的程式碼：

CREATE view [dbo].[VProcessStatus]
WITH SCHEMABINDING
AS
select processstatus, 
     StatusCount = COUNT_BIG(*) 
     FROM DBO.tbltaxtransaction
     GROUP BY processstatus
GO

GRANT SELECT ON [dbo].[VProcessStatus] TO [mydomain\itdevelopment] AS [dbo]
GO


CREATE UNIQUE CLUSTERED INDEX [IDX_VProcessStatus] ON [dbo].[VProcessStatus]
(
   [processstatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [pscheme_ProcessStatus]([processstatus])
GO

當我查找此視圖和基礎表使用的空間時：

sp_spaceused 'tbltaxtransaction'
go
sp_spaceused 'VProcessStatus'
go

我得到以下結果：

唯一額外的是這個索引視圖是根據下面的 PARTITION 模式和 PARTITION 函式進行分區的：

CREATE PARTITION SCHEME [pscheme_ProcessStatus] AS PARTITION [pfunc_ProcessStatus] TO ([FG01], [FG02], [FG03], [FG04], [FG05])
GO

CREATE PARTITION FUNCTION [pfunc_ProcessStatus](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 9)
GO

我現在已經從開發轉移到生產。FactBackOrder 表有 4,183,289 行。

我們在 sql server 2012 中。Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 9600) :)

但我不想使用列儲存索引——這些表經常更新。也許在 sql 2014 中？

我想提高性能的查詢的原始查詢計劃是：

我發現改進原始查詢的唯一方法是向表中添加索引。

create index idx_FactBackOrder_OrderNo_OrderItemSeqNo 
on Facts.FactBackOrder (OrderNo,OrderItemSeqNo) with (online=on)

然後當我執行查詢時，我注意到它比沒有索引的原始查詢花費的時間更長。新建索引後的新查詢計劃如下：

我立即刪除了新創建的索引：

drop index idx_FactBackOrder_OrderNo_OrderItemSeqNo on Facts.FactBackOrder

然後我創建一個索引視圖：

--drop view [dbo].[VfactBackOder]

CREATE view [dbo].[VfactBackOder]
WITH SCHEMABINDING
AS
select fo.OrderNo, 
      fo.OrderItemSeqNo, 
      COUNT_BIG(*) as the_number
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
GO

為索引視圖創建聚集索引：

CREATE UNIQUE CLUSTERED INDEX [IDX_VfactBackOder] ON [dbo].[VfactBackOder]
(
   OrderNo, OrderItemSeqNo
)
go

當我使用新創建的索引視圖而不是引用基礎表的原始查詢執行查詢時：

select fo.OrderNo, 
      fo.OrderItemSeqNo,
      fo.the_number
from VfactBackOder fo
where fo.the_number > 1

我得到了一個不滿意的結果。然後我在視圖中添加另一個索引：

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[VfactBackOder] ([the_number])
INCLUDE ([OrderNo],[OrderItemSeqNo])
go

現在查詢像火箭一樣飛了起來。新的查詢計劃是：

並查看索引視圖和基礎表使用的空間：

sp_spaceused ‘Facts.FactBackOrder’ 去 sp_spaceused ‘VfactBackOder’ 去

查詢表現良好，客戶在這裡很開心。感謝所有的評論。

引用自：https://dba.stackexchange.com/questions/89359