x

Index Scan instead of Index Seek

Dear All,

I need help on one of the query. My execution plans shows Index Scan in spite of using the Indexed Column in the Join predicate. I have two tables and one is 35 million and another one with 10K rows. I am trying to do a left outer join and my execution plans shows up Hash Match with Index Scan. I am not sure on how to improve this query. Also, my statistics are updated. Please can someone help on this issue?

Many thanks in advance.

Cheers - DV

SELECT     
       dbo.Test_Sales.Productinterfacestorageid, 
       dbo.Test_Sales.[County Name], 
       dbo.Test_Sales.Unitary_Name, 
       dbo.Test_Sales.Ward_Name, 
       dbo.Test_Sales.Postcode, 
       dbo.Test_Sales.Sector, 
       dbo.Test_Sales.ISBA, 
       dbo.Test_Sales.[Storage Date], 
       dbo.Test_Sales.[Storage Hour], 
       dbo.Test_Sales.[Storage Day], 
       dbo.Test_Sales.Website, 
       dbo.Test_Sales.Age, 
       dbo.Test_Sales.[Age Band], 
       dbo.Test_Sales.Carbrandtext, 
       dbo.Test_Sales.Carinsuredvalue, 
       dbo.Test_Sales.Carmanufacturingyear, 
       dbo.Test_Sales.Premiumtotalmotor, 
       dbo.Test_Sales.[Name and Cluster code], 
       dbo.Test_Sales.Affluence, 
       dbo.Test_Sales.Lifestage, 
       dbo.Test_Sales.Occupation, 
       dbo.Test_Sales.Gender, 
       dbo.Test_Risk_Factor.Rating
FROM         dbo.Test_Sales LEFT OUTER JOIN
                      dbo.Test_Risk_Factor 
ON 
dbo.Test_Sales.Sector = dbo.Test_Risk_Factor.Sector
more ▼

asked Nov 01 '11 at 08:04 AM in Default

OnlyGraphite gravatar image

OnlyGraphite
41 6 6 7

Is Test_Sales the 35 million one, and Test_Risk_Factor the 10k one?
Nov 01 '11 at 08:14 AM Kev Riley ♦♦
Yes, that's correct.
Nov 01 '11 at 08:16 AM OnlyGraphite

Hi Again,

Sorry for not providing this information earlier. Here you go...

Test_Sales.Productinterfacestorageid – Clustered Index
Test_Sales.Sector – Non Clustered Index with Included Column (PostCode)

Test_Risk_Factor.Sector – Clustered Index

Thanks again for your help.

Cheers - DV
Nov 01 '11 at 08:35 AM OnlyGraphite
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Hash match is common if the inputs are unsorted, so the optimizer needs to build a hash table and probe it from the other table, rather than use the 'faster' merge join.

What indexes are on these tables? And can you post the execution plan, either as an image or as XML?


I had a quick look before you removed the pasted XML plan, and there seemed to be a datatype conversion on the sector field - this could be throwing the optimizer out.


Based on what you have said below, and the amount of data being processed, the hash match may be the best operator. The reason for the index scan, rather than the index seek is simply because you have no WHERE clause - there is nothing to filter on and hence nothing to 'seek'

more ▼

answered Nov 01 '11 at 08:21 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Sorry for the mess with the XML excecution plan. I understand its hard to read, but I am unable to attach the execution plan image here. Sorry :(
Nov 01 '11 at 08:40 AM OnlyGraphite
what are the datatypes of Test_Sales.Sector and Test_Risk_Factor.Sector?
Nov 01 '11 at 08:52 AM Kev Riley ♦♦
Test_Sales.Sector is NVarchar(8) and Test_Risk_Factor.Sector is Varchar(50)
Nov 01 '11 at 08:55 AM OnlyGraphite
This query is taking 5 hours to complete and it is way too long to complete. :(
Nov 01 '11 at 08:56 AM OnlyGraphite

Well it is returning 35 million rows......

Where is the data going to? It can't possibly be being returned to an application for viewing by a user!

Try changing the datatypes (on your test system) to match - see if that changes the query plan.
Nov 01 '11 at 09:00 AM Kev Riley ♦♦
(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:

x38

asked: Nov 01 '11 at 08:04 AM

Seen: 1340 times

Last Updated: Nov 01 '11 at 08:11 AM