领导希望看到平台支持的各种数据库在大数据量的情况分页,查询的情况,所以就有了前提工作,准备构造千万级的数据,在网上找了各种数据库别人构造的文档,后面整理了这篇。
declare @i int declare @j int set @i = 1 while @i <= 1000 begin set @j = 1 begin tran while @j <= 10000 begin insert into [zlyTest].[dbo].[SS_TYPE](test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19) values(1,'1','1','2021-04-14 10:23:03','2021-04-14 10:23:06','2021-04-14 10:23:08',1.1,1.1,1,'1','1','1',1,'1','2021-04-14 10:23:29',1,'1','1','1') set @j = @j + 1 end commit tran set @i = @i + 1 end
按1万条提交一次,1000万大约要50分钟
DROP PROCEDURE IF EXISTS proc_buildata; DROP PROCEDURE IF EXISTS proc_buildata_fast; CREATE PROCEDURE proc_buildata(IN loop_times INT) BEGIN DECLARE var INT DEFAULT 0; SET AUTOCOMMIT=0; START TRANSACTION; WHILE var<loop_times DO SET var=var+1; INSERT INTO ISTRONG_ZLYTEST.SS_TYPE (test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20,test21,test22,test23,test24,test25) VALUES (1,'1','1','1','2021-04-14 00:00:00','2021-04-14 11:01:08',1,1.1,1,1,1,'1','1','1','1','1',1,1,'1','1970-01-01 11:01:28','2021-04-14 11:01:32','1',1,'1','1'); END WHILE; COMMIT; SET AUTOCOMMIT=1; END; CREATE PROCEDURE proc_buildata_fast(IN loop_times INT) BEGIN DECLARE single_max INT DEFAULT 100000; DECLARE i INT DEFAULT 0; DECLARE max INT DEFAULT 0; SET max=loop_times/single_max; SET i=0; WHILE i<max DO SET i=i+1; CALL proc_buildata(single_max); END WHILE; END; CALL proc_buildata_fast(10000000);
按10万条提交一次,1000万大约要91分钟
declare i integer; --定义变量 begin i := 1; loop /* 插入数据 */ INSERT INTO SS_TYPE (test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16) VALUES ('1','1','1',to_date('2021-04-14 11:21:11' , 'yyyy-mm-dd hh24:mi:ss'),1.1,1.1,1,1,'1',1.1,1.1,'1',1,to_date('2021-04-14 11:21:20' , 'yyyy-mm-dd hh24:mi:ss'),'1','1'); /* 参数递增 */ i := i + 1; /* 停止条件 */ exit when i > 10000000; end loop; commit; end;
DECLARE i int :=1; BEGIN FOR i IN 1..10000000 LOOP INSERT INTO "ss_type"(COLUMN_1,COLUMN_2,COLUMN_3,COLUMN_4,COLUMN_5,COLUMN_6,COLUMN_7,COLUMN_8,COLUMN_9,COLUMN_10,COLUMN_11,COLUMN_12,COLUMN_13,COLUMN_14,COLUMN_15,COLUMN_16,COLUMN_17,COLUMN_18,COLUMN_19) VALUES ('1','1','1',1.1,1.1,1.1,1,1,1,1,1,1.1,1.1,'2021-04-14 14:11:06','2021-04-14 14:11:03','2021-04-14 14:11:05','2021-04-14 14:11:01','1','1'); END LOOP; END; COMMIT
1000万大约要15分钟
CREATE OR REPLACE PROCEDURE proc_buildata() AS DECLARE v_id integer :=1; BEGIN FOR i IN 1..100000 LOOP INSERT INTO "ss_type"(test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20,test21,test22) VALUES ('1','1','2021-04-14 11:45:28','2021-04-14 11:45:26','2021-04-14 11:45:30',1,1,1,1,1,1,1,1.1,1.1,1.1,'1','1','1',false,1,1,'1'); v_id := v_id+1; END LOOP; END; CALL proc_buildata();
declare i integer; --定义变量 begin i := 1; loop /* 插入数据 */ INSERT INTO "SS_TYPE"(COLUMN1,COLUMN4,COLUMN7,COLUMN8,COLUMN9,COLUMN10,COLUMN11,COLUMN18,COLUMN19,COLUMN20,COLUMN21,COLUMN22,COLUMN23,COLUMN24,COLUMN25,COLUMN26,COLUMN27) VALUES('1','1','1','1','2021-04-14 11:45:28',1.1,1.1,'1',1.1,1.1,1.1,1,'1','2021-04-14 11:45:26','2021-04-14 11:45:30','1','1'); /* 参数递增 */ i := i + 1; /* 停止条件 */ exit when i > 100000; end loop; commit; end;