asked
Why is index scan run on outer join when not using "SELECT TOP"
If I run the following query **without** "TOP" in the Select clause then a index scan is run against table tBookingGuest. When I run the query with "TOP(10)" the optimizer chooses to do an index seek against tBookingGuest with the proper covering index. Why would the optimizer behave this way? SELECT br.bookingid, br.roomnumber, bg.adults, bg.children, bg.infants FROM tBookingRatePlan br inner join booking b on b.bookingid=br.bookingid and b.propertyid = @lpropertyid left join tBookingGuest bg on bg.bookingid = br.bookingid and bg.roomnumber = br.roomnumber group by br.bookingid, br.roomnumber, bg.adults, bg.children, bg.infants ---------------------- Here is some schema information **tBookingRatePlan** id int identity PK bookingid int FK to Booking table roomnumber int **Booking** BookingId int PK PropertyId int **tBookingGuest** Id int PK BookingId int RoomNumber int Adults int Children int Infants int *This table has non clustered index (BookingId,RoomNumber includes:adults,children,infants)
I'd want to look at both execution plans before hazarding a guess.
You say the proper index is used when doing TOP 10. Which index is scanned when you ommit the TOP clause?
To answer Kev, there are only 18 rows returned when I do not use the Select top 10. And the index scan is only against 2868 rows (actual number of rows in execution plan). It bothers me that if all the join values are provided, the optimizer isnt just choosing to do an index seek.
It sounds like that reasoning is backwards. I would think that if you use select top 10 and its working with a smaller resultset, then a scan may be acceptable. If you dont use select top 10 and the resultset is larger, than an index seek would be more beneficial. Why the optimizer is using scan for large resultsets and seek for smaller resultsets is beyond me.
If the optimizer believes that a significant portion of the data will be returned it is more efficient to use the scan. Update your statistics and try again. Try and determine the selectivity of your @lpropertyid column with a DISTINCT query for it.
As Grant says, we are all guessing here anyway - can you post the 2 execution plans (as .sqlplan)
The optimiser may be deciding that an index seek is 'less costly' for the TOP 10, rather than the index scan for the full results. Also with a Top 10, you are asking the engine to do less work, and without an ORDER BY - simply return 10 'random' records. It's always quicker to give someone 10 random records than 100 specific ones. How many rows are returned when not limited by the TOP 10? As with any optimizer question - have you updated your index statistics?
Kev has basically answered the question... it only needs to return the first 10 it finds so it assumes an index seek is the best option as it is a very small sample being requested.
8 People are following this question.
