case performance

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