|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
retrieving a large amount of data across the network from SQL2000We 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? >We need to retrieve approx 200,000 records containing about 100 megabytes of With something like this, I'd say if ever possible try to leave the>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. 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 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. Show quote "Randy Collins" <rcollin***@nyc.rr.com> wrote in message news:uVCGroANFHA.3328@TK2MSFTNGP14.phx.gbl... > 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? > > 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 -- Show quoteW.G. Ryan MVP (Windows Embedded) TiBA Solutions www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com "Randy Collins" <rcollin***@nyc.rr.com> wrote in message news:uVCGroANFHA.3328@TK2MSFTNGP14.phx.gbl... > 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? > >  |
|||||||||||||||||||||||
Other interesting topics