SQL Server Performance

XML Bulk Load vs Bulk Insert

Discussion in 'T-SQL Performance Tuning for Developers' started by Dave Wells, Jun 17, 2003.

  1. Dave Wells New Member

    Does anyone know of any performance tests done comparing XML Bulk Load against Bulk Insert?
    Dave Wells, Jun 17, 2003
    #2

  2. vbkenya New Member

    Not yet heard of or done any tests on the issue but I remember an old sqlmag article comparing the BULK INSERT command against other data loading tools like bcp. You might be interested in reading it at this link:

    http://www.sqlmag.com/Articles/Print.cfm?ArticleID=19760]

    Nathan H.O.
    vbkenya, Jun 18, 2003
    #2

  3. satya Moderator

    Not done so far, but it would help with your feedback if you carry on so.

    _________
    Satya SKJ
    satya, Jun 18, 2003
    #2

  4. BigJimSlade New Member

    XML bulkload is faster then the non bulk insert methods but slower then BCP or BULK INSERT
    statements

    it has several options for perfromance tuning:
    Table lock
    and transaction
    that can increase perfomnace but there are tradeoffs involved

    SP2 beta now suports multiple identity inserts

    I have it loading a 30mb in under 5 seconds YMMV
    BigJimSlade, Jun 18, 2003
    #2

  5. manishas New Member

    I am facing a problem in uploading bulk XML of 37 MB. Kindly let me know the code that u have used for uploading the file in just 5 seconds.
    manishas, Jul 13, 2003
    #2

  6. BigJimSlade New Member

    I just modified the code in the SQLXML 3.0 bulkload example.

    I used .net(C#) I'm working on a custom DTS for this. there are serveral performance options you can use with XMLBulkload including ForceTableLock and useTransaction.

    loadtime of course is highly dependant on the speed of your hardware and the amount of contention in the DB
    BigJimSlade, Jul 18, 2003
    #2

Share This Page