users-list April 2014

users-list@monetdb.org

25 participants
21 discussions

why the same procedure (in shell or in sql) cost so different time?

by integrity

Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng

8 years, 2 months

procedure to follow when changing the sql-optimizer settings

by Mahmut Uludag

Hi, We were getting database crashes when we were using our database with read/write mode and using the default sql_optimizer settings. We decided to run our database in read-only mode and also has changed the sql_optimizer setting to sequential_pipe. With new settings we don't see any crashes but have started to see error messages in the log files similar to the following lines. 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_schema schema1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_table schema1.table1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_column table1.col1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback done 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit done My question is there a procedure to follow when we change sql-optimizer settings? I assume MonetDB has some history of the past and because of the change of the sql_optimizer settings the bits in the history causing these error messages but i would not be surprised if this is completely wrong. Mahmut Uludag

8 years, 3 months

concurrency and use of temporary tables

by Niall Rooney

Hello, I am running a java program which connects to a MonetDB database and runs a thread where each thread creates a number of local temporary tables using the and populates these tables in certain data processing steps. On termination the temporary tables are deleted. These temporary tables are utilised in a number of select statements in combination with the tables in the database. Tables are created using a table name where a UUID is generated for each thread so the use of local tables is unique to the thread e.g. create local temporary table tempquerywords0e5a0e08c1d24cf6b9529426463b6191(word_word varchar(255)) ON COMMIT PRESERVE ROWS. All needed temporary tables are created by the thread as an initial process. The connection to the database is set with autoCommit set to true. If each thread is run sequentially , there is no problem. However if I run the process concurrently with a maximum of 2 concurrent threads, some threads terminate early with an Exception message e.g. nested exception is java.sql.SQLException: SELECT: no such table 'tempweightedclustersa7f7f47e7c184175ac8ab5ee2dc76a03'. This message is confusing as the table is populated ok before the select. Although other participants have posted messages concerning problems based on concurrent updates and temporary tables , is there an issue concerning concurrent reads and temporary tables? Best Regards Niall

8 years, 3 months

TPC-H in MALs

by Qing Guo

Hi, I was trying to run “mserver” (standalone) in a simulator to do some performance study. As mserver only reads MALs, are there any ready-to-use MAL scripts for TPC-H? I tried to use “explain” in “mclient”, but the translated MALs seem to be too detailed and cannot simply copy and run. Any idea on this? Thank you! -Kevin

8 years, 3 months

Build for Ubuntu 14.04 "Trusty Tahr"?

by Robert Kosara

Hi, I see Ubuntu 14.04 (which was just officially released a few days ago) listed on the Ubuntu/Debian packages list: http://dev.monetdb.org/downloads/deb/ However, the “trusty” directory doesn’t exist, and I can’t install MonetDB through apt-get. Will that build be enabled in the near future? Thanks, Robert

8 years, 3 months

Overflow calculation in tpch Q1

by Tsai Li Ming

Hi, I’m getting an overflow calculation when SF=100 for Q1. The rest of the queries are fine. There’s an old thread on this from Sep 2013: https://www.monetdb.org/pipermail/users-list/2013-September/006817.html I’m using 11.17.13. Will this be fixed soon? Thanks!

8 years, 3 months

Blob and Clob not implement in JDBC v 2.10?

by Phat Loc

Hello, I am not able to get Blob and Clob to work in JDBC v 2.10? I get a SQLFeatureNotSupportedException? Is this the correct behavior? Thanks, Phat

8 years, 3 months

fastest way of having a number of rows of a table in monetdb ?

by Leon-Constantin Fopa

Helo everybody, To have the exact number of rows of a table t I actually run the following query SELECT COUNT(*) AS n FROM t; Is there a faster way to do this ? Best regards, Fopa

8 years, 3 months

How to have the list of all tables names of a monetdb database ?

by Leon-Constantin Fopa

Hi everyone In postgresql one can have all tables names by running the following query SELECT table_name FROM information_schema.tables WHERE table_schema='public'; Is there something similar in monetdb to have the list of tables ? Best regards, Fopa

8 years, 3 months

UPDATE fails with BATproject: does not match always

by Radovan Bičiště

Hello, I have a problem with following update and suspect data corruption. UPDATE crmkarta SET rodne_cislo = (SELECT MAX(rodne_cislo) FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL) WHERE cislo_karty IS NOT NULL --AND aktivita > 0 AND EXISTS (SELECT verka_klient.cislo_karty FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL); Can not create object [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Would dump and restore of tables help ? We are on version Jan2014-SP1. Thank you, Radovan

8 years, 3 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list April 2014