Large-scale data generation (5 million data)

Preface:

A recent job requires performance testing of large scale data

Need 5 million data, this is a very large amount, we can not through CVS file this way to import data into the database

I started thinking about a solution that uses Java programs, loops 5 million times, executes update statements, and inserts data into the database.

Although this method is convenient to operate, but the efficiency must be very slow.

the best solution is given below

Using stored procedures for data insertion

CREATE OR REPLACE FUNCTION insert_users_test () RETURNS void as $BODY $ DECLARE randomsid text;
    randomna_id text;
    P_source text:= ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';
    P_sourcen text:= ' 0123456789 ';
    p_length int: = 9;
    W_result text: = ';
    W_index int: = 0;
    Curtime timestamp;
Enttime timestamp;
      Begin for I in 1..5000000 LOOP Begin--user_id Text column generation (numeric combination) W_result: = ';
      W_index: = 0;
      P_length: = 9;
        For i in 1..p_length LOOP w_index: = Floor (random () * Length (P_sourcen)):: integer + 1; W_result: = W_result | |
      SUBSTRING (P_sourcen, w_index, 1);
      
      End LOOP;

      Randomsid: = W_result;
      --User_name text column generation (alphanumeric combination) P_length: = 8;
      W_result: = ';
      W_index: = 0;
        For i in 1..p_length LOOP w_index: = Floor (random () * Length (P_source)):: integer + 1; W_result: = W_result | |
      SUBSTRING (P_source, w_index, 1);
      End LOOP;
      randomna_id: = W_result;
      Curtime: = ' Now ';
          
      Enttime: = Curtime + '-1 hours '; INSERT into User (user_id, user_name, Enttime, Utdate_time) VALUES (Randomsid, randomna_id, L

    Ast_login_time, Curtime);
    EXCEPTION when Unique_violation THEN NULL;
  End;
End LOOP;
End; $BODY $ LANGUAGE ' plpgsql ';

Execute Select insert_users_test ();

For some of the above usage instructions

Random () Take out random numbers from 0 to 1

Length (str) returns the lengths of the string

SUBSTRING (str,beginindex,length) operation of the string, starting from the number of bits, the length of the removed

:: Forced conversions

: = Assignment operator

--------------------------------------------------

Each time a random generation, add these digits together to generate the data we need.

Catches the exception and continues if the uniqueness constraint is violated.