user3174976 user3174976 - 5 months ago 11
SQL Question

Performance of nested select and DISTINCT/ORDER

My original query is:

SELECT DISTINCT
Member.Id,
Member.Email,
Booking.ItemId
FROM Member WITH(NOLOCK)
JOIN Booking WITH(NOLOCK) ON Member.Id = Booking.CustomerId
WHERE DATEDIFF(d, Booking.BookingDate, GETUTCDATE()) > 1


I tried to order the result by Booking.BookingDate, and added the Booking.BookingDate related information as a new column in the returned result:

So, the new query will look like:

SELECT *
FROM
(
SELECT DISTINCT
Member.Id,
Member.Email,
Booking.ItemId,
Booking.BookingDate
FROM Member WITH(NOLOCK)
JOIN Booking WITH(NOLOCK) ON Member.Id = Booking.CustomerId
WHERE DATEDIFF(d, Booking.BookingDate, GETUTCDATE()) > 2
) a
Order by BookingDate


I use SSMS's execution plan to compare these two queries, and they look the same.
Is there any way I can make sure my rewriting won't make performance slow?

Answer

You don't need a subquery (although that doesn't affect performance). So:

SELECT DISTINCT m.Id, m.Email, b.ItemId, b.BookingDate
FROM Member m JOIN
     Booking b
     ON m.Id = b.CustomerId
WHERE b.BookingDate > DATEADD(day, 1, GETUTCDATE())
ORDER BY BookingDate;

I really doubt you need the SELECT DISTINCT, because duplicates seem unlikely. Notice that the table aliases make the query easier to write and to read.

Comments