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
Hi Daniel,
[ the Users mailing list is probably more suited for your question ]
The main reason why your first approach is slower is that MonetDB is column-oriented. It doesn't evaluate your condition per input tuple, but per input column.
See the EXPLAIN of your example, using one of my existing databases (TABLE= spinque.info, FIELD=attribute, PATTERN=lock):
| C_9:bat[:oid] := sql.tid(X_8:int, "spinque":str, "info":str);
| | X_12:bat[:str] := sql.bind(X_8:int, "spinque":str, "info":str, "attribute":str, 0:int); | | (X_17:bat[:oid], X_18:bat[:str]) := sql.bind(X_8:int, "spinque":str, "info":str, "attribute":str, 2:int); | | X_15:bat[:str] := sql.bind(X_8:int, "spinque":str, "info":str, "attribute":str, 1:int); | | X_21:bat[:str] := sql.projectdelta(C_9:bat[:oid], X_12:bat[:str], X_17:bat[:oid], X_18:bat[:str], X_15:bat[:str]); | | X_26:bat[:bit] := algebra.project(X_21:bat[:str], false:bit);
| Just binding input columns to variables
| X_32:bat[:bit] := batcalc.ifthenelse(X_26:bat[:bit], false:bit, true:bit); Evaluating your first constant condition (user_case_sensitive_pref=1)
| X_36:bat[:bit] := batalgebra.like(X_21:bat[:str], "%lock%":str);
| Evaluating the "then" branch
| X_40:bat[:bit] := batalgebra.ilike(X_21:bat[:str], "%lock%":str);
| Evaluating the "else" branch
| X_42:bat[:bit] := batcalc.ifthenelse(X_32:bat[:bit], X_36:bat[:bit], X_40:bat[:bit]); | Evaluating the condition. This produces a boolean column with either the value from the "then" branch or the value from the "else" branch
| C_45:bat[:oid] := algebra.thetaselect(X_42:bat[:bit], true:bit, "==":str); | Evaluating where, in the original column, the condition is satisfied
| X_47:bat[:str] := algebra.projection(C_45:bat[:oid], X_21:bat[:str]);
| Fetching the values, in the original column, where the condition is satisfied
| sql.resultSet(X_59:bat[:str], X_61:bat[:str], X_63:bat[:str], X_65:bat[:int], X_67:bat[:int], X_47:bat[:str]); |
This is really an overkill for your statement. Firstly, because it always evaluates fully both the output branches of your condition. Secondly, because of the overhead needed for this approach. Without a specialised "contains" UDF, this is how MonetDB handles it in a generic way. It is the price to pay to get the benefits that column decomposition brings in other cases.
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api ( https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti...), and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
You could also try the relatively new JIT C UDF mechanism, which allows you to write your function in C but with a simpler API and without recompiling: https://www.monetdb.org/blog/JIT_C_C%252B%252B_UDFs_in_MonetDB
Best regards, Roberto
On Mon, 7 Oct 2019 at 13:17, Daniel Zvinca daniel.zvinca@logbis.com wrote:
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
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti...), and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api (
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ),
and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Just to mention, you can also force a tuple-at-the-time evaluation in pure SQL:
-- BEGIN declare cs boolean; set cs=true;
START TRANSACTION;
CREATE OR REPLACE FUNCTION mycontains(s STRING, p STRING, casesensitive boolean) RETURNS BOOLEAN BEGIN IF casesensitive THEN RETURN SELECT s like '%'||p||'%'; ELSE RETURN SELECT s ilike '%'||p||'%'; END IF; END;
CREATE TABLE t(s STRING); INSERT INTO t VALUES ('apple'),('pear'),('banana'),('orange');
explain select * from t where mycontains(s,'an',cs);; -- END
If you look at this explain, you'll see it makes a loop that calls the mycontains() function. This does avoid evaluating both branches. However the explicit interpreted loop won't make it very fast, perhaps see on your data. (Note: when the function is simpler, the loop is actually done in C)
On Mon, 7 Oct 2019 at 16:53, Daniel Zvinca daniel.zvinca@logbis.com wrote:
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api (
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ),
and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Your last reply, Roberto, makes me wonder if it would be an idea to implement a scalar capi as well (for now is bat only). Then for sure the loop will be entirely done in C.
BAT operations are faster for most of the operations, but when formulas include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
On Mon, Oct 7, 2019, 18:39 Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Just to mention, you can also force a tuple-at-the-time evaluation in pure SQL:
-- BEGIN declare cs boolean; set cs=true;
START TRANSACTION;
CREATE OR REPLACE FUNCTION mycontains(s STRING, p STRING, casesensitive boolean) RETURNS BOOLEAN BEGIN IF casesensitive THEN RETURN SELECT s like '%'||p||'%'; ELSE RETURN SELECT s ilike '%'||p||'%'; END IF; END;
CREATE TABLE t(s STRING); INSERT INTO t VALUES ('apple'),('pear'),('banana'),('orange');
explain select * from t where mycontains(s,'an',cs);; -- END
If you look at this explain, you'll see it makes a loop that calls the mycontains() function. This does avoid evaluating both branches. However the explicit interpreted loop won't make it very fast, perhaps see on your data. (Note: when the function is simpler, the loop is actually done in C)
On Mon, 7 Oct 2019 at 16:53, Daniel Zvinca daniel.zvinca@logbis.com wrote:
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api (
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ),
and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Daniel,
I personally would put it the other way around. The scalar version should in principle always be there, as it guarantees that every query works.
Then you look at optimizing performance. 1) leave it as is and let it wrap in a MAL loop 2) If it is a C function, it will likely be wrapped in a C loop by manifold. 3) For best performance, you may want to implement the loop yourself with a BAT implementation.
When doing what depends on the nature of the function. If every scalar call requires costly initialisations or allocations, then the first two options can be very slow. In that case you want to choose option 3 and do all initialisations and allocations outside the loop. Your function is essentially a tight loop around a strstr() call, so in principle I don't expect much difference between 2) and 3).
There is a catch though. You have a boolean parameter to control casessensitive. So in case of a BAT implementation, you have 3 columns in input (*also for the possibly constant boolean column*) and your loop looks like (pseudocode):
loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern)
Conditions inside loops are not your best friends. Given that in most use cases your boolean column will be constant (either all true or all false), you can optimize this by doing:
if (cs.sorted && cs.revsorted) /* cs is constant */ { if (cs[0]) { loop(string,pattern,cs) strstr(sring,pattern); } else { loop(string,pattern,cs) strcasestr(string,pattern) } } else { /* cs not constant, check at every tuple */ loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern) }
The point here is that option 3) is the only one that allows you to play this way. I normally use option 3) whenever I see a chance to get some work out of the loop. In all other cases there is no real need.
BAT operations are faster for most of the operations, but when formulas
include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
Notice that when you manage to go back to a very tight loop, you got your vectorization opportunities back.
As for parallelism opportunities, I doubt that option 2) would allow any. Parallelism in MonetDB is handled at MAL level. So option 1), if any, would be your best shot for this. Especially if your function can be written in SQL or MAL (these are rare), and they are simple enough to be inlined.
Best regards, Roberto
On Tue, 8 Oct 2019 at 08:06, Daniel Zvinca daniel.zvinca@logbis.com wrote:
Your last reply, Roberto, makes me wonder if it would be an idea to implement a scalar capi as well (for now is bat only). Then for sure the loop will be entirely done in C.
BAT operations are faster for most of the operations, but when formulas include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
On Mon, Oct 7, 2019, 18:39 Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Just to mention, you can also force a tuple-at-the-time evaluation in pure SQL:
-- BEGIN declare cs boolean; set cs=true;
START TRANSACTION;
CREATE OR REPLACE FUNCTION mycontains(s STRING, p STRING, casesensitive boolean) RETURNS BOOLEAN BEGIN IF casesensitive THEN RETURN SELECT s like '%'||p||'%'; ELSE RETURN SELECT s ilike '%'||p||'%'; END IF; END;
CREATE TABLE t(s STRING); INSERT INTO t VALUES ('apple'),('pear'),('banana'),('orange');
explain select * from t where mycontains(s,'an',cs);; -- END
If you look at this explain, you'll see it makes a loop that calls the mycontains() function. This does avoid evaluating both branches. However the explicit interpreted loop won't make it very fast, perhaps see on your data. (Note: when the function is simpler, the loop is actually done in C)
On Mon, 7 Oct 2019 at 16:53, Daniel Zvinca daniel.zvinca@logbis.com wrote:
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a column of values). You would write that in C using the GDK api (
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ),
and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Daniel,
Actually I wasn't considering that you can also keep the casesensitive parameter as scalar (and the pattern as well), so have mycontains(bat *ret, const bat *s, const str pattern, const bit casesensitive). So that optimization I was mentioning would still be there, but simpler:
This is essentially what manifold would provide you with:
loop(string) if (cs) res[i] = strstr(string[i],pattern); else res[i] = strcasestr(string[i],pattern);
This is what you could do with a BAT implementation:
if (cs) loop(string) res[i] = strstr(string[i],pattern); else loop(string) res[i] = strcasestr(string[i],pattern);
Roberto
On Tue, 8 Oct 2019 at 10:07, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Daniel,
I personally would put it the other way around. The scalar version should in principle always be there, as it guarantees that every query works.
Then you look at optimizing performance.
- leave it as is and let it wrap in a MAL loop
- If it is a C function, it will likely be wrapped in a C loop by
manifold. 3) For best performance, you may want to implement the loop yourself with a BAT implementation.
When doing what depends on the nature of the function. If every scalar call requires costly initialisations or allocations, then the first two options can be very slow. In that case you want to choose option 3 and do all initialisations and allocations outside the loop. Your function is essentially a tight loop around a strstr() call, so in principle I don't expect much difference between 2) and 3).
There is a catch though. You have a boolean parameter to control casessensitive. So in case of a BAT implementation, you have 3 columns in input (*also for the possibly constant boolean column*) and your loop looks like (pseudocode):
loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern)
Conditions inside loops are not your best friends. Given that in most use cases your boolean column will be constant (either all true or all false), you can optimize this by doing:
if (cs.sorted && cs.revsorted) /* cs is constant */ { if (cs[0]) { loop(string,pattern,cs) strstr(sring,pattern); } else { loop(string,pattern,cs) strcasestr(string,pattern) } } else { /* cs not constant, check at every tuple */ loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern) }
The point here is that option 3) is the only one that allows you to play this way. I normally use option 3) whenever I see a chance to get some work out of the loop. In all other cases there is no real need.
BAT operations are faster for most of the operations, but when formulas
include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
Notice that when you manage to go back to a very tight loop, you got your vectorization opportunities back.
As for parallelism opportunities, I doubt that option 2) would allow any. Parallelism in MonetDB is handled at MAL level. So option 1), if any, would be your best shot for this. Especially if your function can be written in SQL or MAL (these are rare), and they are simple enough to be inlined.
Best regards, Roberto
On Tue, 8 Oct 2019 at 08:06, Daniel Zvinca daniel.zvinca@logbis.com wrote:
Your last reply, Roberto, makes me wonder if it would be an idea to implement a scalar capi as well (for now is bat only). Then for sure the loop will be entirely done in C.
BAT operations are faster for most of the operations, but when formulas include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
On Mon, Oct 7, 2019, 18:39 Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Just to mention, you can also force a tuple-at-the-time evaluation in pure SQL:
-- BEGIN declare cs boolean; set cs=true;
START TRANSACTION;
CREATE OR REPLACE FUNCTION mycontains(s STRING, p STRING, casesensitive boolean) RETURNS BOOLEAN BEGIN IF casesensitive THEN RETURN SELECT s like '%'||p||'%'; ELSE RETURN SELECT s ilike '%'||p||'%'; END IF; END;
CREATE TABLE t(s STRING); INSERT INTO t VALUES ('apple'),('pear'),('banana'),('orange');
explain select * from t where mycontains(s,'an',cs);; -- END
If you look at this explain, you'll see it makes a loop that calls the mycontains() function. This does avoid evaluating both branches. However the explicit interpreted loop won't make it very fast, perhaps see on your data. (Note: when the function is simpler, the loop is actually done in C)
On Mon, 7 Oct 2019 at 16:53, Daniel Zvinca daniel.zvinca@logbis.com wrote:
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote:
Hi Daniel,
[...]
In this case, you would get better performance by implementing a BAT function (not a function that works on a single value, but on a
column
of values). You would write that in C using the GDK api (
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ),
and evaluate the condition i the loop, per value. However, this requires you to recompile MonetDB to include your new function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
I totally agree with you, Roberto. Every argument you came up with works for simple cases and conditions like the mentioned one.
In a more general context, the reason I was looking for a scalar capi solution is the real life conditions that I face on daily basis for ERP data, especially for large sets. My example "Contains" it is just as one of the many possible "lines" from a complex "where" clause that are in fact a collection of conditions glued together by AND and OR operators. That can lead to possible memory overhead if the default optimizer functionality is used. That happens when many temporary bats are built during computational stage.
A scalar capi solution should not exclude the parallelism IMO (a parallel loop is often possible), and SIMD vectorization is usually broken due checks against NULLs, for instance.
Thank you, Dan
For instance a matrix oriented calculation engine as Eigen
On Tue, Oct 8, 2019 at 12:45 PM Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Daniel,
Actually I wasn't considering that you can also keep the casesensitive parameter as scalar (and the pattern as well), so have mycontains(bat *ret, const bat *s, const str pattern, const bit casesensitive). So that optimization I was mentioning would still be there, but simpler:
This is essentially what manifold would provide you with:
loop(string) if (cs) res[i] = strstr(string[i],pattern); else res[i] = strcasestr(string[i],pattern);
This is what you could do with a BAT implementation:
if (cs) loop(string) res[i] = strstr(string[i],pattern); else loop(string) res[i] = strcasestr(string[i],pattern);
Roberto
On Tue, 8 Oct 2019 at 10:07, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Daniel,
I personally would put it the other way around. The scalar version should in principle always be there, as it guarantees that every query works.
Then you look at optimizing performance.
- leave it as is and let it wrap in a MAL loop
- If it is a C function, it will likely be wrapped in a C loop by
manifold. 3) For best performance, you may want to implement the loop yourself with a BAT implementation.
When doing what depends on the nature of the function. If every scalar call requires costly initialisations or allocations, then the first two options can be very slow. In that case you want to choose option 3 and do all initialisations and allocations outside the loop. Your function is essentially a tight loop around a strstr() call, so in principle I don't expect much difference between 2) and 3).
There is a catch though. You have a boolean parameter to control casessensitive. So in case of a BAT implementation, you have 3 columns in input (*also for the possibly constant boolean column*) and your loop looks like (pseudocode):
loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern)
Conditions inside loops are not your best friends. Given that in most use cases your boolean column will be constant (either all true or all false), you can optimize this by doing:
if (cs.sorted && cs.revsorted) /* cs is constant */ { if (cs[0]) { loop(string,pattern,cs) strstr(sring,pattern); } else { loop(string,pattern,cs) strcasestr(string,pattern) } } else { /* cs not constant, check at every tuple */ loop(string,pattern,cs) if (cs) strstr(sring,pattern); else strcasestr(string,pattern) }
The point here is that option 3) is the only one that allows you to play this way. I normally use option 3) whenever I see a chance to get some work out of the loop. In all other cases there is no real need.
BAT operations are faster for most of the operations, but when formulas
include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
Notice that when you manage to go back to a very tight loop, you got your vectorization opportunities back.
As for parallelism opportunities, I doubt that option 2) would allow any. Parallelism in MonetDB is handled at MAL level. So option 1), if any, would be your best shot for this. Especially if your function can be written in SQL or MAL (these are rare), and they are simple enough to be inlined.
Best regards, Roberto
On Tue, 8 Oct 2019 at 08:06, Daniel Zvinca daniel.zvinca@logbis.com wrote:
Your last reply, Roberto, makes me wonder if it would be an idea to implement a scalar capi as well (for now is bat only). Then for sure the loop will be entirely done in C.
BAT operations are faster for most of the operations, but when formulas include multiple conditional evaluations, the parallelism and vectorization advantage is clearly lost and loop × scalar might be the winner, don't you think?
On Mon, Oct 7, 2019, 18:39 Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:
Just to mention, you can also force a tuple-at-the-time evaluation in pure SQL:
-- BEGIN declare cs boolean; set cs=true;
START TRANSACTION;
CREATE OR REPLACE FUNCTION mycontains(s STRING, p STRING, casesensitive boolean) RETURNS BOOLEAN BEGIN IF casesensitive THEN RETURN SELECT s like '%'||p||'%'; ELSE RETURN SELECT s ilike '%'||p||'%'; END IF; END;
CREATE TABLE t(s STRING); INSERT INTO t VALUES ('apple'),('pear'),('banana'),('orange');
explain select * from t where mycontains(s,'an',cs);; -- END
If you look at this explain, you'll see it makes a loop that calls the mycontains() function. This does avoid evaluating both branches. However the explicit interpreted loop won't make it very fast, perhaps see on your data. (Note: when the function is simpler, the loop is actually done in C)
On Mon, 7 Oct 2019 at 16:53, Daniel Zvinca daniel.zvinca@logbis.com wrote:
I wasn't sure if this question should be posted here or on regular user list, considering the way statements are generated and possible connections with internal functionality.
I understand the explanations. I should have checked the explain feature myself. I think that I expected the optimizer to notice a constant expression and evaluate it before choosing which real columnar operation needs to be performed (obviously only one). Instead all members are evaluated, the whole explain statement is self-explanatory, indeed. (a delayed bat evaluation mechanism would have helped, I guess, for this case)
I use capi intensively, but for string columns the capi memory management can be a serious limitation for large sets.
Anyway, your answer is very helpful for me, it makes me consider adjusting my approach from a straight translation into one SQL statement into using an intermediary language that would build the optimal statement instead of stretching the SQL to get what I need.
Thank you,
On Mon, Oct 7, 2019 at 4:44 PM Sjoerd Mullender sjoerd@monetdb.org wrote:
On 07/10/2019 14.33, Roberto Cornacchia wrote: > Hi Daniel,
[...]
> In this case, you would get better performance by implementing a BAT > function (not a function that works on a single value, but on a column > of values). > You would write that in C using the GDK api > ( https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti... ), > and evaluate the condition i the loop, per value. > However, this requires you to recompile MonetDB to include your new > function.
Recompilation of MonetDB should not be necessary. See [1].
[1] https://www.monetdb.org/hg/MonetDB-extend/
-- Sjoerd Mullender
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
participants (3)
-
Daniel Zvinca -
Roberto Cornacchia -
Sjoerd Mullender