CODEWITHSUNDEEP
performancedb2
user1902861
user1902861

Reputation: 23

db2 improve performance of coalesce function

Our system has some performance problems about db2 database. Recently we found some SQL may cost too much time to execute. The following one is a simple case

select * from table1 a
inner join table2 b on a.code = coalesce(b.code2,b.code1);

It costs about 1 second,while if I replace "coalesce(b.code2,b.code1)" with "b.code1", it will be 10 times faster.(addition:the table2.code2 only have null value).

In some complex statement, it will be 40 seconds compare to 1 second.

But it will be better if there is a way that can improve the performance while keeping code2 there in case it will be not null in future. I tried create indexes on each column,but it seems no improve at all. I also tried some condition expression replace of coalesce function,and it costs the same

Upvotes: 0

Views: 1399

Answers (1)

Charles
Charles

Reputation: 23793

Anything other than an Equi-join is going to perform worse than an Equi-join; Even though you are using equality operator, the use of coalesce disqualifies it as an Equi-join.

Do you have an index on table2 (code2, code1)? If not, it would probably help.

You could try a union of two Equi-joins

First thought 

select * from table1 a
inner join table2 b on a.code = b.code2
UNION ALL
select * from table1 a
inner join table2 b on a.code = b.code1 and b.code2 is NULL;

Alternatives

select * from table1 a
inner join table2 b on a.code = b.code2
UNION ALL
select * from table1 a
inner join table2 b on a.code = b.code1 
where b.code2 is NULL;

Using UNION instead of UNION ALL forces the DB to remove duplicate rows.

select * from table1 a
inner join table2 b on a.code = b.code2
UNION
select * from table1 a
inner join table2 b on a.code = b.code1;

The following index may or may not be of any use (depending on the statement used and the data):

  • table2 (code2)
  • table2 (code1)
  • table2 (code1,code2)

Upvotes: 2

Related Questions