which is the better way to go ? 
Author Message
 which is the better way to go ?

I am reading 50,000 records from DB and process them - add delimiters and
then write them to a disk file. Is it better to write 1 records at a time
and write 50,000 times or use System.Text.StringBuilder to append them (4.2
MB) and then write to disk in one shot. I timed both and the write 50,000
times is 67 seconds and the write once is 73 seconds. Compare to the OLD
classic ASP ADO which I can use a getrow to get the records real fast, the
new ADO.NET is really slow have to read record one field at a time because
you have to determine what is the field type first and use the appropriate
get ??? to get the field value - Something is not right there !!

Frank



Mon, 27 Sep 2004 09:04:45 GMT  
 which is the better way to go ?

Quote:

> Is it better to write 1 records at a time and write 50,000 times or use
> System.Text.StringBuilder to append them (4.2 MB) and then write
> to disk in one shot.

Hi Frank:  All things being equal I would tend to go with the multiple
writes.

Quote:
> I timed both and the write 50,000 times is 67 seconds and the write once
> is 73 seconds. Compare to the OLD classic ASP ADO which I can use
> a getrow to get the records real fast, the new ADO.NET is really slow
> have to read record one field at a time because you have to determine
> what is the field type first and use the appropriate get ??? to get the
> field value

How long did it take using the OLD method?

Is this something you can post the code for?

Tom Leylan
www.leylan.com



Mon, 27 Sep 2004 11:01:37 GMT  
 which is the better way to go ?
Frank,

A couple of suggestions..

It sounds like you are using the datareader.. you don't need to check for
type, just use GetString(Position)
How about get it to a Dataset and then run a XSL against it and write the
result to a file...
How about using a stream to build the file

HTH
--
DavidM

Tomorrow is the same day as today was yesterday.


Quote:

> I am reading 50,000 records from DB and process them - add delimiters and
> then write them to a disk file. Is it better to write 1 records at a time
> and write 50,000 times or use System.Text.StringBuilder to append them
(4.2
> MB) and then write to disk in one shot. I timed both and the write 50,000
> times is 67 seconds and the write once is 73 seconds. Compare to the OLD
> classic ASP ADO which I can use a getrow to get the records real fast, the
> new ADO.NET is really slow have to read record one field at a time because
> you have to determine what is the field type first and use the appropriate
> get ??? to get the field value - Something is not right there !!

> Frank



Mon, 27 Sep 2004 10:12:52 GMT  
 which is the better way to go ?
Thomas,

The code is inside a DLL so it is hard to break it out. I am converting my
old product to ASP.NET. Essentially, the operation is very simple. DB
opened, Recordset obtained with GetRow (this is the difference) the rest are
identical in adding delimiters and vbcrlf. Open file, write and then send an
<HREF> to user's browser to allow download of the file. I think the time
difference within the same code is shown faster with 50,000 writres compare
to 50,000 System.Text.StringBuilder append. In VB6-ASP-ADO it has to be
50,000 writes as there is no provision like System.Text.StringBuilder class
to allow building long string. So in ASP.NET the difference is about 6
seconds or so. The concern was that if 1000 user is downloading there will
be a lot of writes !! So, we'll have to do  a web stress test to find out.

The big problem is the getrow and MS folks in these forums did recognize the
problem. But I never thought it is such a big problem. The test in ASP.NET
67-73 seconds is only 10 seconds in ASP-ADO-VB6 (with getrow). When I go
back to look at the code and realized that the fields of each record are
obtained one at a time by knowing it's type before you can provide the
appropriate Get??? to get the value. So, unless there is a GetRow equivalent
in ADO.NET seems like big trouble ahead.....

Thanks,

Frank


Quote:

> > Is it better to write 1 records at a time and write 50,000 times or use
> > System.Text.StringBuilder to append them (4.2 MB) and then write
> > to disk in one shot.

> Hi Frank:  All things being equal I would tend to go with the multiple
> writes.

> > I timed both and the write 50,000 times is 67 seconds and the write once
> > is 73 seconds. Compare to the OLD classic ASP ADO which I can use
> > a getrow to get the records real fast, the new ADO.NET is really slow
> > have to read record one field at a time because you have to determine
> > what is the field type first and use the appropriate get ??? to get the
> > field value

> How long did it take using the OLD method?

> Is this something you can post the code for?

> Tom Leylan
> www.leylan.com



Mon, 27 Sep 2004 10:27:08 GMT  
 which is the better way to go ?
David,
Yes, I am using the OleDbDataReader.
The doc say -> GetString = Gets the value of the specified column as a
string.
Sounds real good. I tried that ->

For c = 0 To UBound(arr)
If c > 0 Then
    DL.append(Z.P_CsvDelimiter)
End If
If oReader.IsDBNull(arr(c)) Then
    s = zz & Z.P_BlankText & zz
    DL.append(s)
Else
    s = zz & oReader.GetString(c) & zz
    DL.append(s)
End If
Next

Got "Specified cast is not valid" error.

Also tried using DS, but I already know it is no good as I ran many test in
Dataset and OleDBReader and SQLClient. Dataset is not for speed at all. This
test using DS takes 64 seconds. XML/XSL.... it is probably worst. Try my
example #72 ( http://www.*-*-*.com/ ) in
generating 227 tree nodes of a TreeView using Staright XML (no control to
render) to see how long it takes.  I always thought such a wonder Framework
with that much features.. something has to give !! Seems like ADO is the
first victum. Hope MS address this problem soon casue users would be very
e{*filter*}d about all these controls and controls... and when they are ready to
go and put a couple 10 Meg data on it and they'll be very disappointed.

Frank


Quote:
> Frank,

> A couple of suggestions..

> It sounds like you are using the datareader.. you don't need to check for
> type, just use GetString(Position)
> How about get it to a Dataset and then run a XSL against it and write the
> result to a file...
> How about using a stream to build the file

> HTH
> --
> DavidM

> Tomorrow is the same day as today was yesterday.



> > I am reading 50,000 records from DB and process them - add delimiters
and
> > then write them to a disk file. Is it better to write 1 records at a
time
> > and write 50,000 times or use System.Text.StringBuilder to append them
> (4.2
> > MB) and then write to disk in one shot. I timed both and the write
50,000
> > times is 67 seconds and the write once is 73 seconds. Compare to the OLD
> > classic ASP ADO which I can use a getrow to get the records real fast,
the
> > new ADO.NET is really slow have to read record one field at a time
because
> > you have to determine what is the field type first and use the
appropriate
> > get ??? to get the field value - Something is not right there !!

> > Frank



Mon, 27 Sep 2004 12:06:52 GMT  
 which is the better way to go ?
Frank,

I would highly recommend reposting this question to the
microsoft.public.dotnet.framework.adonet forum.  Those guys handle database
programming regardless of the language and may have some better ideas for
you.

 Thank You,
 Dale Roberson
 Microsoft Developer Support

 This posting is provided "AS IS" with no warranties, and confers no rights.
 You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.



Mon, 27 Sep 2004 20:39:22 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Modeless and Going both ways

2. dropdownlists and grids, which ways is the best?

3. good ways to use recordsets without autocommit?

4. Better ways to schedule?

5. good ways to use recordsets without autocommit?

6. Better ways to put text into textbox??

7. ** HELP WITH vb 4.0 I am going crazy**

8. Am I going insane...?

9. Am I going mad?

10. help i am going mad

11. Overflow - Am I going crazy?

12. Where am I going wrong ??!!

 

 
Powered by phpBB® Forum Software