Stored Procedure Performance using Temporary Tables

I have the following Stored Procedure I would like to improve the performance of

DELIMITER $
CREATE PROCEDURE NEXT_UNIT_OF_WORK
(
    IN batchId INT,
    IN size INT
)
BEGIN

   start transaction;

   CREATE TEMPORARY TABLE IF NOT EXISTS BATCH_ITEMS_UOW ENGINE=MEMORY AS (SELECT ID FROM BATCH_ITEMS WHERE STATUS = 'UNPROCESSED' AND BATCH_JOB_ID = batchId LIMIT size);

   UPDATE BATCH_ITEMS SET STATUS = 'PROCESSING' WHERE BATCH_JOB_ID = batchID AND ID IN (SELECT ID FROM BATCH_ITEMS_UOW);

   SELECT * FROM BATCH_ITEMS_UOW;

   DROP TABLE BATCH_ITEMS_UOW;

   commit;
END $
DELIMITER ;

The intent is to grab a chunk of batch items via a stored procedure identified by a batch job id and restricted to a number of items (size).

The query seems to take 1 second initially and then takes consecutively longer with each call to the stored proc.

Given this stored proc is going to be called hundreds of times per minute by multiple threads, its unacceptable to take so long.

Is there a better approach to this stored proc?

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:

DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS 
     WHERE STATUS = 'UNPROCESSED' 
       AND BATCH_JOB_ID = batchId 
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;

StackExchange Database Administrators Q#54872, answer score: 6