none
Intensive bulk insert

    Question

  • Hi Experts,

           recently i faced an issue, i want to try to save a 5MB data into a particular table, current solution is passing XML into the SP, then from there to extract and process, however we realize that SQL server not able to handle this kind of XML size (sql server spend a lot of time to extract the XML then only put into the table variable), we are thinking the workaround solution, we are trying to extract the XML value at the client level, then only insert the row data into the table directly, basically we have two kind of solution, table-value or ADO.net bulk insert, due to this is a production and the transaction is very instensive (meaning is it could one second to process more then 10 request of the 5MB data), i would like experts for me some advise. i knew that ADO bulk insert could faster then the table-value, however due to our enviroment is very intensive, i'm not sure ADO bulk insert able to handle this kind of data; another hand table-value is transaction base, 5 MB of data to transform from application level into SP is another heavy load (i have checked the profiler). i very appreacite someond can give me some advise, thanks


    XJYian
    • Edited by XJYian Tuesday, January 11, 2011 6:36 AM typo
    Tuesday, January 11, 2011 6:15 AM

All replies

  • Perhaps you can optimize XML extracting

    http://sqlblog.com/blogs/adam_machanic/archive/2010/01/12/t-sql-tuesday-002-is-it-xml-or-not.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, January 11, 2011 8:46 AM
    Answerer
  • Bulk loading though ADO.NET can be extremely efficient if you use the SqlBulkCopy class. If your source data is in XML, you could try loading that into a DataSet - either by iterating through the XML document manually, or via the DataSet.ReadXml method - here's a MSDN reference for that.

    You can then highly optimise the bulk loading when using SqlBulkCopy class by:

    1) loading into a heap table (no indexes - create the indexes AFTER the data has been loaded in)

    2) specifying the TableLock option for the bulk load, which will get a bulk upload lock on the table

    Last year I blogged about using SqlBulkCopy to bulk load data with a performance comparison vs. another approach of bulk loading data from .NET (using SqlDataAdapter). It highlighted just how fast SqlBulkCopy is - 100K rows loaded in 0.8229s compared to 25.0729s for the SqlDataAdapter approach.

    And there's a great article by SQLCAT: The Data Loading Performance Guide

    So basically, my suggestion is to extract the data out of the XML on the .NET side as I think that will perform better and then you can optimise the bulk loading side separately like above.

    Tuesday, January 11, 2011 9:46 AM
  • If you are on SQL 2008, shredding the XML client-side and use a table-valued parameter is certainly a good idea. Maybe you could even pass an XmlDataReader as the actual parameter for the TVP.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, January 11, 2011 11:13 PM
  • Hi Ada,

           thanks for your sharing, my oncern is this sqlBulkCopy solution able to run at concurrent enviroment, as i mentioned , 5MB data will keep inserting withtin a sec.

    thanks


    XJYian
    Wednesday, January 12, 2011 7:01 AM
  • Hi Erland,

    yes, however for the TVP, my concern is for that 5MB data will transfer from client to SQL server and cache at the SQL table variable (from sql profiler showing the way to manage for the TVP), and prepare for the stored procedures to process. since we are is a intensive enviroment, hopefully sql server able to handle it.

    thanks


    XJYian
    Wednesday, January 12, 2011 7:08 AM
  • > yes, however for the TVP, my concern is for that 5MB data will transfer from client to SQL server and cache at the SQL table variable (from sql profiler showing the way to manage for the TVP), and prepare for the stored procedures to process. since we are is a intensive enviroment, hopefully sql server able to handle it.

    Try it, and what see happens.

    As for the text you see in Profiler, that is a visualisation of the process, and that visualisation sure is expensive for 5MB data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, January 12, 2011 8:49 AM