MySQL Forums
Forum List  »  Stored Procedures

Counting execution time by using stored procedure
Posted by: ahlo ahlo
Date: January 12, 2015 08:05AM

I am a student, and I am focusing on the processing time on a query command.
I use MySQL 5.5, not 5.6.
I have a stored procedure in below:

/*===========================================*/
create procedure SP_Table_Create(LOOPFROM INT(5) UNSIGNED, LOOPTO INT(5) UNSIGNED)
BEGIN
DECLARE COUNTLOOP INT(5) UNSIGNED;
DECLARE sqlstr text;
DECLARE start_time BIGINT;
DECLARE end_time BIGINT;
IF (LOOPFROM <= LOOPTO) THEN
DELETE FROM tmp_time_elapsed;
SET COUNTLOOP=LOOPFROM;
SET start_time = conv(concat(substring(uuid(),16,3),substring(uuid(),10,4),substring(uuid(),1,8)),16,10) div 10 - (141427 * 24 * 60 * 60 * 1000000);
REPEAT
set sqlstr = concat('create table TABLE_',COUNTLOOP,' (`Column1` int(5))');
SET @sqlstr = sqlstr;
prepare stmt from @sqlstr;
execute stmt;
deallocate prepare stmt;

SET COUNTLOOP = COUNTLOOP + 1;
UNTIL COUNTLOOP > LOOPTO
END REPEAT;
SET end_time = conv(concat(substring(uuid(),16,3),substring(uuid(),10,4),substring(uuid(),1,8)),16,10) div 10 - (141427 * 24 * 60 * 60 * 1000000);
select concat("Table ",LOOPFROM," to table ",LOOPTO," created.");
insert into tmp_time_elapsed values (end_time - start_time);
END IF;
END
/*===========================================*/

I set commands on the profiling:
set profiling=1;
SET @@profiling_history_size = 100;

When I run the stored procedure:
CALL SP_Table_Create('1','100');

The whole stored procedure took 8891998ms to run (the value that insert into tmp_time_elapsed). Also, when I use the command:
show profiles;

Most of the time duration would taken on the 'execute stmt' (each of them is about 0.05ms).
Is the duration of 'execute stmt' is normal? What I can do to lower the execution time?

Thanks a lot.

Options: ReplyQuote


Subject
Views
Written By
Posted
Counting execution time by using stored procedure
9696
January 12, 2015 08:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.