MySQL Cursor Batching Process

MySQLstored-procedures

I have a cursor in MySQL and what I want to do is rather than having an enormous cursor loop that can be up to 4000+ users in size, do it smaller batches so that the MySQL engine doesn't suffer. I have been told to add a SLEEP into it, but have read it is also possible to batch it but not quite sure how to do that most efficiently. In the middle of the loop, it goes off and does a couple of other Stored Procedures for each user, but they are quite small and quick – its the main loop below I am stuck on – any help greatly appreciated:-

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR 
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through
  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
    CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
    SELECT SLEEP(1);    
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN 
    LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Best Answer

Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second.

I will add that code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_BatchCount,v_BatchLimit INTEGER;
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR 
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through

  SET v_BatchCount = 0;
  SET v_BatchLimit = 100;

  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
        CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
        SET v_BatchCount = v_BatchCount + 1;
        IF v_BatchCount = v_BatchLimit THEN
            SELECT SLEEP(1) INTO @dummy;
            SET v_BatchCount = 0;
        END IF ;
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN 
        LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Please note the code

SET v_BatchCount = 0;
SET v_BatchLimit = 100;

and

SET v_BatchCount = v_BatchCount + 1;
IF v_BatchCount = v_BatchLimit THEN
    SELECT SLEEP(1) INTO @dummy;
    SET v_BatchCount = 0;
END IF ;

These encapsulate the SLEEP for every 100 rows.

Feel free to increase the v_BatchLimit to 1000 or more if you are dealing with large result sets.

Please note that I changed

SELECT SLEEP(1);

to

SELECT SLEEP(1) INTO @dummy;

That way, each SLEEP command does not echo anything.

Related Question