SQL Server Performance

bulk load option native? or formatfile?

Discussion in 'Performance Tuning for DBAs' started by ilddoo, Jun 1, 2004.

  1. ilddoo New Member

    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!
    ilddoo, Jun 1, 2004
    #2

  2. satya Moderator

    Yes using the format file option, it will be easy to assess and import the selective rows than the native.

    Two factors determine which of these options can or should be used to increase the performance of bulk-copy operations:
    -Amount of existing data in the table compared to the amount of data to be copied into the table.
    -Number and type of indexes on the table.

    Additionally, these factors depend on whether data is bulk copied into a table from a single client or in parallel from multiple clients.

    MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_5zqd.asp article for information.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Jun 1, 2004
    #2

Share This Page