Sql-Server

優化查詢以使用多個連接和過濾器隨機檢索記錄

  • May 7, 2020

我有以下架構：

數據庫時間表

這個問題也發佈在 StackOverflow 上，但我也想諮詢更專注於數據庫管理的專家，因為我的項目的性質。抱歉，如果這是一個錯誤

目前，該表Property擁有超過 70K 條記錄。我正在開發一個更新以支持超過 500 個並發會話。該應用程序將支持地圖 a 進行搜尋，這就是GeoLocation聲明Coordinate為geography數據類型的原因。現在我們遇到了一個大問題，因為某些查詢（最重要的查詢）的響應時間非常慢。我的意思是，如果指定參數有這麼多的結果，應用程序必須一次返回大約 1000 條記錄。

參數分佈在模式的所有表上（實際上，它是模式的一部分）。作為Features一張包含所有主要“特徵”的表格（臥室數量、車庫數量等）。

考慮到這一點，現在需要花費大量時間的查詢如下：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

DECLARE @properties TABLE(
   [ID] INT
)

INSERT INTO @properties
   SELECT p.[Id]
   FROM[Property] p
   INNER JOIN[GeoLocation] AS[g] 
       ON[p].[Id] = [g].[PropertyId]
   INNER JOIN[PropertyFeature] AS[pf] 
       ON[pf].[PropertyId] = [p].[Id]
   INNER JOIN[Feature] AS[f] 
       ON[pf].[FeatureId] = [f].[Id]
   WHERE[g].[Address] IS NOT NULL AND(([g].[Address] <> N'') OR[g].[Address] IS NULL)
       AND[pf].[FeatureId] IN(
           Select ID from feature where featuretype = 1)
   GROUP BY p.Id, p.ModificationDate
   ORDER BY [p].ModificationDate DESC, newid()
   OFFSET 0 ROWS
   FETCH NEXT 1000 ROWS ONLY

DECLARE @features TABLE(
   [Name] NVARCHAR(80)
)

INSERT INTO @features
   select Name from feature where FeatureType = 1

CREATE TABLE #temptable
(
   Id INT,
   Url NVARCHAR(200),
   Title NVARCHAR(300),
   Address NVARCHAR(200),
   Domain Tinyint,
   Price Real,
   Image NVARCHAR(150), 
   Name NVARCHAR(80),
   Value NVARCHAR(150)
)

INSERT INTO #temptable
SELECT
   [t].[Id], 
   [t].[Url], 
   [t].[GeneratedTitle] AS[Title], 
   [t].[Address], 
   [t].[Domain], 
   [t].[Price],
   (SELECT TOP(1) ISNULL([m].[Resize1200x1200], [m].Resize730x532)
    FROM [Multimedia] AS[m]
    WHERE [t].[Id] = [m].[PropertyId]
       and m.MultimediaType = 1
    ORDER BY [m].[Order]) AS[Image], 
   [t].[Name], 
   [t].[Value]
FROM
   (SELECT
       [p].[Id],
       [p].[Url],
       [p].[GeneratedTitle],
       [g].[Address],
       [p].[Domain],
       [pr].[Amount] AS Price,
       [p].[ModificationDate],
       [f].[Name],
       [pf].[Value]
   FROM [Property] AS [p]
   INNER JOIN [GeoLocation] AS[g] 
       ON [p].[Id] = [g].[PropertyId]
   INNER JOIN [PropertyFeature] AS[pf] 
       ON [pf].[PropertyId] = [p].[Id]
   INNER JOIN [Feature] AS[f] 
       ON [pf].[FeatureId] = [f].[Id]
   INNER JOIN [Operation] AS [o] 
       ON [p].[Id] = [o].[PropertyId] 
   INNER JOIN [OperationType] AS [o0] 
       ON [o].[OperationTypeId] = [o0].[Id] 
   INNER JOIN [Price] AS [pr] 
       ON [pr].[OperationId] = [o].[Id] 
   WHERE p.Id in 
       (Select Id from @properties)
   GROUP BY [p].[Id], 
            [p].[Url],
            [p].[GeneratedTitle], 
            [g].[Address],
            [p].[Domain], 
            [pr].[Amount],
            [p].[ModificationDate],
            [f].[Name],
            [pf].[Value]) AS[t]
   ORDER BY[t].[ModificationDate] DESC

