Is there a way to get this to seek in the 2nd query?

I broke the query apart into a inital dump to a temp table containing the primary key values to join in the 2nd query. The temp table has a clustered primary key on the key field, and the joining table has a clustered primary key on the same field. There are only 22k records in the main table, of which I am returning 6k. I am thinking is just a matter of it being easier to scan for 6k records than seek, but wasnt sure.

avatar image By John M Couch 16 asked May 18 at 06:02 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

It's scanning the table rather than doing 6141 seeks. Each seek would need to look at probably three levels of the index, making over 18k page reads. Your 22130 rows fit into 10MB, which is only about 1300 pages. Using these numbers, it's figuring that a scan is MUCH better. But your index is very useful, because it allows a Merge Join to be done.

avatar image By Rob Farley 196 answered May 26 at 06:43 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x39
x24
x17

asked: May 18 at 06:02 PM

Seen: 540 times

Last Updated: May 26 at 06:43 AM