Tuesday, March 27, 2012

bulk load option native? or formatfile?

I tested bulk load.
I had knowed native option is faster.
But test resulted that formatfile is faster than native option.
Test data is 120000 row, and text file size is 25M.
And I monitored log records, log bytes, elapsed time.
Result : log records, log bytes, elapsed time(ss).
native 7934, 468212, 12
fomatfile 5041, 297452, 9
Is this a special case because data size is much small?
Or is this truth?
Thank you!>
> I tested bulk load.
> I had knowed native option is faster.
> But test resulted that formatfile is faster than native option.
> Test data is 120000 row, and text file size is 25M.
> And I monitored log records, log bytes, elapsed time.
> Result : log records, log bytes, elapsed time(ss).
> native 7934, 468212, 12
> fomatfile 5041, 297452, 9
> Is this a special case because data size is much small?
> Or is this truth?
> Thank you!
>
--
There are always exceptions to a rule. SQL Server documentation says that
native format is the fastest form of data transfer. There is also a table
in SQL Server books online that tells you what format to use on which
circumstances.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sql

No comments:

Post a Comment