mssql 2008 CPU Xeon E5 2G (12 CORE ) X 2 RAM 128G case 1: while @i1,000,000 begin Insert into T1( column1, column2, column3, ... column34) values( @i, getdate(), rand()*getdate, ... ...) endTest result : 1,000,000 rows time: 00:04:28 size
mssql 2008
CPU Xeon E5 2G (12 CORE ) X 2
RAM 128G
case 1:
while @i
begin
Insert into T1(
column1,
column2,
column3,
...
column34) values(
@i,
getdate(),
rand()*getdate,
...
...)
endTest result : 1,000,000 rows time: 00:04:28 size 503MBcase 2:insert into T1 select * from T1Test result : 1,000,000 rows insert to Table(1,000,000 rows exist) time: 00:00:51
case 3:
select * into NewTableName from T1Test result : 2,000,000 rows insert to NewTable time: 00:00:22
select * into TableName from T1Test result : 2,000,000 rows insert to TableName(2,000,000 rows exist) time: 00:01:00
case 4:
Tmp(20,000,000 rows) Target ( 20,000,000 rows)
Open 10 threads run this sql in same time
insert into Target select * from TmpTest result : 2,000,000X10 rows insert to Target(20,000,000 rows exist) time: 04:29:00
case 5:
cmd>bcp T1 out t1.dat -f bcp.Xml -T -d database
Test result : 20,000,000 rows export to t1.dat time: 00:00:20
cmd>bcp Target in t1.dat -f bcp.Xml -T -d database
Test result : 20,000,000 rows import from t1.dat to Target(500,000,000 rows exist ) time: 00:50:01
case 6:
bcp import 20,000,000 X 2 rows use 2 thread to same blank table
cmd>bcp Target in t1.dat -f bcp.Xml -T -d database
Test result : thread 1 time: 00:08:35 speed :38761 rows/sec ; thread 2 time: 00:07:37 speed: 87359 rows/sec
case 7:
bcp export 639,160,000 rows to a file
cmd>bcp Target out target.dat -f bcp.Xml -T -d database
Test result : time : 01:24:17 speed: 126,380 rows/sec
case 8:
bcp import 639,160,000 rows to target table
cmd>bcp Target in target.dat -f bcp.Xml -T -d database
Test result : Very slow, canceled.
case 9:
use bulk insert clause to import 20,000,000 rows to target table ( 640,000,000 rows exist )
bulk insert Target from 'tmp.dat' with (formatfile = 'bcp.Xml', tablock)
Test result: time : 01:33:06 ( compare with case 5 )
大家有更快的方案可以分享一下吗？
