x

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)
more ▼

asked May 08, 2012 at 01:47 AM in Default

shill1970 gravatar image

shill1970
0 1 1 1

You say the proper index is used when doing TOP 10. Which index is scanned when you ommit the TOP clause?
May 08, 2012 at 07:44 AM Magnus Ahlkvist
I'd want to look at both execution plans before hazarding a guess.
May 08, 2012 at 10:17 AM Grant Fritchey ♦♦

To answer Magnus, The optimizer is using the correct covering index in both cases (whether or not select TOP is used).

Im just questioning why the optimizer would choose to scan rows when im providing the exact key (bookingid,roomnumber) to the index.
May 08, 2012 at 03:01 PM shill1970
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.
May 08, 2012 at 03:07 PM shill1970

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.
May 08, 2012 at 04:25 PM shill1970
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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?
more ▼

answered May 08, 2012 at 10:00 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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.
May 08, 2012 at 03:26 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x12
x11
x4
x2

asked: May 08, 2012 at 01:47 AM

Seen: 802 times

Last Updated: May 08, 2012 at 05:16 PM