SET @cols = STUFF(
               (
                   SELECT DISTINCT
                           ','+QUOTENAME(c.[Name])
                   FROM @features c FOR XML PATH(''), TYPE
                ).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [Id], 
                    [Url], 
                    [Title], 
                    [Address], 
                    [Domain], 
                    [Price], 
                    [Image], 
                    ' + @cols + '
              FROM (SELECT [Id], 
                           [Url], 
                           [Title], 
                           [Address], 
                           [Domain], 
                           [Price], 
                           [Image], 
                           [Value] AS [value], 
                           [Name] AS[name] 
                    FROM #temptable)x 
                    PIVOT(max(value) for name in ('+@cols+')) p';
EXECUTE(@query);

DROP TABLE #temptable

執行計劃和實時查詢統計顯示如下：

查詢執行計劃

前面的查詢嘗試隨機獲取 X 個記錄 ID，保留所有過濾條件以僅獲取滿足該條件的記錄的 ID。現在的時間最長為 15 秒。如果我們談論超過 400 個使用者同時使用該應用程序，那就很多了。

請在這件事上給予我幫助。我花了三週時間試圖解決這個問題，但沒有成功，但已經取得了很多進展（在平均消耗 2 分鐘之前）。

如果有幫助，我可以讓您訪問具有相同數量記錄的數據庫的“虛擬”部署版本，以測試並直接查看問題。

提前致謝…

==================================================== ==================================================== = 索引：

目前在表上的索引如下：

GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Property_ModificationDate 
ON [dbo].[Property] (ModificationDate DESC) 
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)

GO
CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode] 
ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode_Id_ModificationDate] 
ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC, [Id] ASC, [ModificationDate] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Property_ParentId]
   ON [dbo].[Property]([ParentId] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

   GO
CREATE NONCLUSTERED INDEX [IX_Property_Identity_Domain_StatusCode]
   ON [dbo].[Property]([Identity] ASC, [Domain] ASC, [StatusCode] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Property_Id_ModificationDate] 
ON [dbo].[Property] (Id ASC, ModificationDate ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Property_PublisherId]
   ON [dbo].[Property]([PublisherId] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);


GO
CREATE NONCLUSTERED INDEX [IX_Property_RealEstateTypeId]
   ON [dbo].[Property]([RealEstateTypeId] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)


GO

CREATE INDEX FIX_Property_StatusCode_Online ON [dbo].[Property](StatusCode) WHERE StatusCode = 1
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE INDEX FIX_Property_StatusCode_Offline ON [dbo].[Property](StatusCode) WHERE StatusCode = 0
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE INDEX FIX_Property_Domain_Urbania ON [dbo].[Property](Domain) WHERE Domain = 1
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE INDEX FIX_Property_Domain_Adondevivir ON [dbo].[Property](Domain) WHERE Domain = 2
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

GO
CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_ModificationDate] 
ON [dbo].[GeoLocation] (PropertyId ASC, [ModificationDate] DESC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_Address] 
ON [dbo].[GeoLocation] (PropertyId ASC, [Address] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE UNIQUE NONCLUSTERED INDEX IX_GeoLocation_ModificationDate 
ON [dbo].[GeoLocation] (ModificationDate DESC) 
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GeoLocation_Ubigeo]
ON [dbo].[GeoLocation]([Ubigeo] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId]
   ON [dbo].[GeoLocation]([PropertyId] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE SPATIAL INDEX SIX_GeoLocation_Coordinate ON [dbo].[GeoLocation](Coordinate)
GO

CREATE INDEX FIX_GeoLocation_Domain_Urbania ON [dbo].[GeoLocation](Domain) WHERE Domain = 1
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

CREATE INDEX FIX_GeoLocation_Domain_Adondevivir ON [dbo].[GeoLocation](Domain) WHERE Domain = 2
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

GO
CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId_Order] 
ON [dbo].[Multimedia] (PropertyId ASC, [Order] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId]
   ON [dbo].[Multimedia]([PropertyId] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_Multimedia_Order]
   ON [dbo].[Multimedia]([Order] ASC)
   WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);
