Re: [MonetDB-users] Problems with huge SQL queries.

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