[MonetDB-users] Problems with huge SQL queries.

Manuel Mayr

5 Aug 2010 5 Aug '10

1:20 p.m.

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

Show replies by date

Fabian Groffen

5 Aug 5 Aug

1:48 p.m.

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? http://bugs.monetdb.org/show_bug.cgi?id=2595

Stefan de Konink

2:05 p.m.

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?

http://bugs.monetdb.org/show_bug.cgi?id=2595

'You are not authorized to access bug #2595.' Stefan

Stefan Manegold

2:08 p.m.

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?

http://bugs.monetdb.org/show_bug.cgi?id=2595

Jan Rittinger

2:38 p.m.

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?

http://bugs.monetdb.org/show_bug.cgi?id=2595

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

-- 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

Fabian Groffen

3:24 p.m.

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?

http://bugs.monetdb.org/show_bug.cgi?id=2595

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.

Stefan de Konink

2:06 p.m.

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

Jan Rittinger

2:41 p.m.

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

...

cat test1000.sql

plan SELECT * FROM tables WHERE (tables.id IN (1,2,...,500)); 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

Jan Rittinger

2:51 p.m.

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

Niels Nes

4:43 p.m.

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

-- 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

Jan Rittinger

7:26 p.m.

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 -- 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

Martin Kersten

5:39 p.m.

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 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

Jan Rittinger

7:23 p.m.

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 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

Stefan Manegold

7:50 p.m.

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 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

Jan Rittinger

8:52 p.m.

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 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

Niels Nes

6 Aug 6 Aug

6:58 a.m.

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 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

Manuel Mayr

10 Aug 10 Aug

1:09 p.m.

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 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

Niels Nes

1:38 p.m.

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 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

Manuel Mayr

2:26 p.m.

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 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

5325

Age (days ago)

5330

Last active (days ago)

List overview

Download

18 comments

7 participants

participants (7)

Fabian Groffen
Jan Rittinger
Manuel Mayr
Martin Kersten
Niels Nes
Stefan de Konink
Stefan Manegold