GO

CREATE NONCLUSTERED INDEX [PK_Multimedia_Property]
   ON [dbo].[Multimedia]([Id] ASC, [PropertyId] ASC);
GO

CREATE INDEX FIX_Multimedia_MultimediaType_Image ON [dbo].[Multimedia](MultimediaType) WHERE MultimediaType = 1
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)
GO

GO
CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId_FeatureId] 
ON [dbo].[PropertyFeature] (PropertyId ASC, [FeatureId] ASC)
WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);

GO
CREATE NONCLUSTERED INDEX [IX_PropertyFeature_FeatureId]
   ON [dbo].[PropertyFeature]([FeatureId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId]
   ON [dbo].[PropertyFeature]([PropertyId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_PropertyFeature-FeatureId]
   ON [dbo].[PropertyFeature]([Id] ASC, [FeatureId] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_PropertyFeature_Property]
   ON [dbo].[PropertyFeature]([Id] ASC, [PropertyId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Operation_PropertyId]
   ON [dbo].[Operation]([PropertyId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Operation_OperationTypeId]
   ON [dbo].[Operation]([OperationTypeId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Price_OperationId]
   ON [dbo].[Price]([OperationId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Price_Operation]
   ON [dbo].[Price]([Id] ASC, [OperationId] ASC);

更新： 我使用的 Azure SQL Server 服務層是標準 S0（10 個 DTU）

處於標準層 (S0) 會導致您的查詢受到顯著限制，從而影響總執行時間。以下是批次中所有 5 條語句的時間，在 Sentry One 計劃資源管理器中查看：

所有 5 條語句的編譯時間、CPU 時間和持續時間的螢幕截圖

如您所見，大多數查詢的持續時間都比 CPU 時間長得多。這通常意味著查詢正在等待某些資源。查看中間查詢，我們可以在 XML 中看到這些等待統計資訊：

<WaitStats>
 <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="939" WaitCount="20" />
 <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="858" WaitCount="61" />
</WaitStats>

查詢基本上花費了整個持續時間等待在 CPU 上調度。您可以在SQL Skills 等待類型庫中查找這些等待的詳細資訊。

查詢事件等待很長時間才能編譯，如“QueryPlan”元素中的統計資訊所示：

<QueryPlan ... CompileTime="711" CompileCPU="67" ... >

所有 5 條語句都有相似的特徵（高資源等待，低 CPU）。

可以對查詢進行一些改進。例如，由於使用了表變數，@properties導致中間查詢的估計錯誤，從一開始就導致不理想的計劃選擇（#temp在這種情況下，表可能會更好）。

但是，如果沒有所有的等待，這整批查詢將在不到一秒的時間內執行。因此，除非您有更多可用硬體，否則調整查詢和索引將無濟於事。

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

相關問答

Sql-Server

了解高度獨特的 WHERE 子句的性能

  • September 12, 2015
檢視問答
Sql-Server

僅在使用 SESSION_CONTEXT 時在 Azure 中進行非並行計劃

  • March 12, 2022
檢視問答
Sql-Server

在 Azure SQL 中使用 DTU 時如何確定 MAXDOP 設置

  • March 9, 2022
檢視問答
Sql-Server

對於在單個表中包含每個使用者數十億行的 sql 數據庫，最有效的設計模式是什麼？

  • February 10, 2022
檢視問答
Sql-Server

查詢儲存需要很長時間才能載入持續時間

  • December 14, 2021
檢視問答
Sql-Server

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答