MySQL Forums
Forum List  »  Performance

Re: Stored Procedure too slow
Posted by: Aftab Khan
Date: December 03, 2012 09:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2937
December 03, 2012 04:46AM
Re: Stored Procedure too slow
1579
December 03, 2012 09:00AM
1167
December 03, 2012 09:27AM
1124
December 04, 2012 12:20AM
1703
December 04, 2012 07:02AM
1299
December 05, 2012 12:18AM


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.