1

I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE Category CHAR(200);
DECLARE cur1 CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='technology2' AND TABLE_NAME <>'technologyRepo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
  FETCH cur1 INTO Category;
     IF done THEN
       LEAVE read_loop;
     END IF;

  SET @table_name:=Category;
  SET @QRY=CONCAT('INSERT INTO 
  technologyRepo (
                  documentId,
                  url,
                  version,
                  first_detected,
                  last_detected,
                  technology_name)
                  SELECT
                  id,
                  url,
                  version,
                  first_detected,
                  last_detected,', QUOTE(Category));
SET @QRYFRM=concat(@QRY, ' FROM technology2.');
SET @sql:=CONCAT(@QRYFRM,@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END LOOP;
CLOSE cur1;
END

Here is the total number of tables that i need to merge:

+------------+
| TABLE_NAME |
+------------+
|       1178 |
+------------+
1 row in set (0.16 sec)

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA                                                                                                                                                           
= 'technology2' AND TABLE_NAME <>'technologyRepo';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|       150116963 |
+-----------------+
1 row in set (0.02 sec)

Any performance optimization for this? I'm not that familiar with cursor.

5
  • Instead of CONCAT and QUOTE, use EXECUTE .. USING ..
    – Rick James
    Jul 22, 2020 at 5:08
  • I am not really familiar with mysql.
    – Edu Cielo
    Jul 22, 2020 at 5:52
  • I use QOUTE to save the table name on the column on the insert
    – Edu Cielo
    Jul 22, 2020 at 5:54
  • How many records are being inserted? From how many tables are these coming? Jul 22, 2020 at 8:19
  • the total number of rows that needs to be inserted is around 150M at 1000+ tables. I used cursor to loop through all those tables
    – Edu Cielo
    Jul 22, 2020 at 8:28

1 Answer 1

1

Don't use a cursor, use a JOIN to do the entire set of statements all at once. Here are two examples:

http://mysql.rjweb.org/doc.php/myisam2innodb#generating_alters http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Both of them depend on doing lots of statements, then you manually copy the commands into MySQL commandline tool (or some other tool). They could be adapted to do prepare/execute.

See also the following for construction a single query to pivot a table:

http://mysql.rjweb.org/doc.php/pivot

3
  • How would that be?
    – Edu Cielo
    Jul 22, 2020 at 5:52
  • 1
    join isn't going to cut it here, Rick. He needs to use a union all -- and I have my doubts that the performance will be that much better. It all depends on how many tables and how many rows are being copied. Jul 22, 2020 at 8:19
  • Cursors are inefficient, and so are multiple 1-row INSERTs.
    – Rick James
    Jul 23, 2020 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.