PolarDB-X Best Practice Series (3): Use Qwen and Stored Procedures to Quickly Generate Test Data

This article showcases a method for efficiently generating test data for database performance testing using PolarDB-X.

By Mengshi

When testing database performance, we usually need to generate a batch of test data.

In the past, it usually needed to write a program or script to complete this work, but now it has changed!

PolarDB-X is currently a rare distributed database that supports stored procedures. Combined with large models, we can easily create test data:

For example, there is a table structure like this:

CREATE TABLE t2 (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        c1 varchar(64) NOT NULL ,
        c2 tinyint(4) NOT NULL ,
        c3 varchar(64) NOT NULL ,
        c4 tinyint(4) NOT NULL ,
        c5 varchar(64) NOT NULL ,
        c6 bigint(20) DEFAULT NULL ,
        c7 bigint(20) DEFAULT NULL ,
        c8 double DEFAULT NULL ,
        c9 bigint(20) DEFAULT NULL ,
        PRIMARY KEY (id),
        CLUSTERED INDEX gsi1 (c3)
                PARTITION BY KEY(c3)
                PARTITIONS 32,
        UNIQUE KEY uk1 (c5, c3, c1),
        KEY idx1 (c1),
        KEY idx2 (c5, c1, c8),
        KEY idx3 (c5, c3, c8)
) ENGINE = InnoDB PARTITION BY KEY(c1) PARTITIONS 32;

We only need to ask Qwen questions and describe the following requirements, and it can automatically generate a stored procedure:

Write a stored procedure to generate 1 million pieces of data for the following table, where c1 and c5 are 40-bit hexadecimal strings. (SQL content omitted)

Qwen generates the following stored procedure:

DELIMITER ;;
CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_hex_string CHAR(40);

    WHILE v_counter < 1000000 DO
        -- Generate a 40-digit hexadecimal string
        SET v_hex_string = LPAD(LOWER(REPLACE(UUID(), '-', '')), 40, '0');

        INSERT INTO t2 (
            c1,
            c2,
            c3,
            c4,
            c5,
            c6,
            c7,
            c8,
            c9
        ) VALUES (
            -- Generate a hexadecimal string for c1
            v_hex_string,
            -- Generate a random tinyint value (such as range from 0 to 255)
            FLOOR(RAND() * 256),
            -- Generate a random string for c3 (hexadecimal is applicable as well, only a sample here)
            LPAD(LOWER(REPLACE(UUID(), '-', '')), 32, '0'),
            -- Generate a random tinyint value
            FLOOR(RAND() * 256),
            -- Generate a hexadecimal string for c5
            v_hex_string,
            -- Generate a random bigint value
            FLOOR(RAND() * POW(2, 63)),
            -- Generate a random bigint value
            FLOOR(RAND() * POW(2, 63)),
            -- Generate a random double value
            RAND(),
            -- Generate a random bigint value
            FLOOR(RAND() * POW(2, 63))
        );

        SET v_counter = v_counter + 1;
    END WHILE;

END;;
DELIMITER ;

CALL GenerateTestData();

Since PolarDB-X is highly compatible with MySQL syntax, we do not need to modify the generated results. We can directly paste them into PolarDB-X for execution:

Community

PolarDB-X Best Practice Series (3): Use Qwen and Stored Procedures to Quickly Generate Test Data

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

Best Practices

PolarDB for Xscale

MaxCompute

Database for FinTech Solution