[MonetDB-users] very slow query execution
hello, this is how my table - table1(int,int,varchar) - looks like: recid | ft | val --------------------------------------- 1001 | 301 | a 1001 | 302 | b 1001 | 303 | c 1002 | 301 | d 1002 | 302 | e 1003 | 303 | f 1003 | 301 | g 1003 | 302 | h 1004 | 303 | i in reality this table has a few millions of records. for the following query: select t1.val as val1 , t2.val as val2 from table1 t1 left join table1 t2 on t1.recid=t2.recid and t2.ft=303 where t1.recid=1002 and t1.ft=301 i get the expected result: val1 | val2 --------------------- d | f the execution time of this query is very slow: ~1.5 seconds - in comparison to MSSql:0.016 seconds. the kind of a query that i really need in my production application doesn't have only one such a "left join...", but may have even 10. with 5 such "left join..."s the query execution time is ~25 seconds, which is so far slower than MSSql: 0.156 more info: 1. i run the queries from SQuirreL SQL Client. the MonetDB5 is installed\run on the same machine. the MSSql server was running on another machine in the same LAN. 2. the primary key of table1 is : recid,ft,val 3. at the beginning i had another problem, that i have got an empty result when running a simple query like this: select * from table1 t1 where t1.recid=1002 and t1.ft=301 i'm not sure about this, but i guess that this problem had been solved by creating a view such as: create view v1 as select recid,ft,val from table1 i have no clue whether these two problems related to each other, but i mentioned it since it might give you a hint for solving the other problem. thanks in advanced for any help, ewilde.
On Tue, Apr 22, 2008 at 08:18:34AM +0200, ewilde wrote:
hello,
this is how my table - table1(int,int,varchar) - looks like:
recid | ft | val --------------------------------------- 1001 | 301 | a 1001 | 302 | b 1001 | 303 | c 1002 | 301 | d 1002 | 302 | e 1003 | 303 | f 1003 | 301 | g 1003 | 302 | h 1004 | 303 | i
in reality this table has a few millions of records.
for the following query: select t1.val as val1 , t2.val as val2 from table1 t1 left join table1 t2 on t1.recid=t2.recid and t2.ft=303 where t1.recid=1002 and t1.ft=301
i get the expected result: val1 | val2 --------------------- d | f
the execution time of this query is very slow: ~1.5 seconds - in comparison to MSSql:0.016 seconds. the kind of a query that i really need in my production application doesn't have only one such a "left join...", but may have even 10. with 5 such "left join..."s the query execution time is ~25 seconds, which is so far slower than MSSql: 0.156
more info: 1. i run the queries from SQuirreL SQL Client. the MonetDB5 is installed\run on the same machine. the MSSql server was running on another machine in the same LAN. 2. the primary key of table1 is : recid,ft,val
3. at the beginning i had another problem, that i have got an empty result when running a simple query like this:
select * from table1 t1 where t1.recid=1002 and t1.ft=301
Given your example data this should return a result, ie its a bug if this happens. Please file it at the monetdb sourceforge bug reporting system.
i'm not sure about this, but i guess that this problem had been solved by creating a view such as:
create view v1 as select recid,ft,val from table1
i have no clue whether these two problems related to each other, but i mentioned it since it might give you a hint for solving the other problem.
The performance difference between mssql and MonetDB in this case is because monetdb will to a hash based solution and msssql will use its btree. To solve your performance problem it maybe helpfull to order your data. INSERT INTO tables_ordered select * from table1 order by recid, ft, val; Niels
thanks in advanced for any help, ewilde.
------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javao... _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
participants (2)
-
ewilde
-
Niels Nes