processing feeds: most efficient way?

[darylbewise]

I am trying to create a feed (csv, txt etc) processor that will do the following:

1. Run through each feed line by line
2. Make any required alterations to the values located within the feeds (if required at all)
3. Save each line in turn into a MS SQL database

The first two points above work really efficiently. I am using a really quick CSV Reader (

http://www.codeproject.com/KB/database/CsvReader.aspx)

to loop through each feed.

My problem begins with point 3 above. What is the most efficient way to save to the MS SQL database? Currently this seems like a huge bottle neck im my application.

I have tried to reduce the amount of database calls, for example:

1. save all into an array and bulk upload to the database - the array size becomes far too large to actually hold in memory
2. save all values to a csv file and bulk upload the entire csv file to the database - still takes far too long to execute.

Any ideas would be much appreciated.

 

[jmeckley]

some options
1. run this in the background (out of sync) timing is less of an issue because the GUI is not waiting for the process to complete.

2. Instead of importing everything all at once break up the imports into batches of ~100 and insert each batch using the bulk inserter.

3. Try tuning the database depending on transactions, PK, FK, index, etc. this will slow down inserts because the db is making background calculations with all these features. If you disable them the inserts may speed up. I know this is possible, but I would only entertain this idea as a last resort. i would also implement this in conjunction with a set of staging tables before inserting into the final tables.

4. look into the concepts of ETL (Extract, Transform, Load). MS has SSIS, or something like that. I have tinkered wit the Rhino.ETL which is part of Rhino Tools. if your not familiar with SVN, nant, or build scripts Rhino will be a steep learning curve.


Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190