In the performance test of the query operation, it is often necessary to test the performance of the query function in the large data volume mode, it is necessary for us to create some test data to populate the database, to simulate the real environment, the way to build data there are many ways, can use LoadRunner, JMeter pressure measurement tools to press some data into, but this is not high efficiency, the following record a rapid generation of a large number of regular data, that is, the stored procedure function of the database, through the running function to quickly generate a large amount of data. (The premise of using this method is that you need to know the structure of the database tables you want to manipulate)
1. Oracle Database
DECLARE--declaring functionsIINT;BEGINI:=Ten; while(I<10000Loop --Set loop loop I:=I+1; INSERT intoProject_item (ID, CODE, VERSION, Folder_code, NAME, TYPE, Assort, PROGRESS, Region_code, Region_name, Org_code, Org_n AME, Agent_code, Agent_name, OBJECT, CONDITIONS, SCOPE, KIND, Law_time, Agree_time, Accept_time, Send_time, Total_size, is _rate, Is_charge, Is_online, Is_front, Is_public, Is_procedure, procedure_name, Procedure_time, IS_CATALOG, PAY_ONLINE, Consult_online, Complain_phone, window_process, online_process, power_process, Title_name, FUND_ACCOUNT, RELIEF_WAY, Sort_order, CREATOR, Create_time, Last_editor, Last_time, REMARK, property, state, STATUS, Form_keys, Procedure_code, KIN D_name, Is_comple_flow, Olditemid, Service_object, Service_object_type, Xk_item_property, IS_AGENCYORGAN, TITLE_CODE, MODLE_ID)VALUES('A94A341D2A3B4D25BD6212A1'||I'370000-sdgt-xk-'||I||'-'||I1,'370000-sdgt-xk-'||I'Batch new test items'||I'XK','1',NULL,'370000000000','Shandong Province','SD370000GT','Department of Land and Resources of Shandong province','SD370000GT','Department of Land and Resources of Shandong province',NULL,NULL,NULL,NULL, +, -,NULL,NULL,0,'0','0','0',NULL,'0','0',NULL,0,'0','0','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'00001',NULL,TIMESTAMP '2015-05-24 13:01:09',NULL,TIMESTAMP '2015-07-23 09:37:37','No','0','5','5',NULL,NULL,NULL,NULL,NULL,'1',NULL,0,NULL,NULL,NULL);ENDLOOP;COMMIT; --Submit after the loop executesEND;
Note: I variables can be passed through the "| |" Stitching into value.
2. mysql Database
1 delimiter $$--Terminator is $$2 DROP PROCEDURE IF EXISTSMyTest----determine if the process exists, and then delete it.3 CREATE PROCEDUREmytest ()--New process4 BEGIN5 DeclareIint; 6 DECLAREJvarchar( $);7 SetI= 2;8 whileI< OneDo --loop body9 SETJ=CONCAT ('Wwq', i); --Stitching stringsTen INSERT intopub_user (' ID ', ' NAME ', ' account ', ' PASSWORD ', ' user_code ', ' GRADE ', ' GENDER ', ' BIRTHDAY ', ' identity_num ', ' PHONE ', ' MOBILE ', ' EMAIL ', ' POSITION ', ' type_code ', ' last_login_time ', ' role_code ', ' region_code ', ' region_name ', ' org_code ', ' Org_name ', ' org_short_code ', ' role_value ', ' is_admin ', ' STATUS ') One VALUESI'Arch'J'e10adc3949ba59abbe56e057f20f883e',"','0','1','2015-06-25',"',"','31f6fb51a5a8a901c96ace','aabb19d2e1ef19993095','LD','GWY','2016-04-07 11:25:36','95ac32c8b9874b4085a01187c341067b,afb491c3a39c4fd4aa3008e32564a8d9,','500000','Chongqing City','0410','Chongqing Civic Prevention Office',"','role_user,role_hallorg,','0','1'); A INSERT intopub_user_post (' Post_code ', ' User_code ') - VALUESI'a685187d29af4ad793f2753dc17c1435'); - INSERT intopub_user_role (' Role_code ', ' user_code ', ' APP_CODE ') the VALUES('95ac32c8b9874b4085a01187c341067b'I'INSPUR-DZZW-TYSP'); - SetI=I+1; - End while; - End$$--end Definition statement + delimiter; --First return the Terminator to; -Call MyTest (); --Call process
Generate large amounts of data with stored procedures (ORACLE,MYSQL)