retrieving a large amount of data across the network from SQL2000

[Randy Collins]

We need to retrieve approx 200,000 records containing about 100 megabytes of
data across the network from a SQL 2000 DB server to a VB.NET app that then
cursors through this data, does some processing and outputs it to several
destination tables. What is the optimal way to handle this large amount
of data? Should we retrieve this from SQL Server using an ADO.NET
server-side cursor, which takes care of buffering etc? I'm concerned about
the 2 hours or so that the db connection would have to be open. We could
add error handling to reexecute if the query and figure out how to continue
where we left off, but this sounds kludgy. We could retrieve all 100
megabytes of data into a clientside recordset and keep it in RAM. However,
this would not scale should this data increase dramatically in size in the
future. We could retrieve this data to the client server and save it to
disk and then retrieve from it there.

Ideas?

 

[Marc Scheuner [MVP ADSI]]

We need to retrieve approx 200,000 records containing about 100 megabytes of

data across the network from a SQL 2000 DB server to a VB.NET app that then
cursors through this data, does some processing and outputs it to several
destination tables.

With something like this, I'd say if ever possible try to leave the
processing on the SQL Server itself ! Try to get this stuff done in
T-SQL so that you don't have to move around 100+ MB of data over the
wire - that'll ALWAYS be slow, no matter what technology you use......

Mrac
================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch

 

[Sylvain Lafontaine]

First, you should ask yourself why you aren't doing the processing directly
on the SQL-Server. If you can't, then I don't see why you should be
concerned with the 2 hours that this connection will require to be opened if
you are using transactions.

You if want to scale up and don't want to make the work on the server side,
the only other practical solution might be to store the data into a local
copy of the database.

S. L.

 

[W.G. Ryan eMVP]

Definitely avoid using ADO.NET if possible. THis scenario is a nightmare if
you're using disconnected objects - I'd look to DTS if possible or Bulkload