Wybierz * z widoku zajmuje 4 minuty

Występuje problem polegający na tym, że po uruchomieniu zapytania dotyczącego widoku trwa to ponad 4 minuty. Jednak gdy uruchamiam wnętrzności zapytania, kończy się ono w ciągu 1 sekundy.

Jedyne, czego nie jestem pewien, to, że połączone tabele to tabele czasowe.

Plan zapytań ad hoc: https://www.brentozar.com/pastetheplan/?id=BykohB2p4

Zobacz plan zapytań: https://www.brentozar.com/pastetheplan/?id=SkIfTHh6E

Jakieś sugestie, gdzie spróbować to rozgryźć?

Wyświetl kod:

ALTER VIEW [dbo].[vwDealHistoryPITA]
AS
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.LastUpdateDate) AS Deal_HistoryID,
       cm.CodeMasterID,
       cm.ProjectName,
       cm.[Status],
       d.CompanyID,
       d.DealTypeMasterID,
       cm.[Description],
       d.PassiveInd,
       d.ApproxTPGOwnership,
       d.NumberBoardSeats,
       d.FollowonInvestmentInd,
       d.SocialImpactInd,
       d.EquityInd,
       d.DebtInd,
       d.RealEstateInd,
       d.TargetPctgReturn,
       d.ApproxTotalDealSize,
       cm.CurrencyCode,
       d.ConflictCheck,
       cm.CreatedDate,
       cm.CreatedBy,
       cm.LastUpdateDate,
       cm.LastUpdateBy,
       d.ExpensesExceedThresholdDate,
       d.CurrentTPGCheckSize,
       d.PreferredEquityInd,
       d.ConvertibleDebtInd,
       d.OtherRealAssetsInd,
       d.InitialTPGCheckSize,
       d.DirectLendingInd,
       cm.NameApproved,
       cm.FolderID,
       cm.CodaProcessedDateTime,
       cm.DeadDate,
       d.SectorMasterID,
       d.DTODataCompleteDate,
       cm.ValidFrom AS CodeMasterValidFrom,
       cm.ValidTo   AS CodeMasterValidTo,
       d.validFrom  AS DealValidFrom,
       d.validTo    AS DealValidTo
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID;
GO

Dodałem partycję i uzyskałem podobne wyniki do zapytania ad hoc.

Główne różnice w wydajności

Główne różnice tutaj polegają na tym, że bardziej wydajne zapytanie przesuwa predykat wyszukiwania CodeMasterIDna wszystkich 4 tabelach (2 tabelach czasowych (rzeczywista i historia)), gdzie wydaje się, że zaznaczenie w widoku nie robi tego aż do końca (operator filtru) .

TL DR;

Problem wynika z tego, że w niektórych przypadkach, na przykład widokach, parametry nie dociskają do funkcji okna. Najłatwiejszym rozwiązaniem jest dodanie OPTION(RECOMPILE)do wywołania widoku, aby optymalizator „zobaczył” parametry w czasie wykonywania, jeśli jest to możliwe. Jeśli rekompilacja planu wykonania dla każdego wywołania zapytania jest zbyt droga, rozwiązaniem może być funkcja o wartości tabeli wbudowanej, która oczekuje, że parametr może być parametrem. Jest na nim doskonały blog na blogu Paula White'a . Aby uzyskać bardziej szczegółowy sposób na znalezienie i rozwiązanie konkretnego problemu, czytaj dalej.

---

Lepsza wydajność zapytania

Stół Codemaster

Tabela rozdań

Uwielbiam zapach poszukiwanie predykatów rano

---

Wielkie złe zapytanie

Stół Codemaster

Jest to strefa tylko dla predykatów

Tabela rozdań

Ale optymalizator nie przeczytał „Sztuka umowy”

... i nie uczy się z przeszłości

Aż wszystkie te dane dotrą do operatora filtru

---

Co więc daje?

Głównym problemem tutaj jest to, że optymalizator nie „widzi” parametrów w czasie wykonywania z powodu funkcji okna w widoku i niemożności korzystania z SelOnSeqPrj (wybierz projekt sekwencji, w dalszej części tego postu w celach informacyjnych) .

Udało mi się zreplikować te same wyniki na próbce testowej i za pomocą SP_EXECUTESQLparametrów sprowadzić wywołanie do widoku. Zobacz aneks do DDL / DML

wykonanie zapytania względem widoku testowego z funkcją okna i INNER JOIN

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

Daje to około 4,5 s czasu procesora i 3,2 s czasu, który upłynął

 SQL Server Execution Times:
   CPU time = 4595 ms,  elapsed time = 3209 ms.

Kiedy dodamy słodki uścisk OPTION(RECOMPILE)

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1 OPTION(RECOMPILE)',N'@P1 INT',@P1 = 37155; 

