0

I use C# SqlBulkCopy class to load big XML file to SQL server. I've implemented IDataReader, which loops throught XML and get values. The file contains a lot of tables, so I have to call SqlBulkCopy. WriteToServer method as many times as many tables I have in source XML file. Every time DataReader loops throught whole file, which takes a lot of time. How can I improve perfomance of my app? Is there a better way to do what I want?

Here is a plan of my program:

  1. Loop thru source file - determine tables and their columns (and datatypes).
  2. Create tables on Sql Server.
  3. Load data to Sql Server by looping source file and get the values for each table I've determined, one by one.
Nalaka526
  • 11,278
  • 21
  • 82
  • 116
Andrey Gordeev
  • 30,606
  • 13
  • 135
  • 162
  • You may throw away first loop - create table on demand, when the first record, which belong to that table, found in XML. Please, provide additional info: are records laying in continuous blocks, or records from different tables are mixed? Also, you can consider making parallel bulk copy transactions. – Dennis May 18 '12 at 06:24
  • The problem is the data in source file is not structured well. The first record, which belongs to the table may not contain all possible fields. Unfortunately, records from different tables are mixed. Do you mean using threads? – Andrey Gordeev May 18 '12 at 06:29
  • Thanks a lot for your fast reply! – Andrey Gordeev May 18 '12 at 06:31
  • Your main bottleneck here is most likely going to be network bandwidth between the app and the sql server; against that context, I'm not sure it is worth optimizing much further. – Marc Gravell May 18 '12 at 06:34
  • What is the criteria, that this concrete record contains **all** possible fields? Yes, I mean threads, which running parallel readers and bulk copiers. – Dennis May 18 '12 at 06:43
  • There is no such criteries :) I just collect all possible fields to Dictionary (key is column name, value is column type) and create table with columns based on that dictionary – Andrey Gordeev May 18 '12 at 06:49

0 Answers0