僅在批量請求時查看慢速

我們有一個視圖，其中包含這樣的子查詢：

SELECT
   id AS OrderNo,
   timestamp AS OrderDate,
   email,
   (SELECT TOP (1) reasonId
       FROM dbo.pr_cancel WITH (NOLOCK)
       WHERE (orderId = dbo.pr_orders.id)
       ORDER BY id DESC) AS OrderReason
FROM dbo.pr_orders WITH (NOLOCK)
WHERE (pay_canceled IS NULL) AND (pay_error IS NULL)

如果我使用“從視圖中選擇 *”，我會在幾秒鐘內得到 700k 結果，如果我以 25'000 的塊請求結果，則第二、第三、第四塊需要的時間越來越長：

declare @NumRows int
declare @i int

SET @NumRows = 25000 --return 10 rows at a time
SET @i = 3 --i want the 3rd resultset, 30-40
SELECT * FROM (
SELECT 
 row_number() OVER(ORDER BY BestellNr) AS RW,
 dbo.dp_bestellungen.*
FROM dbo.dp_bestellungen ) myAlias
WHERE myAlias.RW BETWEEN (@NumRows * @i) AND ((@NumRows * @i)+ @NumRows)

將“i”設置為 5 或 10 會使查詢非常慢（最多 20 分鐘）。

刪除子查詢會縮短回答時間。

有沒有辦法優化第一個查詢？我無法更改第二個查詢，因為這是由我無權訪問的外部系統執行的。

PS：“dp_bestellungen”指的是第二個查詢中的視圖名稱。

查詢緩慢的主要原因是您使用的是row_number().

您的子選擇需要為表中的所有行創建一個行號。

SELECT 
 row_number() OVER(ORDER BY BestellNr) AS RW,
 dbo.dp_bestellungen.*
FROM dbo.dp_bestellungen

事實上，如果您使用的是 SQL Server 2012 或更高版本，那麼對於這個案例，您有一個更好的解決方案。您可以使用OFFSET/FETCH NEXT來實現相同的行為。OFFSET定義應該跳過多少行。定義跳過偏移後FETCH NEXT要返回的行數。

你可以試試下面的程式碼：

declare @NumRows int
declare @i int

SET @NumRows = 25000 --return 10 rows at a time
SET @i = 3 --i want the 3rd resultset, 30-40
SELECT dbo.dp_bestellungen.*
FROM dbo.dp_bestellungen
ORDER BY BestellNr OFFSET (@i*@NumRows) ROWS FETCH NEXT @NumRows ROWS ONLY;

事實上，如果你有一個好的索引BestellNr，這會更快，最好的情況BestellNr是你CLUSTERED INDEX的這個案例。

如果你不能使用OFFSET/FETCH，我會嘗試使用這個來減少ROW_NUMBER使用自定義的負載OFFSET：

declare @NumRows int
declare @i int

SET @NumRows = 25000 --return 10 rows at a time
SET @i = 3 --i want the 3rd resultset, 30-40
SELECT * FROM (
   SELECT TOP ((@i+1) * @NumRows)
       row_number() OVER(ORDER BY BestellNr) AS RW,
       dbo.dp_bestellungen.*
   FROM dbo.dp_bestellungen
) myAlias
WHERE myAlias.RW >=  (@NumRows * @i)

您的第二個查詢中有一個排序： row_number() OVER(ORDER BY BestellNr)導致結果按 BestellNr 排序。

要優化它，您可以將整個結果（您的查詢沒有WHERE myAlias.RW BETWEEN (@NumRows * @i) AND ((@NumRows * @i)+ @NumRows)）保存到臨時表中並在 RW 上創建一個 PK。

但是，如果基礎表不斷變化，這不是一個解決方案，因為臨時表將僅儲存您創建臨時表時包含在視圖中的表的“快照”

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