bulk insert much to slower on SQL2k5 in compare with SQL2k.
To understand the problem that I have, I will exemplify but the problem it is general in the SQL2k5.
So any BCP insert it is slower with the SQL2k5 in compare with the SQL2k, doenst matter the edition of SQL2k5.
1. First test environment comparing Enterprise versions of SQL2k and SQL2k5 with memory seted 4GB RAM available on the computer it is 6GB Ram(awe enabled), Windows Server 2003 R2 SP1(this window version was special installed because I got the Info that can work better with the SQL2k5).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.
I was installing the system database from the both system on the same hardisk and the client database on the second harddisk.
I was creating a table with this query:
create table T02
(
autoid int identity (1, 1) not null, --identity (1, 1)
intValue int null,
floatValue float null,
stringValue varchar(255) null
) on [primary]
go
and I was generating a .dat file with 1 milion rows to be imported with the bulk insert Transact-SQL command.
1,1,0.002220,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 1
2,2,0.239019,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 2
3,3,1.219092,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 3
4,4,0.390967,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 4
5,5,1.102109,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 5
6,1,0.202700,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 6
7,2,1.383550,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 7
8,3,20.287169,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 8
9,4,0.250796,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 9
10,5,0.887969,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 10
This are the first 10 lines from the file. You can duplicate this line to obtain the milion lines file.
Now the next step was stopping SQL2k and starting new the SQL2k5 services(no cache, no memory already allocated).
Running the next import command:
bulkinsert T02from'D:\bulk_insert.dat'with(fieldterminator=',')
obtaining the next time results: ~ 17 seconds.
The next step was stopping SQL2k5 and starting new the SQL2k services.
the same command the the time was about 11 seconds.
2. Second test environment comparing Standard versions of SQL2k and SQL2k5 with memory seted 2GB RAM available on the computer it is 4GB Ram, Windows 2000 SP4(a different computer as the first time).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.
I was repeting the tests before and getting the next results:
With SQL2k about 12 seconds.
With SQL2k5 about 20 seconds.
For me this performance it is not acceptable becuase some parts of my application are using the BCP Inserts as an optimize method and when this optimzed parts it is running slower the effect are very strong.
At the end the application it is about 25% slower with Sql2k5 as with Sql2k.
As I sayd at the beginning this problem it is general I can attach some tests programs thats are using the BCP Insert from the DB Library of OLE DB Consumer Templates from ATL. I've choosed this example just becuase it is easear to reproduce.
This programs are having the same procent of performance lost. As the Transact SQL Command the programs are about 50% slower with SQL2k5 as with SQL2k.
Please help me if you know any solution to optimize BCP Inserts on SQL2k5.
Vasile Mirea, Diplom Computer Science.
