Slow case statement evaluation comparing values from two tables

[patrickbachmann]

Hi folks,

We have an SQL type calculation view that is performing badly.  Upon analyzing the visual plan we have found the root of the problem due to a case statement that is simply evaluating two tables and if a field is null in one table then choose the other alternate table field;  something like this;

select

case when a.Customer IS NULL THEN

b.Customer

else

a.Customer

End

As TheCustomer

from table1 a join table2 b on a.XYZ = b.XYZ

etc

If we take the case statement away and simply return both values, the query runs very fast around 2 seconds.  ie: something like this;

select

a.Customer as Customer1,

b.Customer as Customer2

from table1 a join table2 b on a.XYZ = b.XYZ

etc

But doing the case evaluation between the two field values bumps the execution time up to more than 60 seconds!

Has anybody experienced this and do you have any alternative suggestions to achieve this sort of thing?  We have also tried doing a second pass and putting the case evaluation OUTSIDE the original select statement and there is some big improvement yet still not satisfactory.  ie: something like this;

select

case when a.Customer IS NULL THEN

b.Customer

else

a.Customer

End

As TheCustomer from

( select

a.Customer as Customer1,

b.Customer as Customer2

from table1 a join table2 b on a.XYZ = b.XYZ

etc)

Thanks,

-Patrick

[former_member182302 · Accepted solution]

Did you try COALESCE as well?

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Spot on!

COALESCE would be the way to do it. The only thing you have to take care of is to check whether tie values are resolved in your interest.

However, using CASE, COALESCE, IFNULL... in the projection list will always add a lot of execution time as these expressions need to be evaluated on the whole set of returned records.

- Lars

[Former Member]

Hi Patrick,

You mentioned that separating the two SQL statements with each SQL execution runs for 2 seconds.

Can you try to use UNION and see how long it takes?

For example:

select a.CUSTOMER

from table1 a where a.XYZ is not NULL

UNION

select b.CUSTOMER

from table2 b join table 1 a on b.XYZ=a.XYZ WHERE a.XYZ is NULL

Regards,

Ferry

[patrickbachmann]

Hi guys,

After reading your suggestion we tried coalesce however we did not see any improvement whatsoever.  Also thanks for the tips Lars.  What we did find was that if we used input parameters the performance was greatly improved.  Strangely the original case statement OR the coalesce both performed well when we restarted our server (we have seen strange issues with 68 like this) but only when running the entire raw SQL script inside SQL editor.  ie: if our calculation view contains 100 lines of code and we paste it into SQL editor it works great.  Alternatively if we reference the view directly like this;

select * from "SYS_BIC"."CalcViewName" where condition = 'abc'

This performance is bad.  If we parameterize the calculation view and change the way we reference the view like this it is now flying;

select * from "SYS_BIC"."CalcViewName"

('PLACEHOLDER' = ('$$CONDITION$$', ''abc'''))

This makes sense to me as it seems to be pushing the filter to the beginning of the query.  What does not make sense to us is why running the entire SQL directly in SQL editor (without parameters) works very fast as well.  It's only when calling the view directly via SYS_BIC that it becomes slow and then requires the input parameters.  You might then ask why we care, just put the input parameters in the view and call it done!  Well the issue is with Microstrategy tool that we are using which has limitation around multiple values in a single parameter.

-Patrick

[lbreddemann]

Just to mention it: very good way to put the question!

If everybody would do it like this, providing answers would be a whole lot easier.

In case you want to improve this even further, please provide a working example next time.

Something like this:

create column table customers (id integer, name varchar(30))

insert into customers values (1, 'BLA');

insert into customers values (2, NULL);

insert into customers values (3, 'BLUPP');

insert into customers values (4, NULL);

insert into customers values (5, 'BLIP');

insert into customers values (6, 'BLOP');

select c1.id, c1.name as c1name, c2.name as c2name, coalesce (c1.name, c2.name)

from

    customers c1 inner join customers c2

                 on c1.id = c2.id+1

order by c1.id                

is already sufficient to work with your issue.

- Lars