RE: Fixed Length file to Database perfomance issues

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alex (Alex_at_discussions.microsoft.com)
Date: 03/15/05 

Date: Tue, 15 Mar 2005 13:15:04 -0800

Just in case I'll get some kind of response, I have an update to my
code/issues:

I've eliminated some bottlenecks by calculating the start position and
length of each field and storing them in a hashtable before populating the
command object's parameters instead of doing this each time I looped, thus
it's only doing it once because those values will always be the same since
it's a fixed length text file.
This got the speed down to 30 seconds for the same 204 rows, which is much
better than the previous result, but still not enough if I'll be dealing with
several hundred thousands of rows.

Any help will be greatly appreciated!!!

"Alex" wrote:

> Hi! I'm trying to read a fixed length text file, get the information for each
> field from an XML file that describes the length of each sequential field,
> and them put them in SQL database. Here's what I'm doing thus far:
>
> 1) Generate a new command object with same name parameters as defined in XML
> (they directly match the names of parameters expected by the stored proc I'm
> using for inserts)
> 2) Read text file one line at a time
> 3) Pass that string to a function that populates the command object's
> parameter values with actual values parsed out of this line using
> string.Substring method
> 4) perform the command.executenonquery for the insert.
>
> The performance is pretty horrible this way, it takes about 2 minutes 34
> seconds for 204 rows with 52 columns.
>
> I'm open to suggestions as to how I can get this data quicker into the
> database. Either by using, BCP, or structs or some other method, but
> generally I need some kind of suggestion.
>
> Thanks in adance!
>

Relevant Pages

  • Re: Insert to DB from Datagrid
    ... If you have the Diffgram with the XML file (so there's rowstate) then you ... can just configure an Adapter with each command you need and point it to the ... I have succesfully populated a datagrid with data from an XML file. ... want to insert the data into a SQL database table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Fixed Length file to Database perfomance issues
    ... Prepare part of the command to gain performance? ... calls to the database since that could also be a source of slowness. ... > length of each field and storing them in a hashtable before populating the ... >> field from an XML file that describes the length of each sequential ...
    (microsoft.public.dotnet.framework.performance)
  • Executing "unknown" command line in C#
    ... I am writing an application that reads an XML file and displays the contents in various ways to the end user. ... The thing they have in common is that there is always a command line to grab a fresh copy of the XML file. ... Then they have to manually type in the arguments afterwards (well, not Y, since he has a dedicated .exe for grabbing). ... Do some magic on the string and figure out what part of it is the actual command, and what part is the arguments. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Executing "unknown" command line in C#
    ... > fresh copy of the XML file. ... I have a string that needs to be ... And it works fine if you execute it from a DOS box, ... > my problem - to do this, I have to divide my command line into two ...
    (microsoft.public.dotnet.csharp.general)
  • Re: Ping the VB (or something) gurus.
    ... which does a mailmerge in Word using an XML file. ... It then creates a separate Word doccie for each record ... "Unlike C and C++, where Command Line Arguments are passed as an array of values to the executable program, in Visual Basic whatever follows the name of the executable program in the Run Window is passed as a single String value. ...
    (uk.rec.motorcycles)