Re: Performance issue using cursor inside a stored procedure

[Peter Brawley]

>Inserts 1 record into the parent table
>Select select LAST_INSERT_ID() into a variable
>Then insert into all the child tables using the parent table Id for foreign key.

Why does that need a cursor?

Cursors are much slower than straight sql, so the first thing to do is try to get rid of the cursor.

If it's still slow, index loads on the tables and/or my.cnf settings mught be problematic. First thing to do is benchmark the process in straight sql, ie not in any sproc. If that's still slow, post the results of Show Create Table for the involved tables.