Hi,

I guess so, because when the procedure is running, I was doing "select count(*) from uniquecatalog" from another mclient, the result is always the same :3654 rows, while in shell, the result is increasing every seconds.

Meng

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
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list