Any ideas why SQL is making a Hash of my Query Plan?

Question

Any ideas why SQL is making a Hash of my Query Plan?

NigelFN

Default port

Points: 1467
More actions
July 22, 2004 at 11:45 am

#108573

Hi there,
Please can anyone shed any light or suggestions on my problem:
I have two tables, one is a reference table and the other a fact table. Both are heavily indexed (not my doing!) but for this example, the reference table has a PK containing a unique reference value for every entry.
The fact table does have a useful index (on the column that is used to join to the reference table), but SQL Server insists on not using it and doing a clustered table scan instead.
If I use an optimizer hint to specify the index, it is used and the query runs at breakneck speed, left to its own devices, it crawls along.
I have looked at the query plans and have found the following:
-- Optimizer hint
select Fact.fiscal_month, Fact.Pear_Cd
FROM fruit fact (index=IX_fruit_1) ,
pears Pr
WHERE Fact.Pear_Cd = Pr.Pear_Cd AND
  Pr.Variety IN ('Conference', 'Desert', 'Nice')

Execution Tree
--------------
Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Grocers].[dbo].[fruit] AS [fact]))
|--Nested Loops(Inner Join, OUTER REFERENCES[Pr].[Pear_Cd]) WITH PREFETCH)
       |--Clustered Index Scan(OBJECT[Grocers].[dbo].[pears].[PK_pears] AS [Pr]), WHERE([Pr].[Variety]='Nice' OR [Pr].[Variety]='Desert') OR [Pr].[Variety]='Conference'))
       |--Index Seek(OBJECT[Grocers].[dbo].[fruit].[IX_fruit_1] AS [fact]), SEEK[fact].[Pear_Cd]=[Pr].[Pear_Cd]) ORDERED FORWARD)
-- No Optimizer hint
select Fact.fiscal_month, Fact.Pear_Cd
FROM fruit fact ,
pears Pr
WHERE Fact.Pear_Cd = Pr.Pear_Cd AND
  Pr.Variety IN ('Conference', 'Desert', 'Nice')
Execution Tree
--------------
Hash Match(Inner Join, HASH[Pr].[Pear_Cd])=([fact].[Pear_Cd]), RESIDUAL[Pr].[Pear_Cd]=[fact].[Pear_Cd]))
|--Clustered Index Scan(OBJECT[Grocers].[dbo].[pears].[PK_pears] AS [Pr]), WHERE([Pr].[Variety]='Nice' OR [Pr].[Variety]='Desert') OR [Pr].[Variety]='Conference'))
|--Clustered Index Scan(OBJECT[Grocers].[dbo].[fruit].[PK_fruit] AS [fact]))
After some investigation, there was a discrepancy on the format of the joining column (fact table was not null, lookup table was null, and the column length differed by one.) This was fixed and the query is marginally better, but still to slow. New plan:
Execution Tree
--------------
Parallelism(Gather Streams)
    |--Hash Match(Inner Join, HASH[Pr].[Pear_Cd])=([fact].[Pear_Cd]), RESIDUAL[Pr].[Pear_Cd]=[fact].[Pear_Cd]))
         |--Parallelism(Broadcast)
         |    |--Clustered Index Scan(OBJECT[Grocers].[dbo].[pears].[PK_pears] AS [Pr]), WHERE([Pr].[Variety]='Nice' OR [Pr].[Variety]='Desert') OR [Pr].[Variety]='Nice'))
       |--Clustered Index Scan(OBJECT[Grocers].[dbo].[Fruit].[PK_Fruit] AS [fact]))

If anyone can shed any light on why the Index is not used, I would be very grateful. It is a very large table, so a Table Scan is certainly not the best option.
Cheers,
Nigel.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Clay_G Default port Points: 1454 More actions · Answer 1

Given your table is named FRUIT does that mean Pears are only one of many types ?

If so, I suspect the problem would be that most fruit do not have a pear_cd, the optimiser doesnt know that pear_cd will be a good match.

NigelFN Default port Points: 1467 More actions · Answer 2

Hi Clay G,

Thanks for your response. I deliberately renamed all of the object names for anonymity, but this seems to have caused more confusion than it was worth.

We have now partly resolved the problem by changing the clustered index to a more useful definition.

Cheers,

Nigel.