MySQL vs MSSQL INSERTs speed test-CodePudding

Trying to see if there is any way I can improve the INSERT speed in MySQL. I am running on Windows 10. This is the result I got after doing the exact same INSERTs 10000 times in a loop:

MySQL:
CALL TestStoredProcedure(10000);
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 48.906 sec. */

MSSQL:
EXEC TestStoredProcedure @iterations = 10000;
/* Affected rows: 10,000  Found rows: 0  Warnings: 0  Duration for 1 query: 0.875 sec. */

MySQL:

BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= iterations DO
        INSERT INTO mytable (COLUMN1, COLUMN2) VALUES (counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
        SET counter = counter   1;
    END WHILE;
END

MSSQL:

BEGIN
    DECLARE @counter int
    SET @counter = 1

    WHILE (@counter <= @iterations)
        BEGIN
            INSERT INTO mytable (COLUMN1, COLUMN2) VALUES (@counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
            SET @counter = @counter   1
        END
END

Thanks everyone! The solution was found below and it was to insert the START TRANSACTION; line.

Results:

InnoDB (using START TRANSACTION;): 0.406 sec
InnoDB: 48.312 sec
MyISAM: 23.218 sec
MEMORY: 22.922 sec
BLACKHOLE: 22.890 sec
ARCHIVE: 22.860 sec

CodePudding user response：

Hi you are probably using InnoDB as Database Engine and how InnoDB works is that it needs to do commit after every insert and that is making your inserts slow, please rewrite your function as follow:

BEGIN

DECLARE counter INT DEFAULT 1;
Start transaction;
WHILE counter <= iterations DO
    INSERT into MyTable (Column1, Column2) VALUES (counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
    SET counter = counter   1;
END WHILE;
  commit;
END;

CodePudding user response：

For starters avoid loops when and where possible

For example you LOOP takes 0.839 seconds while a set approach took 0.027 seconds

INSERT INTO MyTable (COLUMN1, COLUMN2)
Select Top 10000 
       Column1 = row_number() over (order by (select NULL)) 
      ,Column2 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
  From  master..spt_values n1, master..spt_values n2

EDIT... Just for fun dbFiddle