Re: Counting execution time by using stored procedure

[ahlo ahlo]

Actually, I am testing in both MySQL and SQL Server 2008 Express by using the similar approach in the same machine:



CREATE PROCEDURE SP_Table_Create (@LOOPFROM int, @LOOPTO int)
AS
BEGIN
DECLARE @COUNTLOOP int
DECLARE @sqlstr varchar(200)
DECLARE @StartTime datetime,@EndTime datetime
IF @LOOPFROM <= @LOOPTO
BEGIN
DELETE FROM tmp_time_elapsed
SET @COUNTLOOP = @LOOPFROM
SET @StartTime=GETDATE()
WHILE (@COUNTLOOP <= @LOOPTO)
BEGIN
SET @sqlstr = 'CREATE TABLE TABLE_' + cast(@COUNTLOOP as varchar) + ' (Column1 int)'
EXEC(@sqlstr)
SET @COUNTLOOP = @COUNTLOOP + 1
END
SET @EndTime=GETDATE()
SELECT 'Table ' + CAST(@LOOPFROM AS VARCHAR) + ' to table ' + CAST(@LOOPTO AS VARCHAR) + ' created.'
SELECT DATEDIFF(ms,@StartTime,@EndTime) as 'time_elapsed'
insert into tmp_time_elapsed values (DATEDIFF(ms,@StartTime,@EndTime))
END
END
GO



The result in sql server 2008 express is 640ms,
and the result in Mysql is 13391299ms, the performance is a large difference between them.

Also, I have tried in the free trial of ClearDB in Azure, the result is 9231868ms.

I am testing the performance in different query(create, drop truncate, update select...etc) on different platform. Therefore, I just ask whether I am wrong in the stored procedure.

Besides, the MySQL version in ClearDB is 5.5. I cannot use the curtime(6).

Thanks for all replies.