英文:
Make a SQL Server stored procedure statement faster, execution plan shown
问题
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
答案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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论