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.