May 24, 2013 at 8:32 am
Hello,
I have a query where two tables are joined using a LEFT JOIN. This logical operation is being implemented using a hash match physical operator. I have an index for the join predicate, but my right table is being accessed using a clustered index scan, but this table has more than 12 millions rows. It would be nice if it would be accessed using an index seek.
My question is: is it correct if I assume that SQL Server should have used my index and do an index seek instead of an index scan? I am not sure if it should, because this is a join and the hash match will do the join.
May 24, 2013 at 8:38 am
Its difficult to comment unless seeing the table structures, probably index seek becuase of some column used in where clause or join or in output column is not a part of index. try creating covering index on the columns list.
May 24, 2013 at 8:47 am
George Luiz (5/24/2013)
is it correct if I assume that SQL Server should have used my index and do an index seek instead of an index scan?
No.
To do an index seek means there would need to be a SARGable predicate (where clause predicate) on the second table with an index that can be used for that seek operation
If there is no such predicate, and the number of rows is such that a nested loop join is not the optimal, then it's likely and probably even optimal that you get a table scan. Hash joins don't use indexes on the join columns, the only one of the three join types that uses indexes on the join column to do seek operations on is the nested loop join.
Scans are not automatically bad. If you're reading a large portion of the table, a scan may well be the most optimal option. If you're reading the majority of the table, which do you think's better, a single operation that reads the entire table or multiple reads of a smaller portion of the table?
Now, to answer properly for your case we need to see query and index definitions at the least, execution plan preferably too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply