SQL Server: (1)先插入: Create procedure insertData as BEGIN Set nocount on; declare @iter int; set @iter=0; while @iter<10000 Begin Insert into A Values(NEWID(),'2','3','4'); select @iter=@iter+1; End create Unique index AID on A(id); END (2)再select出来插到b表里 String UpdSQL = "insert into b select * from a order by id asc"; Statement stmt = conn.createStatement(); stmt.execute(UpdSQL); (3)最后删除a表数据 String DelSQL = "Truncate table a"; Statement stmt = conn.createStatement(); stmt.execute(DelSQL);Mysql: (1) String InsSQL = "Insert into A Values (?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(InsSQL); for (int i = 1; i <= 100000; i++) { pstmt.setLong(1, IDGen.genID()); //好象Mysql没有GUID pstmt.setString(2, "2"); pstmt.setString(3, "3"); pstmt.setString(4, "4"); pstmt.addBatch(); } pstmt.executeBatch(); pstmt = conn.prepareStatement("create Unique index AID on A(id)"); pstmt.executeUpdate(); (2),(3)如SQL Server高手看看还有什么能优化的否?
如果只是生成的编号是用Random生成的,其他字段固定的话完全可以用批的insert into A select newid(),'2','3','4' from (select id=a.id+b.id+c.id+d.id+e.id from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b,( select id=0 union all select 100 union all select id=200 union all select 300 union all select id=400 union all select 500 union all select id=600 union all select 700 union all select id=800 union all select 900 ) c,( select id=0 union all select 1000 union all select id=2000 union all select 3000 union all select id=4000 union all select 5000 union all select id=6000 union all select 7000 union all select id=8000 union all select 9000 ) d,( select id=0 union all select 10000 union all select id=20000 union all select 30000 union all select id=40000 union all select 50000 union all select id=60000 union all select 70000 union all select id=80000 union all select 90000 )e)a
ReViSion(和尚) 这个方法很巧妙,能否详细解释一下~
其实你想要的只是十万条记录行,有多少行就可以有多少个随机的编号 这里用的外连接,其结果集的行数,等于各个子表行数的笛卡尔积,其实完全可以不用子查询 insert into A select newid(),'2','3','4' from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b,( select id=0 union all select 100 union all select id=200 union all select 300 union all select id=400 union all select 500 union all select id=600 union all select 700 union all select id=800 union all select 900 ) c,( select id=0 union all select 1000 union all select id=2000 union all select 3000 union all select id=4000 union all select 5000 union all select id=6000 union all select 7000 union all select id=8000 union all select 9000 ) d,( select id=0 union all select 10000 union all select id=20000 union all select 30000 union all select id=40000 union all select 50000 union all select id=60000 union all select 70000 union all select id=80000 union all select 90000 )e
抱歉~~没看得太懂 select id=0 union all select 1 select还能不带from么,而且union不是对两个select集合的合并么还有,select id=a.id+b.id+c.id+d.id+e.id是什么用途佩服老兄,SQL还能这么写的~~
UNION ALL一多了,就出现了该错误: 在查询优化过程中,查询处理器用尽了堆栈空间请问如何解决,谢谢
非常感谢和尚~ 另外,如果用临时表、及表变量,查询优化过程应该也会对其进行优化,是否也会占用STACK空间 比如: insert into A select newid(),'2','3','4' from (select id=a.id+b.id from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b ) temp 其中select id=0 union all select 1就已经是两个temp表的交集,只是整体上temp表比较多 似乎也无法将其temp表再减少阿
然后再把A表里的数据导入到B表,第二个过程可以用上面的方法
我现在是这样用的
StringBuilder createProcedure = new StringBuilder();
createProcedure.append("Create procedure insertData ()");
createProcedure.append("BEGIN");
for(int i=1; i<30000; i++)
createProcedure.append(" Insert into A Values(" + String.valueOf(IDGen.genID()) + ", '2', '3', '4');");
createProcedure.append("END");
Statement stmt = conn.createStatement();
stmt.executeUpdate(createProcedure.toString()); CallableStatement cstmt = conn.prepareCall("{call insertData()}");
cstmt.executeUpdate();
但是StringBuilder的构建时间就用了10秒,prepareCall用了10秒,存储过程执行却只用了1-2秒
应该如何消除前面两个10秒的瓶颈呢?
不知在mysql存储过程中如何生成随机int呢
(1)先插入:
Create procedure insertData as
BEGIN
Set nocount on;
declare @iter int;
set @iter=0;
while @iter<10000
Begin
Insert into A Values(NEWID(),'2','3','4');
select @iter=@iter+1;
End
create Unique index AID on A(id);
END
(2)再select出来插到b表里
String UpdSQL = "insert into b select * from a order by id asc";
Statement stmt = conn.createStatement();
stmt.execute(UpdSQL);
(3)最后删除a表数据
String DelSQL = "Truncate table a";
Statement stmt = conn.createStatement();
stmt.execute(DelSQL);Mysql:
(1) String InsSQL = "Insert into A Values (?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(InsSQL);
for (int i = 1; i <= 100000; i++) {
pstmt.setLong(1, IDGen.genID()); //好象Mysql没有GUID
pstmt.setString(2, "2");
pstmt.setString(3, "3");
pstmt.setString(4, "4");
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt = conn.prepareStatement("create Unique index AID on A(id)");
pstmt.executeUpdate();
(2),(3)如SQL Server高手看看还有什么能优化的否?
SQL Server 37.937 secs
Mysql 27.703 secs
如果是直接BCP 不就可以了吗?
不知Mysql和SQL Server底层还有如何BCP处理的语法
select newid(),'2','3','4' from
(select id=a.id+b.id+c.id+d.id+e.id
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 500
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 5000
union all select id=6000 union all select 7000
union all select id=8000 union all select 9000
) d,(
select id=0 union all select 10000
union all select id=20000 union all select 30000
union all select id=40000 union all select 50000
union all select id=60000 union all select 70000
union all select id=80000 union all select 90000
)e)a
这里用的外连接,其结果集的行数,等于各个子表行数的笛卡尔积,其实完全可以不用子查询
insert into A
select newid(),'2','3','4'
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 500
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 5000
union all select id=6000 union all select 7000
union all select id=8000 union all select 9000
) d,(
select id=0 union all select 10000
union all select id=20000 union all select 30000
union all select id=40000 union all select 50000
union all select id=60000 union all select 70000
union all select id=80000 union all select 90000
)e
select id=0 union all select 1
select还能不带from么,而且union不是对两个select集合的合并么还有,select id=a.id+b.id+c.id+d.id+e.id是什么用途佩服老兄,SQL还能这么写的~~
内部查询处理器错误:在查询优化过程中,查询处理器用尽了堆栈空间。解释
当优化查询时,查询处理器正在使用大量但有限的内存堆栈。在某些极端情况下,堆栈的大小会限制给定的超大型查询(例如,包含具有 100,000 个常量的内目录的查询)。对策
简化查询以避免该问题。例如,在内目录特别大的情况下,使用临时表或表变量来存储常量,并将查询重写为使用该变量或临时表。
另外,如果用临时表、及表变量,查询优化过程应该也会对其进行优化,是否也会占用STACK空间
比如:
insert into A
select newid(),'2','3','4' from
(select id=a.id+b.id
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b ) temp
其中select id=0 union all select 1就已经是两个temp表的交集,只是整体上temp表比较多
似乎也无法将其temp表再减少阿