A question on Hash Joins.

[JNeave]

I have a large complex query in a stored procedure for searching on Cases.

It currently runs in 8 seconds.

Yesterday I fluked a FROM clause that caused it to run in 2 seconds. I was pleased.

I then managed to lose all my work.

I am now not pleased.

The Query Analyser showed the Plan to use strange compound Hash Joins, all contributing to 1 Hash table. Whatever I did, I can't replicate it this morning.

5 tables.

The main table is the Cases table, very big. Joined to 2 other very big tables containing Case Properties, which is in turn contained to all possible addresses for that case in the Addresses table..

There are also 2 small look up tables for status codes joined to the Cases table.

Now the Case Properties and Addresses table use Merge join. The Status tables SHOULD use Nested Loop, but don't, and the query is back to 8 seconds again, no matter how I re-arranged the FROM clause.

Does anybody have any clue what I managed to do and what those complex Hash joins were all about?

cheers,

Jim.

 

[ColinM]

Use can use table hints so as:

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' ' + LTRIM(a.au_lname)), 1, 25)
AS Name, SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta
ON a.au_id = ta.au_id INNER HASH JOIN titles t
ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC

taken from BOL

Might be worth experimenting with these.
Hash Joins are explained in BOL as well.

 

[JNeave]

Read them. Fairly helpfull. Got the query down to 4-5 seconds with random FROM re-arranging. Can't get it to do the compound hash join anymore, which really gave it some speed. God I'm pissed off.

Seriously, I'm gonna bite someone soon...

cheers,

Jim.