回复： why the same procedure (in shell or in sql) cost so differenttime?

30 Apr 2014


      Hi,
the version is Database: MonetDB v11.18.0, I compile from source Jan2014
Thank, Meng
------------------ 原始邮件 ------------------
发件人: "Martin Kersten";Martin.Kersten@cwi.nl;
发送时间: 2014年4月30日(星期三) 下午5:21
收件人: "users-list"users-list@monetdb.org;
主题: Re: why the same procedure (in shell or in sql) cost so differenttime?
Hi
What are the specs of your MonetDB version and platform?
Beware about the difference between hot and cold query runs.
A TRACE might indicate where the costs are.
regards, Martin
On 30/04/14 10:59, 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
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list