case performance

7 Oct 2019

      Hello,

I am exploring MonetDB queries performance that are generated based on user
parameters/preferences.
To be more specific the where clause is generated based on a user interface
mapping user input parameters straight into SQL statements.
See below one example of this transformation.

FIELD contains PATTERN (case sensitive/insensitive)

is transformed into

select ... from TABLE where
(case when (user_case_sensitive_pref=1) then ("FIELD" like '%PATTERN%')
else  ("FIELD" ilike '%PATTERN%')
end);

Performance of this in comparison with query generated from within the code:

char * query;
if ( user_case_sensitive_pref )
  query = "select ... from TABLE where  \"FIELD\" like '%PATTERN%'";
else
  query = "select ... from TABLE where  \"FIELD\" ilike '%PATTERN%'";

query_execute( query);

is huge (second approach takes 0.12 sec, vs 6 sec for first one).
But it requires a far more complex implementation.

First I thought this has to do with user variables but even if the
statement is

select ... from TABLE where
(case when TRUE then ("FIELD" like '%PATTERN%')
else  ("FIELD" ilike '%PATTERN%')
end);

timing is similarly slow.

This narrows down to "case" performance issue, as far as I can see.

Am I wrong?

Thank you,
Dan

case performance

Daniel Zvinca