CODEWITHSUNDEEP
sqlsql-serverquery-optimizationinner-join
Jan Drozen
Jan Drozen

Reputation: 984

Hash and merge join never finishes, loops does

I ran into issues with one query that used to work fine in the past, but now it is so slow so it basically never finishes (perhaps in some hours, beyond any timeouts).

The query is like

select ky.issuer_id from T_MASTER_ISSUER_KEYS ky 
inner join T_ISSUER_SECURITY_LIST lst on lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null

And its live execution plan looks like that enter image description here

The live execution still stuck at the Hash Match join step.

However, if I force a nested loops join instead

select ky.issuer_id from T_MASTER_ISSUER_KEYS ky 
inner loop join T_ISSUER_SECURITY_LIST lst on lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null

Then it finishes in a second, as expected: enter image description here

I already tried create some covering indices to include all the join and selection attributes, but nothing helped there. To me it looks like a hash join of 100k and 300k rows should be a piece of cake for the server.

Moreover, if I remove any of the two join conditions, the query works fine:

select ky.issuer_id from T_MASTER_ISSUER_KEYS ky 
inner join T_ISSUER_SECURITY_LIST lst on /*lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND*/ lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG
WHERE ky.ISSUER_ID_RDE is not null

enter image description here

The joining columns are VARCHAR(20), could it have any impact on the join?

Upvotes: 0

Views: 89

Answers (1)

SQLpro
SQLpro

Reputation: 5169

Rewrite as :

select ky.issuer_id 
from   T_MASTER_ISSUER_KEYS AS ky 
WHERE  EXISTS(SELECT 1/0
              FROM   T_ISSUER_SECURITY_LIST AS lst 
              WHERE  lst.ISSUER_ID_SIX = ky.ISSUER_ID_SIX AND 
                     lst.ISSUER_ID_BBG = ky.ISSUER_ID_BBG AND
                     ky.ISSUER_ID_RDE is not null);

Create theses index if not exists :

-- index for ky : 
CREATE INDEX X_Jan_Drozen_T_MASTER_ISSUER_KEYS_001
   ON  T_MASTER_ISSUER_KEYS (ISSUER_ID_RDE, ISSUER_ID_SIX, ISSUER_ID_BBG) INCLUDE (issuer_id);
-- index for lst :
CREATE INDEX X_Jan_Drozen_T_ISSUER_SECURITY_LIST_001
   ON  T_ISSUER_SECURITY_LIST (ISSUER_ID_SIX, ISSUER_ID_BBG) ;

Upvotes: 0

Related Questions