T-SQL 大師，我需要幫助優化這些查詢以提高性能

我想就提高某些查詢的性能提出建議。這些將在 SQL Server 2008 R2 上針對名為 dbo.bids 的單個表執行

在生產環境中，該表預計最多可容納 1,000,000 條記錄。這是我到目前為止所擁有的。

   /* dbo.Bids structure
   bid     decimal (10,2)
   auctionid   bigint
   msisdn      bigint
   lineid      bigint
   timestamp   bigint

CONSTRAINT [PK_Raw] PRIMARY KEY CLUSTERED 
(
   [auctionid] ASC,
   [bid] ASC,
   [msisdn] ASC,
   [lineid] ASC,
   [timestamp] ASC
)

   dbo.bids test data:

   bid     auctionid   msisdn  lineid  timestamp
   1.01    1011        9999    1       0
   1.20    1011        8888    2       1
   0.98    1011        7777    4       2
   1.01    1011        6666    5       3
   0.98    1011        5555    7       4
   0.99    1011        6666    8       6
   0.99    1011        4444    9       6
   0.99    1011        6666    10      6
   0.99    1011        3333    11      8
   1.20    1011        1234    13      10
   1.01    1011        8888    14      11
   */


   DECLARE @auctionid bigint;
   DECLARE @msisdn bigint;
   SET @auctionid = 1011;
   SET @msisdn = 8888;

   SELECT TOP 1 [bid], [msisdn] FROM [dbo].[bids] 
   WHERE [auctionid] = @auctionid AND [bid] = 
       (
           SELECT [bid] FROM 
               (
                   SELECT TOP 1 [bid], COUNT([bid]) AS [tally] FROM [dbo].[bids] 
                   WHERE [bid] >= 0.01 AND [auctionid] = @auctionid 
                   GROUP BY [bid] ORDER BY [tally] ASC, [bid] ASC
               ) AS LowestTally
        ) ORDER BY [lineid] ASC;

   /* RESULTS SHOULD BE
   bid msisdn
   0.98    7777
   */ 


   SELECT msisdn, MinBid, Ranking FROM 
   (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
   (select [bid] from [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = (SELECT [tally] FROM (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally) )
    GROUP BY msisdn ) AS Temp  ORDER BY MinBid;

    /* RESULTS SHOULD BE
   msisdn  MinBid  Ranking
   7777    0.98    1
   5555    0.98    2
   8888    1.20    3
   1234    1.20    4 
    */


   SELECT msisdn, MinBid, Ranking FROM 
   (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
       (SELECT [bid] FROM [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = 
           (SELECT [tally] FROM 
               (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally))
    GROUP BY msisdn ) AS Temp  WHERE msisdn = @msisdn ORDER BY MinBid;

    /* RESULTS SHOULD BE
   msisdn  MinBid  Ranking
   8888    1.20    3
    */

對於第一個查詢，此版本消除了 CI 查找和 TOP N 排序，並將讀取次數減少了dbo.Bids一半。它總體上執行更多的讀取，但這些是由工作表引起的，而不是實際的表。我相信這會給出您想要的結果，但如果沒有更多範例數據，我無法確定它是否符合您的要求。

SELECT TOP (1) bid, msisdn
 FROM dbo.Bids
 WHERE bid >= 0.01 AND auctionid = @auctionid
 ORDER BY COUNT(*) OVER (PARTITION BY bid), bid, lineid;

對於您的第二個查詢，這消除了 CI 搜尋、排序、嵌套循環連接和一些其他運算符：

;WITH x AS 
(
 SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
 FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS 
(
 SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
 FROM x ORDER BY tally
)
SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid) 
FROM y ORDER BY ranking;

對於第三個查詢，這消除了兩種排序，即 CI 搜尋，並將表讀取量減半。

;WITH x AS 
(
 SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
 FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS 
(
 SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
 FROM x ORDER BY tally
),
z AS 
(
 SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
 FROM y
)
SELECT TOP (1) msisdn, MinBid, ranking FROM z WHERE msisdn = @msisdn ORDER BY ranking;

對於所有這些，您必須測試這是否會導致環境中的整體性能更好或更差。我只使用您有限的樣本數據進行了測試（準確性和計劃形狀/持續時間）。根據數據分佈、基數估計、任何其他索引等，事情可能會出現偏差。

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