Creating many random test database entries

Question

I'm working on a simple time sheet webapp. I have created the following query (simplified - I actually have several mapped columns of a similar type to project_id) to generate test data:

INSERT INTO `entries` (`entry_id`, `user_id`, `project_id`, `date`, `comment`, `hours`)
VALUES
( null,
  0,
  (SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1),
  CURRENT_DATE(),
  '# TEST DATA #',
  (SELECT ROUND((0.25 + RAND() * (24 - 0.24)), 2))
);

I'm currently running this query inside a PHP loop.

This code is currently adequate for my needs now, as I can iterate 50 loops in 0.3037 seconds. However, I fear that when it comes time to test large data sets (searching and report generation for rows > 1 000 000), I may run into problems creating them.

How can I optimize this algorithm? Should I consider using a stored procedure? Or should I just not worry about it, and run the loop to longer iterations, more times?

DROP PROCEDURE IF EXISTS `create_test_entries`;

DELIMITER //

CREATE PROCEDURE `create_test_entries` (IN number INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Creates `number` of random test entries in the timesheet'
BEGIN
    DECLARE i INT;
    SET i = 0;

    WHILE i < number DO
        INSERT INTO `entries` (`entry_id`, `user_id`, `project_id`, `department_id`, `task_id`, `date`, `comment`, `hours`)
        VALUES
        ( null,
          0,
          (SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1),
          CURRENT_DATE(),
          '# TEST DATA #',
          (SELECT ROUND((0.25 + RAND() * (24 - 0.24)), 2))
        );
        SET i = i + 1;
    END WHILE;
END //

Answer 1

It feels like (SELECT project_id FROM projects ORDER BY RAND() LIMIT 1) stands for "get random but still valid project_id". You could probably just iterate over all of your projects (with no randomness) and for every project add a random number of records. I don't think it affects your intention but this will probably work faster.

Answer 2

Performance

How does this query work?

SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1

The database will assign a random number to every single record, and then find the first. So even though only one record gets selected, all records get assigned a random value. This could be expensive when you have a lot of records.

If it's not a problem to load all project ids to memory, then it will be much more efficient.

Logic in code or database

Keeping logic in code is a lot easier to manage than keeping it in a stored procedure, because you can easily add it to version control and track changes to it. So I think stored procedures should be a last resort, and only in performance-critical situations.

You could run a million INSERT queries fast if you stick all of them into a single string, and use the multi_query API. But even a simple loop might be good enough, if you use prepared statements.

Suspicious code in the stored procedure

In the posted stored procedure, the columns in the INSERT and in the SELECT sub-query don't match: it looks like CURRENT_DATE() comes in the place of department_id. I guess it was a copy-paste error in the question, but I think it's worth pointing out anyway.

    INSERT INTO `entries` (`entry_id`, `user_id`, `project_id`, `department_id`, `task_id`, `date`, `comment`, `hours`)
    VALUES
    ( null,
      0,
      (SELECT `project_id` FROM projects ORDER BY RAND() LIMIT 1),
      CURRENT_DATE(),
      '# TEST DATA #',
      (SELECT ROUND((0.25 + RAND() * (24 - 0.24)), 2))
    );