Data Load Question

  • Thread starter Thread starter AllenF
  • Start date Start date
A

AllenF

I have a process built which load records from a text file (not delimited).
The record length is usually 400 characters, but in some instances can
exceed 800 and each file can contain anywhere from 2,000 to well over 50,000
records. The process breaks down each record into approximately 32 fields,
running thru various audits to ensure data integrity then loads them into an
SQL database.
My question is this:
Currently, I dump all records into a disconnected dataset which at the end
of the load then uploads to the SQL server (so as to not have too much
traffic to the SQL server). On large files the process can take several
hours.
Would it be better just to upload each DataRow to the SQL server as it is
created (and passed the audits) instead of to the DataSet and then to the
SQL server? The drawback I see is a continual connection to the SQL server,
which in my experience draws down resources from the server that are
normally available to other users.
Prior to swapping to C# and SQL, I had each record load directly into the
database file, which, although with large files took a long time, was
considerably shorter in time to load than the current process.

Thanx for any input
Allen
 
Hi Allen
i don't see why do you want to get back to an open connection with the SQL
server open connection since i see the model you have now is much better
(in term of speed and resources) . ofcourse writing all records first to a
dataset( an operation that you say takes long time) then updating the
database once from the dataset is much better . it would be even better if
you record the changes in an xml file instead of text file ... then you can
use the dataset readxml method once and update the database in another
operation .
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
Back
Top