Wszystko jest dobrze.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 98 ms.

Dlaczego

To wszystko ponownie potwierdza, że ​​nie można zastosować @P1predykatu do tabel z powodu funkcji okna i parametryzacji, w wyniku których operator filtru

Problem nie tylko w tabelach czasowych

Załącznik 2

Nawet jeśli nie używasz tabel tymczasowych, dzieje się tak:

Ten sam wynik jest widoczny podczas pisania zapytania w ten sposób:

DECLARE @P1 int = 37155
SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1;

Ponownie optymalizator nie przesuwa predykatu przed zastosowaniem funkcji okna.

Pomijając ROW_NUMBER ()

CREATE VIEW dbo.Bad3
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm 
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

Wszystko dobrze

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad3
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155


 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 33 ms.

więc gdzie nas to wszystko opuszcza?

ROW_NUMBER()Oblicza zanim filtr jest stosowany na złych zapytaniami.

Wszystko to prowadzi nas do tego bloga z 2013 roku autorstwa Paula White'a na temat funkcji i widoków okna.

Jedną z ważnych części naszego przykładu jest to stwierdzenie:

Niestety reguła uproszczenia SelOnSeqPrj działa tylko wtedy, gdy predykat dokonuje porównania ze stałą. Z tego powodu poniższe zapytanie tworzy plan nieoptymalny dla SQL Server 2008 i późniejszych:

DECLARE @ProductID INT = 878;

SELECT
    mrt.ProductID,
    mrt.TransactionID,
    mrt.ReferenceOrderID,
    mrt.TransactionDate,
    mrt.Quantity
FROM dbo.MostRecentTransactionsPerProduct AS mrt 
WHERE
    mrt.ProductID = @ProductID;

Ta część odpowiada temu, co widzieliśmy, kiedy sami deklarowaliśmy parametr / używaliśmy go SP_EXECUTESQLw widoku.

---

Rzeczywiste rozwiązania

1: OPCJA (RECOMPILE)

Wiemy, że OPTION(RECOMPILE)„zobaczenie” wartości w czasie wykonywania jest możliwe. Gdy rekompilacja planu wykonania dla każdego wywołania zapytania jest zbyt droga, istnieją inne rozwiązania.

2: Funkcja wartościowana w tabeli wbudowanej z parametrem

CREATE FUNCTION dbo.BlaBla
(
    @P1 INT
)  
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
     SELECT 
     ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
     cm.CodeMasterID,CM.ManagerID,
     cm.ParentDeptID,d.DealID,
     d.CodeMasterID as dealcodemaster,
     d.EvenMoreBlaID
    FROM dbo.CodeMaster2  cm 
    INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID
    Where cm.CodeMasterID = @P1
    ) 
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.BlaBLa(@P1)',N'@P1 INT',@P1 = 37155

Wynikające z przewidywanych predykatów wyszukiwania

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

Z około 9 logicznymi odczytami w moim teście

3: Pisanie zapytania bez użycia widoku.

Innym „rozwiązaniem” może być napisanie zapytania całkowicie bez użycia widoku.

4: Nie zachowując ROW_NUMBER()funkcji w widoku, zamiast tego określając ją w wywołaniu widoku.

Przykładem tego może być:

CREATE VIEW dbo.Bad2
as
SELECT 
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm 
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT ROW_NUMBER() OVER (PARTITION BY CodeMasterID ORDER BY CodeMasterID) AS Deal_HistoryID,* FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

Istnieją inne kreatywne sposoby rozwiązania tego problemu, ważną częścią jest wiedza, co go powoduje.

---

Dodatek nr 1

CREATE TABLE dbo.Codemaster   
(    
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Codemaster_History))   
;  

CREATE TABLE dbo.Deal   
(    
     DealID int NOT NULL PRIMARY KEY CLUSTERED  
   , CodeMasterID INT  NULL  
   , EvenMoreBlaID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Deal_History))   
;  

INSERT INTO dbo.Codemaster(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;


INSERT INTO dbo.Deal(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Deal_History(CodeMasterId);

CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster_History(CodeMasterId);


SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.*, d.* 
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID

GO
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

-- Very bad shame on you

---

Dodatek nr 2

CREATE TABLE dbo.Codemaster2
(    
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  

);  

CREATE TABLE dbo.Deal2
(    
     DealID int NOT NULL PRIMARY KEY CLUSTERED  
   , CodeMasterID INT  NULL  
   , EvenMoreBlaID int NULL    
);  

INSERT INTO dbo.Codemaster2(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;


INSERT INTO dbo.Deal2(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal2(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster2(CodeMasterId);


SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterId) AS Deal_HistoryID,
cm.*, d.* 
FROM dbo.CodeMaster2 cm 
INNER JOIN dbo.Deal2 d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad2
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm 
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155