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

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