英文:

Make a SQL Server stored procedure statement faster, execution plan shown

问题 {#heading}

SELECT BookingDate.[Date] AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #MonthOfRoomBookings
FROM Dates AS BookingDate
INNER JOIN ProviderAccommodationRoomBooking AS PABR ON PABR.CreationDate >= BookingDate.[Date]
AND PABR.CreationDate < DATEADD(DAY, 1, BookingDate.[Date])
WHERE BookingDate.[Date] >= @AsFarBackAs
AND BookingDate.[Date] <= @DateTo
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL 英文:

I am seeing if I can increase the performance of the following query, I cant easily provide the data but have provided the execution plan and what I think is the issue.

SELECT BookingDate.[Date] AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #MonthOfRoomBookings
FROM Dates AS BookingDate 
INNER JOIN ProviderAccommodationRoomBooking AS PABR ON PABR.CreationDate >= BookingDate.[Date] 
AND PABR.CreationDate < DATEADD(DAY, 1, BookingDate.[Date]) 
WHERE BookingDate.[Date] >= @AsFarBackAs 
AND BookingDate.[Date] <= @DateTo
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL

I am using this index

CREATE NONCLUSTERED INDEX IX_ProviderId_ProviderHotelId_CreationDate_StartDate_EndDate
ON dbo.ProviderAccommodationRoomBooking(ProviderId, ProviderHotelId, CreationDate, StartDate, EndDate, CancelledDate)
WITH (MAXDOP = 0, ONLINE = OFF)

At the moment the stored procedure runs in about 800 ms but I feel that if SQL could filter on the CreationDate at the point of the index seek, it would be faster, as it seems to return 61162 rows, but when it gets to the Index Spool these are then filtered by the CreationDate and I only need 793.

Would be good to know if I can make this faster?

As requested the paste the plan link for this

https://www.brentozar.com/pastetheplan/?id=rk3hFdz33

答案1 {#1}

得分: 0

以下是要翻译的内容：

Ok so I was lucky enough to have a clever fellow (Charlie) who works in another department correct what I had done. The Eager spool had now gone and more of what I expected is being done. The new plan can be seen here

https://www.brentozar.com/pastetheplan/?id=HJ_Lav72h

The query in question went from

SELECT BookingDate.[Date] AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #MonthOfRoomBookings
FROM Dates AS BookingDate
INNER JOIN ProviderAccommodationRoomBooking AS PABR ON PABR.CreationDate >= BookingDate.[Date] AND PABR.CreationDate < DATEADD(DAY, 1, BookingDate.[Date])
WHERE BookingDate.[Date] >= @AsFarBackAs
AND BookingDate.[Date] <= @DateTo
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL

to this

SELECT CONVERT(DATE, PABR.CreationDate) AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #RoomsIWant
FROM ProviderAccommodationRoomBooking AS PABR
WHERE PABR.CreationDate >= @StartDate AND PABR.CreationDate < DATEADD(DAY, 1, @BookingDate)
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL

So I was making it more complex exploding the date range and using that to drive it, when actually I could do it all in the WHERE based on the creation date. 英文:

Ok so I was lucky enough to have a clever fellow (Charlie) who works in another department correct what I had done. The Eager spool had now gone and more of what I expected is being done. The new plan can be seen here

https://www.brentozar.com/pastetheplan/?id=HJ_Lav72h

The query in question went from

SELECT BookingDate.[Date] AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #MonthOfRoomBookings
FROM Dates AS BookingDate 
INNER JOIN ProviderAccommodationRoomBooking AS PABR ON PABR.CreationDate >= BookingDate.[Date] AND PABR.CreationDate < DATEADD(DAY, 1, BookingDate.[Date]) 
WHERE BookingDate.[Date] >= @AsFarBackAs 
AND BookingDate.[Date] <= @DateTo
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL

to this

SELECT CONVERT(DATE, PABR.CreationDate) AS BookingDate, PABR.StartDate, PABR.EndDate
INTO #RoomsIWant
FROM ProviderAccommodationRoomBooking AS PABR
WHERE PABR.CreationDate >= @StartDate AND PABR.CreationDate < DATEADD(DAY, 1, @BookingDate)
AND PABR.ProviderId = @ProviderId
AND PABR.ProviderHotelId IN (SELECT ProviderHotelId FROM #ProviderDetailsIWant)
AND PABR.CancelledDate IS NULL

So I was making it more complex exploding the date range and using that to drive it, when actually I could do it all in the WHERE based on the creation date.