Hi, I'm not an expert. But I would suspect transaction processing could cause it. The big procedure is running in one transaction everything, the shell script commits every execution of a procedure. Radovan On 04/30/2014 10:59 AM, integrity wrote:
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 _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- __________________________ Radovan Bičiště ceos data s.r.o. Pouchovská 153 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste