Performance tuning delimited file read and writes to the database

Question

I am in need of help speeding up my app please. The application reads about 53,000 lines of delimited records, parses each row, and then proceeds to send each row to the database to be written. So far, I have identified the Database end as the major bottleneck,and I would appreciate help tuning this up please. Currently, it takes about 20 minutes to process all the records(53,000) with 190 fields each, I would like to significantly reduce that number starting with the code that sends the data to the database.

I use Enterprise Library 5, taking advantage of connection pooling there) to connect to the db like so

   internal void SaveItem(String connString)
    {
        try
        {
            ImportDataAccessor dbacess = new ImportDataAccessor(connString);

            foreach (ItemDetail item in itemEZdetails)
            {
                if (dbacess.SaveProduct(RecordID, item))
                {
                    updatecounter++;
                }
            }
            successfulsaves = dbacess.RowsProcessed;
            updatecounter = dbacess.TotalRows;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


public bool SaveProduct(String RecordUID, ItemDetail item)
{
 //. . . . start function here
 DbCommand insertCommand = db.GetStoredProcCommand("IDW_spEZViewRecordImport");
 db.AddInParameter(insertCommand, "SessionUID", DbType.String, recordUID);
  // the other 189 Parameters go here 
        int rowsAffected = db.ExecuteNonQuery(insertCommand);
                   // Object sreturnval = (String)db.GetParameterValue(insertCommand, "ReturnVal");
                    String returnval = String.Empty;
                    if ( ! (db.GetParameterValue(insertCommand, "ReturnVal") == DBNull.Value))
                        returnval = (String)db.GetParameterValue(insertCommand, "ReturnVal");
                    if (returnval == "60")
                        RowsProcessed++;
                    result = rowsAffected > 0;
}
//end of line add

How do I achieve this with the code I have now. Thanks in advance.

Have a look here http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c — tranceporter, Oct 17 '12 at 16:26

System Down · Answer 1 · 2012-10-18T00:13:19.470

Use SQLBulkCopy as it is very fast when entering multiple rows.

DataTable newProducts = MakeTable();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = 
            "dbo.BulkCopyDemoMatchingColumns";

        try
        {
            // Write from the source to the destination.
            bulkCopy.WriteToServer(newProducts);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

You just need to pass it a DataTable with matching column names, and voila!

score 0 · Answer 2 · answered Oct 17 '12 at 19:48

0

Probably, you'll get better performance by using multiple records INSERT command in your stored procedure, something like this:

Insert into ItemType (ItemTypeName)
VALUES
('TYPE1'),
('TYPE2'),
('TYPE3')

answered Oct 17 '12 at 19:48

Mohsen Afshin

13,273
10
65
90

Performance tuning delimited file read and writes to the database

2 Answers2