Hash Match performance problem

[katbear]

Hi,

I have a part of a query that is quite expensive:

LEFT JOIN
myTable t1
ON t1.SyId = pos.SyId and t1.CuId = cu.CuId and t1.Date = @AsOfDate
LEFT JOIN
myTable t2
ON t2.SyId = pos.SyId and t2.CuId = cu.CuId and t2.Date = @StartDate-1
LEFT JOIN
myTable t3
ON t3.SyId = pos.SyId and t3.CuId = cu.CuId and t3.Date = @StartDate-1

As you can see, it's joining against itself depending on the "Date". Yes, I see that t2 and t3 appear to be doing the same thing! I removed one of them, but the execution plan didn't change one iota.

The problem is, this particular part of the query is taking up 100% of the cost with 2 full index scans on myTable's PK, and a corresponding "Hash Match".

The PK is on (SyId, CuId, Date) in that order. Why is it still doing index scans??

Any suggestions on how to tune this sucker? Alternative ways of writing this part of the query? Any advice appreciated!!!

 

[bborissov]

How about only one JOIN:

LEFT JOIN
      myTable t1
      ON t1.SyId = pos.SyId and 
         t1.CuId = cu.CuId  and
         (t1.Date = @AsOfDate or t1.Date = @StartDate-1)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[nice95gle]

I think a join on the _PK or any join on an indexed table will produce an index scan. (I might be wrong)

Maybe you should run a ITW for 2000 or DTA for 2005.
If anything it will give you a good starting point for improvement.

Well Done is better than well said
- Ben Franklin

 

[katbear]

Changing the join changes the result, unfortunately. And does not eliminate the index scans of hash match. They are still there, just in a different place.

 

[gmmastros]

Do you have an index on the columns that are participating in the join?

I would suggest a composite index on "myTable" that includes the column SyId, CuId, and Date.


-George

"the screen with the little boxes in the window." - Moron

 

[katbear]

Yes, there's already a PK on syid, cuid, and date.

Seems to be an issue with the self-join, from what I've seen in other posts.

The query is *extremely* complex in its entirely, but this part of the query uses up 100% of the execution cost.