Investigation report of MSSQL data read and write performance

MSSQL 2008

CPU Xeon E5 2G (12 CORE ) X 2

RAM 128G

case 1:

while @i<1,000,000
begin
Insert into T1(
column1,
column2,
column3,
...
column34) values(
@i,
getdate(),
rand()*getdate,
...
...)
end

insert into T1 select * from T1

select * into NewTableName from T1

select * into TableName from T1

insert into Target select * from Tmp

cmd>bcp T1 out t1.dat -f bcp.Xml -T -d database

cmd>bcp Target in t1.dat -f bcp.Xml -T -d database

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 : doing....

大家有更快的方案可以分享一下吗？

Investigation report of MSSQL data read and write performance,布布扣,bubuko.com

Investigation report of MSSQL data read and write performance

原文：http://blog.csdn.net/linuxnc/article/details/22533693