[MonetDB-users] Problems with huge SQL queries.
Hello everyone,
I'm currently using Monet5 for some experiments with ActiveRecord but some SQL queries seem to be too big for the default configuration of monet. The prominent characteristic of the query is a huge IN part in the WHERE clause (nearly 13Kb).
$ mclient -d tpch01 -h localhost < q1.sql MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = SELECT states.* FROM states WHERE (states.country_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141 ERROR = !Connection terminated
Is there a parameter that allows me to increase the SQL query buffer?
Beste regards, Manuel
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner? -- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen
On 05-08-2010 15:20:07 +0200, Manuel Mayr wrote:
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner?
Could this be bug #2595 by chance?
On Thu, 5 Aug 2010, Fabian Groffen wrote:
On 05-08-2010 15:20:07 +0200, Manuel Mayr wrote:
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner?
Could this be bug #2595 by chance?
'You are not authorized to access bug #2595.'
Stefan
On Thu, Aug 05, 2010 at 03:48:35PM +0200, Fabian Groffen wrote:
On 05-08-2010 15:20:07 +0200, Manuel Mayr wrote:
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner?
Could this be bug #2595 by chance?
and/or
http://bugs.monetdb.org/show_bug.cgi?id=104
?
Stefan
On Aug 5, 2010, at 15:48, Fabian Groffen wrote:
On 05-08-2010 15:20:07 +0200, Manuel Mayr wrote:
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner?
Could this be bug #2595 by chance?
You are not authorized to access bug #2595.
Hmmm...
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On 05-08-2010 16:38:12 +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 15:48, Fabian Groffen wrote:
On 05-08-2010 15:20:07 +0200, Manuel Mayr wrote:
PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner?
Could this be bug #2595 by chance?
You are not authorized to access bug #2595.
Hmm, yeah, sorry. We cannot open up that one. I can post the relevant bits though:
The details are in bug #2595. Using sql_debug=65536 we can now (temporarily) disable the expensive or-style optimisation of selection push-downs.
http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=897717064f69
The query in question actually uses a huge number of args in an IN query (... IN (x1, x2, ..., xlarge)), which looks like yours.
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Stefan
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Thu, Aug 05, 2010 at 04:51:59PM +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
If activeRecords could generate the temp table this should indeed help code generation speed. Still the bug with large in values lists should be fixed too.
Niels
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Aug 5, 2010, at 18:43, Niels Nes wrote:
If activeRecords could generate the temp table this should indeed help code generation speed. Still the bug with large in values lists should be fixed too.
The queries are generated by ActiveRecord and thus are not under our control...
Jan
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Aug 5, 2010, at 21:50, Stefan Manegold wrote:
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
The time is really spent during planning
plan (select with 1000 IN items): 28 sec explain (select with 1000 IN items): 30 sec (select with 1000 IN items): 30 sec
So in my eyes there is no need to analyze the MAL plan, but to improve the compilation of the SQL-algebra.
For now the optimizers are not involved in the query. Setting '--set sql_debug=65536' doesn't change the execution time—it still takes 30 sec for 1000 IN items (see above).
Jan
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
On Thu, 5 Aug 2010, Manuel Mayr wrote:
> Is there a parameter that allows me to increase the SQL query buffer?
I did see these issues before with large IN stuff, but lately I don't have them anymore. So you might report the bug as well. Does the issue also happen if you create a new table, with all items and do a IN (select ids FROM newtable); ?
Hmmm, I should be more informative about what I want to say...
cat test500.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
cat test1000.sql
plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
time mclient test500.sql > /dev/null
real 0m4.062s user 0m0.031s sys 0m0.006s
time mclient test1000.sql > /dev/null
real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
Stefan
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Thu, Aug 05, 2010 at 10:52:18PM +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 21:50, Stefan Manegold wrote:
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
The time is really spent during planning
plan (select with 1000 IN items): 28 sec explain (select with 1000 IN items): 30 sec (select with 1000 IN items): 30 sec
So in my eyes there is no need to analyze the MAL plan, but to improve the compilation of the SQL-algebra.
For now the optimizers are not involved in the query. Setting '--set sql_debug=65536' doesn't change the execution time—it still takes 30 sec for 1000 IN items (see above).
Jan
Stefan, Jan
Jan is correct, the optimization of IN-lists is very in efficient (rewrites into a binary tree of IN or expressions, which only during algebra 2 bin-algebra get optimized into a list (ie bat).
Niels
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
On Aug 5, 2010, at 16:06, Stefan de Konink wrote:
> On Thu, 5 Aug 2010, Manuel Mayr wrote: > >> Is there a parameter that allows me to increase the SQL query buffer? > > I did see these issues before with large IN stuff, but lately I don't have > them anymore. So you might report the bug as well. Does the issue also > happen if you create a new table, with all items and do a IN (select ids > FROM newtable); ? >
Hmmm, I should be more informative about what I want to say...
> cat test500.sql plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500));
> cat test1000.sql plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000));
> time mclient test500.sql > /dev/null real 0m4.062s user 0m0.031s sys 0m0.006s
> time mclient test1000.sql > /dev/null real 0m34.939s user 0m0.117s sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
> > Stefan > > ------------------------------------------------------------------------------ > The Palm PDK Hot Apps Program offers developers who use the > Plug-In Development Kit to bring their C/C++ apps to Palm for a share > of $1 Million in cash or HP Products. Visit us here for more details: > http://p.sf.net/sfu/dev2dev-palm > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi all,
first of all, thanks for your suggestions. I have another query that fits into this "huge SQL query thread" and breaks on M5.
The error message I get from mclient is the following, which doesn't give me a hint what is going wrong:
MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = WITH ERROR = !Connection terminated
It runs well on DB2, so I assume it to be correct. I'm also able to run it partwise on M5 (till >>>>>> in the SQL query ) ... which makes me believe that it's simply to huge. I also tried to remove unnecessary characters like avoiding the columns parts of the CTE definitions, but still no chance.
Thanks in advance, Manuel
SQL query
WITH yv(id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at, li.id, o.id FROM Orders o, Line_Items li, Variants v WHERE o.id = li.order_id AND li.variant_id = v.id AND o.user_id = 20),
Cheapest_Price(product_id, price) AS (SELECT product_id, MIN(price) FROM Variants v WHERE v.product_id IN (SELECT product_id FROM yv) GROUP BY product_id),
Cheapest_Variants(id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at FROM Variants v, Cheapest_Price cp WHERE v.price = cp.price AND v.product_id = cp.product_id),
Suggestions_(rid, id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT ROW_NUMBER() OVER (PARTITION BY cv.id) AS rid, cv.id, cv.product_id, cv.sku, cv.price, cv.weight, cv.height, cv.width, cv.depth, cv.deleted_at FROM Cheapest_Variants cv),
-- >>>>>>
Suggestions(sugg_id, sugg_product_id, sugg_sku, sugg_price, sugg_weight, sugg_height, sugg_width, sugg_depth, sugg_deleted_at, id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT cv.id as sugg_id, cv.product_id as sugg_product_id, cv.sku as sugg_sku, cv.price as sugg_price, cv.weight as sugg_weight, cv.height as sugg_height, cv.width as sugg_width, cv.depth as sugg_depth, cv.deleted_at as sugg_deleted_at, yv.id, yv.product_id, yv.sku, yv.price, yv.weight, yv.height, yv.width, yv.depth, yv.deleted_at, yv.line_item_id, yv.order_id FROM Suggestions_ cv, yv yv WHERE cv.product_id = yv.product_id AND rid = 1),
-- return only one variant per suggestions
Savings(order_id, amount) AS (SELECT order_id, SUM(s.price - s.sugg_price) AS amount FROM Suggestions s GROUP BY order_id)
SELECT sugg.*, sav.amount FROM suggestions sugg, savings sav WHERE sugg.order_id = sav.order_id;
-- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen
On Aug 6, 2010, at 8:58 AM, Niels Nes wrote:
On Thu, Aug 05, 2010 at 10:52:18PM +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 21:50, Stefan Manegold wrote:
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
The time is really spent during planning
plan (select with 1000 IN items): 28 sec explain (select with 1000 IN items): 30 sec (select with 1000 IN items): 30 sec
So in my eyes there is no need to analyze the MAL plan, but to improve the compilation of the SQL-algebra.
For now the optimizers are not involved in the query. Setting '--set sql_debug=65536' doesn't change the execution time—it still takes 30 sec for 1000 IN items (see above).
Jan
Stefan, Jan
Jan is correct, the optimization of IN-lists is very in efficient (rewrites into a binary tree of IN or expressions, which only during algebra 2 bin-algebra get optimized into a list (ie bat).
Niels
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
On Aug 5, 2010, at 16:41, Jan Rittinger wrote:
> > On Aug 5, 2010, at 16:06, Stefan de Konink wrote: > >> On Thu, 5 Aug 2010, Manuel Mayr wrote: >> >>> Is there a parameter that allows me to increase the SQL query buffer? >> >> I did see these issues before with large IN stuff, but lately I don't have >> them anymore. So you might report the bug as well. Does the issue also >> happen if you create a new table, with all items and do a IN (select ids >> FROM newtable); ? >> >
Hmmm, I should be more informative about what I want to say...
> >> cat test500.sql > plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500)); > >> cat test1000.sql > plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000)); > >> time mclient test500.sql > /dev/null > real 0m4.062s > user 0m0.031s > sys 0m0.006s > >> time mclient test1000.sql > /dev/null > real 0m34.939s > user 0m0.117s > sys 0m0.008s
This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items...
To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation?
Jan
> >> >> Stefan >> >> ------------------------------------------------------------------------------ >> The Palm PDK Hot Apps Program offers developers who use the >> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >> of $1 Million in cash or HP Products. Visit us here for more details: >> http://p.sf.net/sfu/dev2dev-palm >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > > -- > Jan Rittinger > Lehrstuhl Datenbanken und Informationssysteme > Wilhelm-Schickard-Institut für Informatik > Eberhard-Karls-Universität Tübingen > > http://www-db.informatik.uni-tuebingen.de/team/rittinger > > > ------------------------------------------------------------------------------ > The Palm PDK Hot Apps Program offers developers who use the > Plug-In Development Kit to bring their C/C++ apps to Palm for a share > of $1 Million in cash or HP Products. Visit us here for more details: > http://p.sf.net/sfu/dev2dev-palm > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Tue, Aug 10, 2010 at 03:09:10PM +0200, Manuel Mayr wrote:
Hi all,
first of all, thanks for your suggestions. I have another query that fits into this "huge SQL query thread" and breaks on M5.
The error message I get from mclient is the following, which doesn't give me a hint what is going wrong:
MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = WITH ERROR = !Connection terminated
It runs well on DB2, so I assume it to be correct. I'm also able to run it partwise on M5 (till >>>>>> in the SQL query ) ... which makes me believe that it's simply to huge. I also tried to remove unnecessary characters like avoiding the columns parts of the CTE definitions, but still no chance.
Thanks in advance, Manuel
Manuel
Could you file this on bugzilla? We will need full schema information to rerun the query and debug this. The server seems to crash, but its a different problem then your earlier big query one.
Niels
SQL query
WITH yv(id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at, li.id, o.id FROM Orders o, Line_Items li, Variants v WHERE o.id = li.order_id AND li.variant_id = v.id AND o.user_id = 20),
Cheapest_Price(product_id, price) AS (SELECT product_id, MIN(price) FROM Variants v WHERE v.product_id IN (SELECT product_id FROM yv) GROUP BY product_id),
Cheapest_Variants(id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at FROM Variants v, Cheapest_Price cp WHERE v.price = cp.price AND v.product_id = cp.product_id),
Suggestions_(rid, id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT ROW_NUMBER() OVER (PARTITION BY cv.id) AS rid, cv.id, cv.product_id, cv.sku, cv.price, cv.weight, cv.height, cv.width, cv.depth, cv.deleted_at FROM Cheapest_Variants cv),
-- >>>>>>
Suggestions(sugg_id, sugg_product_id, sugg_sku, sugg_price, sugg_weight, sugg_height, sugg_width, sugg_depth, sugg_deleted_at, id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT cv.id as sugg_id, cv.product_id as sugg_product_id, cv.sku as sugg_sku, cv.price as sugg_price, cv.weight as sugg_weight, cv.height as sugg_height, cv.width as sugg_width, cv.depth as sugg_depth, cv.deleted_at as sugg_deleted_at, yv.id, yv.product_id, yv.sku, yv.price, yv.weight, yv.height, yv.width, yv.depth, yv.deleted_at, yv.line_item_id, yv.order_id FROM Suggestions_ cv, yv yv WHERE cv.product_id = yv.product_id AND rid = 1),
-- return only one variant per suggestions
Savings(order_id, amount) AS (SELECT order_id, SUM(s.price - s.sugg_price) AS amount FROM Suggestions s GROUP BY order_id)
SELECT sugg.*, sav.amount FROM suggestions sugg, savings sav WHERE sugg.order_id = sav.order_id;
-- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen
On Aug 6, 2010, at 8:58 AM, Niels Nes wrote:
On Thu, Aug 05, 2010 at 10:52:18PM +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 21:50, Stefan Manegold wrote:
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
The time is really spent during planning
plan (select with 1000 IN items): 28 sec explain (select with 1000 IN items): 30 sec (select with 1000 IN items): 30 sec
So in my eyes there is no need to analyze the MAL plan, but to improve the compilation of the SQL-algebra.
For now the optimizers are not involved in the query. Setting '--set sql_debug=65536' doesn't change the execution time—it still takes 30 sec for 1000 IN items (see above).
Jan
Stefan, Jan
Jan is correct, the optimization of IN-lists is very in efficient (rewrites into a binary tree of IN or expressions, which only during algebra 2 bin-algebra get optimized into a list (ie bat).
Niels
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
most likely it ia one of optimizers. Use debug select ..... and the look at the optimizers cost break down in the query plan, thus type
next optimizers
see doc on line
On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote:
> > On Aug 5, 2010, at 16:41, Jan Rittinger wrote: > >> >> On Aug 5, 2010, at 16:06, Stefan de Konink wrote: >> >>> On Thu, 5 Aug 2010, Manuel Mayr wrote: >>> >>>> Is there a parameter that allows me to increase the SQL query buffer? >>> >>> I did see these issues before with large IN stuff, but lately I don't have >>> them anymore. So you might report the bug as well. Does the issue also >>> happen if you create a new table, with all items and do a IN (select ids >>> FROM newtable); ? >>> >> > > Hmmm, I should be more informative about what I want to say... > >> >>> cat test500.sql >> plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500)); >> >>> cat test1000.sql >> plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000)); >> >>> time mclient test500.sql > /dev/null >> real 0m4.062s >> user 0m0.031s >> sys 0m0.006s >> >>> time mclient test1000.sql > /dev/null >> real 0m34.939s >> user 0m0.117s >> sys 0m0.008s > > This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items... > > To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation? > > Jan > >> >>> >>> Stefan >>> >>> ------------------------------------------------------------------------------ >>> The Palm PDK Hot Apps Program offers developers who use the >>> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >>> of $1 Million in cash or HP Products. Visit us here for more details: >>> http://p.sf.net/sfu/dev2dev-palm >>> _______________________________________________ >>> MonetDB-users mailing list >>> MonetDB-users@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> -- >> Jan Rittinger >> Lehrstuhl Datenbanken und Informationssysteme >> Wilhelm-Schickard-Institut für Informatik >> Eberhard-Karls-Universität Tübingen >> >> http://www-db.informatik.uni-tuebingen.de/team/rittinger >> >> >> ------------------------------------------------------------------------------ >> The Palm PDK Hot Apps Program offers developers who use the >> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >> of $1 Million in cash or HP Products. Visit us here for more details: >> http://p.sf.net/sfu/dev2dev-palm >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > > -- > Jan Rittinger > Lehrstuhl Datenbanken und Informationssysteme > Wilhelm-Schickard-Institut für Informatik > Eberhard-Karls-Universität Tübingen > > http://www-db.informatik.uni-tuebingen.de/team/rittinger > > > ------------------------------------------------------------------------------ > The Palm PDK Hot Apps Program offers developers who use the > Plug-In Development Kit to bring their C/C++ apps to Palm for a share > of $1 Million in cash or HP Products. Visit us here for more details: > http://p.sf.net/sfu/dev2dev-palm > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users
The Palm PDK Hot Apps Program offers developers who use the Plug-In Development Kit to bring their C/C++ apps to Palm for a share of $1 Million in cash or HP Products. Visit us here for more details: http://p.sf.net/sfu/dev2dev-palm _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi Niels,
I filed the problem on bugzilla.
Thanks, Manuel -- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen
On Aug 10, 2010, at 3:38 PM, Niels Nes wrote:
On Tue, Aug 10, 2010 at 03:09:10PM +0200, Manuel Mayr wrote:
Hi all,
first of all, thanks for your suggestions. I have another query that fits into this "huge SQL query thread" and breaks on M5.
The error message I get from mclient is the following, which doesn't give me a hint what is going wrong:
MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = WITH ERROR = !Connection terminated
It runs well on DB2, so I assume it to be correct. I'm also able to run it partwise on M5 (till >>>>>> in the SQL query ) ... which makes me believe that it's simply to huge. I also tried to remove unnecessary characters like avoiding the columns parts of the CTE definitions, but still no chance.
Thanks in advance, Manuel
Manuel
Could you file this on bugzilla? We will need full schema information to rerun the query and debug this. The server seems to crash, but its a different problem then your earlier big query one.
Niels
SQL query
WITH yv(id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at, li.id, o.id FROM Orders o, Line_Items li, Variants v WHERE o.id = li.order_id AND li.variant_id = v.id AND o.user_id = 20),
Cheapest_Price(product_id, price) AS (SELECT product_id, MIN(price) FROM Variants v WHERE v.product_id IN (SELECT product_id FROM yv) GROUP BY product_id),
Cheapest_Variants(id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT v.id, v.product_id, v.sku, v.price, v.weight, v.height, v.width, v.depth, v.deleted_at FROM Variants v, Cheapest_Price cp WHERE v.price = cp.price AND v.product_id = cp.product_id),
Suggestions_(rid, id, product_id, sku, price, weight, height, width, depth, deleted_at) AS (SELECT ROW_NUMBER() OVER (PARTITION BY cv.id) AS rid, cv.id, cv.product_id, cv.sku, cv.price, cv.weight, cv.height, cv.width, cv.depth, cv.deleted_at FROM Cheapest_Variants cv),
-- >>>>>>
Suggestions(sugg_id, sugg_product_id, sugg_sku, sugg_price, sugg_weight, sugg_height, sugg_width, sugg_depth, sugg_deleted_at, id, product_id, sku, price, weight, height, width, depth, deleted_at, line_item_id, order_id) AS (SELECT cv.id as sugg_id, cv.product_id as sugg_product_id, cv.sku as sugg_sku, cv.price as sugg_price, cv.weight as sugg_weight, cv.height as sugg_height, cv.width as sugg_width, cv.depth as sugg_depth, cv.deleted_at as sugg_deleted_at, yv.id, yv.product_id, yv.sku, yv.price, yv.weight, yv.height, yv.width, yv.depth, yv.deleted_at, yv.line_item_id, yv.order_id FROM Suggestions_ cv, yv yv WHERE cv.product_id = yv.product_id AND rid = 1),
-- return only one variant per suggestions
Savings(order_id, amount) AS (SELECT order_id, SUM(s.price - s.sugg_price) AS amount FROM Suggestions s GROUP BY order_id)
SELECT sugg.*, sav.amount FROM suggestions sugg, savings sav WHERE sugg.order_id = sav.order_id;
-- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen
On Aug 6, 2010, at 8:58 AM, Niels Nes wrote:
On Thu, Aug 05, 2010 at 10:52:18PM +0200, Jan Rittinger wrote:
On Aug 5, 2010, at 21:50, Stefan Manegold wrote:
On Thu, Aug 05, 2010 at 09:23:34PM +0200, Jan Rittinger wrote:
As far as I understood 'plan <sql>' does not go the whole way to mal, but prints the SQL-algebra plan and then stops.
PLAN show the relation plan. EXPLAIN shows the MAL plan.
'debug <sql>' does not work in a script ('0 tuples') and seems to be to big for the interactive session.
DEBUG is interactive and hence indeed not suitbale for scripts.
The time is really spent during planning
plan (select with 1000 IN items): 28 sec explain (select with 1000 IN items): 30 sec (select with 1000 IN items): 30 sec
So in my eyes there is no need to analyze the MAL plan, but to improve the compilation of the SQL-algebra.
For now the optimizers are not involved in the query. Setting '--set sql_debug=65536' doesn't change the execution time—it still takes 30 sec for 1000 IN items (see above).
Jan
Stefan, Jan
Jan is correct, the optimization of IN-lists is very in efficient (rewrites into a binary tree of IN or expressions, which only during algebra 2 bin-algebra get optimized into a list (ie bat).
Niels
Stefan
On Aug 5, 2010, at 19:39, Martin Kersten wrote:
> most likely it ia one of optimizers. Use > debug select ..... > and the look at the optimizers cost break down in the query plan, thus type > > next > optimizers > > see doc on line > > > > On 5 aug. 2010, at 07:51, Jan Rittinger jan.rittinger@uni-tuebingen.de wrote: > >> >> On Aug 5, 2010, at 16:41, Jan Rittinger wrote: >> >>> >>> On Aug 5, 2010, at 16:06, Stefan de Konink wrote: >>> >>>> On Thu, 5 Aug 2010, Manuel Mayr wrote: >>>> >>>>> Is there a parameter that allows me to increase the SQL query buffer? >>>> >>>> I did see these issues before with large IN stuff, but lately I don't have >>>> them anymore. So you might report the bug as well. Does the issue also >>>> happen if you create a new table, with all items and do a IN (select ids >>>> FROM newtable); ? >>>> >>> >> >> Hmmm, I should be more informative about what I want to say... >> >>> >>>> cat test500.sql >>> plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500)); >>> >>>> cat test1000.sql >>> plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,1000)); >>> >>>> time mclient test500.sql > /dev/null >>> real 0m4.062s >>> user 0m0.031s >>> sys 0m0.006s >>> >>>> time mclient test1000.sql > /dev/null >>> real 0m34.939s >>> user 0m0.117s >>> sys 0m0.008s >> >> This example shows that the compilation of long IN lists screws up after some hundred of items. Manuel tried to run a query with some thousand items... >> >> To cope with queries that feature such long lists (e.g., stemming from generated ActiveRecord queries) other database turn the values in an IN clause into a temporary table. Might that be an option to speed up the code generation? >> >> Jan >> >>> >>>> >>>> Stefan >>>> >>>> ------------------------------------------------------------------------------ >>>> The Palm PDK Hot Apps Program offers developers who use the >>>> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >>>> of $1 Million in cash or HP Products. Visit us here for more details: >>>> http://p.sf.net/sfu/dev2dev-palm >>>> _______________________________________________ >>>> MonetDB-users mailing list >>>> MonetDB-users@lists.sourceforge.net >>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>> >>> -- >>> Jan Rittinger >>> Lehrstuhl Datenbanken und Informationssysteme >>> Wilhelm-Schickard-Institut für Informatik >>> Eberhard-Karls-Universität Tübingen >>> >>> http://www-db.informatik.uni-tuebingen.de/team/rittinger >>> >>> >>> ------------------------------------------------------------------------------ >>> The Palm PDK Hot Apps Program offers developers who use the >>> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >>> of $1 Million in cash or HP Products. Visit us here for more details: >>> http://p.sf.net/sfu/dev2dev-palm >>> _______________________________________________ >>> MonetDB-users mailing list >>> MonetDB-users@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> -- >> Jan Rittinger >> Lehrstuhl Datenbanken und Informationssysteme >> Wilhelm-Schickard-Institut für Informatik >> Eberhard-Karls-Universität Tübingen >> >> http://www-db.informatik.uni-tuebingen.de/team/rittinger >> >> >> ------------------------------------------------------------------------------ >> The Palm PDK Hot Apps Program offers developers who use the >> Plug-In Development Kit to bring their C/C++ apps to Palm for a share >> of $1 Million in cash or HP Products. Visit us here for more details: >> http://p.sf.net/sfu/dev2dev-palm >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > > ------------------------------------------------------------------------------ > The Palm PDK Hot Apps Program offers developers who use the > Plug-In Development Kit to bring their C/C++ apps to Palm for a share > of $1 Million in cash or HP Products. Visit us here for more details: > http://p.sf.net/sfu/dev2dev-palm > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen
http://www-db.informatik.uni-tuebingen.de/team/rittinger
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
This SF.net email is sponsored by
Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (7)
-
Fabian Groffen -
Jan Rittinger -
Manuel Mayr -
Martin Kersten -
Niels Nes -
Stefan de Konink -
Stefan Manegold