Okay, here are my results. I ended up HAVING to go to a DataReader.
For some reason, when I was using a DataAdapter, it would puke at
somewhere around 200mb if DBF file size. It would take 50 minutes to
process a 160mb file, but up around 200mb it would take 11 - 12 HOURS
before it puked on me. No idea what was causing that, probably a
memory issue.
I also changed the file writing so that the StringBuilder would build
up about 500 rows before it writes to the file. I think I'm going to
change that to 1000 and see if it will handle it, but I would bet it
can.
The net result of my changes so far:
Pulling data into a DataSet and then writing it out 1 row at a time
would take me 50 minutes to write out a 115mb text file.
Pulling the data through a DataReader and writing that same file in
500 row chunks takes 8 minutes.
It took this process 35 minutes to write out a 600mb file and 44
minutes to write out a 738,568kb file (which going through the
DataSet wouldn't even do!)
I would say that is a pretty darned nice improvement! Thanks to all
who contributed.
KRP
--- In padnug@xxxxxxxxxxxxxxx, "Keith R. Pinster"
<keith.pinster@xxxx> wrote:
>
> Actually, yes there is a good reason. The native FoxPro exporter
has
> a problem with exporting Memo fields (it limits the export to 4000
> characters). I believe the team that I am working with had other
> problems, but the bottom line is they want a .NET solution for this
> extraction.
>
> I understand that a DataReader would be faster, and, like I said, I
> may pursue this in the future. However, writing the data from the
> DataSet to the text files is taking most of the time, so that is
what
> I am focusing on for now.
>
> I did try taking the StringBuilder out of the loop (so to speak)
and
> it made a major difference. In a BAD way. I didn't check the
> timing, but it probably made the writing to the files something in
> the neighborhood of 20 times slower. So, based on that, I would
say
> that the issue is the actual writing to the file. I tried to put
in
> a NewLine (as Brian suggested), but the program bombed because
> several of the text files end up being over 200gb. I am going to
> test by building 100-200 rows at a time between each write and see
if
> that makes a difference.
>
> I'll keep everyone posted of my progress.
>
> KRP
>
> --- In padnug@xxxxxxxxxxxxxxx, "fggoulart" <fggoulart@xxxx> wrote:
> >
> > I agree with Chris Tavares, seems that the datareader would give
> you
> > faster access to your data. With the dataadapter you will have
> > the overhead of the entire result set being returned to the app.
> > Datareader results are returned as your query exectes. Also, and
> > sorry if I am stating the obvious, but I believe foxpro should
have
> > a native export feature that can do what you want. There is
> probably
> > some good reason for not using this?
> >
> > Frank
> >
> > --- In padnug@xxxxxxxxxxxxxxx, "Keith R. Pinster"
> > <keith.pinster@xxxx> wrote:
> > >
> > > I've got an application that pulls data out of a FoxPro
database
> > via
> > > an OleDB provider. I then take tables created by this process
> and
> > go
> > > through the tables a row at a time, building a StringBuilder
> > object,
> > > adding all the cells (with formatting, so it HAS to be done on
a
> > cell-
> > > by-cell basis) and then write each line as it is created to a
> text
> > > file.
> > >
> > > My question is this: does anyone have a suggestion as to how to
> > make
> > > this process faster? It takes a while to build up those text
> > files
> > > when dealing with large tables, so I was hoping someone else
has
> > run
> > > into this issue and figured out a faster way.
> > >
> > > BTW, the text file is a comma AND pipe delimitered file, plus I
> > have
> > > to check for null values and convert a couple of different data
> > types
> > > (like formatting dates in military time), as well as convert
any
> > pipe
> > > charactes that might have ended up in a field to a pair of
> colons.
> > >
> > > So there is quite a bit of data checking and manipulation that
> has
> > to
> > > occur. I was just thinking there might be a faster method of
> > writing
> > > the StringBuilder object to a text file then putting it in the
> > file
> > > one line at a time.
> > >
> > > This is an example of a row that would end up in the text file:
> > >
> > > |text from field 1|,|Field2|,|2004-12-01 23:59:50|,|0|
> > >
> > > Thanks
> > > KRP
------------------------ Yahoo! Groups Sponsor --------------------~-->
$4.98 domain names from Yahoo!. Register anything.
http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/dpFolB/TM
--------------------------------------------------------------------~->
|
