How many rows did you attempt to insert? Whats the table type? Innodb or Myisam? Single row insert is slower than batch insert. Also, if the table type is innodb then you can evaluate the effect of the frequency of commits on performance, for example:
CREATE PROCEDURE `testAddPhysNode`(
IN _iterations INT,
IN _startIter INT,
IN _netID INT
)
BEGIN
DECLARE _type INT;
DECLARE _newid INT; -- this is output param
DECLARE _creat BIT; -- this is output param
SET _type = _startIter;
set autocommit=0; -- disable autocommit
WHILE (_type < (_startIter + _iterations)) DO
CALL AddPhysNode(1, 123, 1, 'test', 0x1234, 0x1256,
_netID, 1, _type, _newid, _creat);
SET _type = _type + 1;
IF (_type % 10 = 0 ) THEN -- commit after every 10 rows
commit;
END IF;
END WHILE;
COMMIT;
set autocommit=1; -- enable autocommit
END
Please provide following:
SHOW CREATE PROCEDURE AddPhysNode\G
>with a single insert statement in a table with autoincrement PK
Please provide table structure information i.e. SHOW CREATE TABLE tbl_name\G
And show table status like 'tbl_name'\G
Edited 1 time(s). Last edit at 12/03/2012 09:00AM by Aftab Khan.