Efficiency question

[B. Chernick]

I've just written a rather simple (and temporary) utility to update a data
table. (VB Dot Net 2.0) All it does is wipe a SQL Server 2000 database
table and then repopulate it. I simply populate a data table within a
dataset and then use the default update call to send it to the server. It
works fine, however...

It only takes a moment on my local machine. If I go across a network, it
takes closer to a half hour. We're talking about approx. 35000 records.

Any suggestions for speeding it up?

(Admittedly part of the problem is almost certainly that we have an
extremely slow and troubled network. Just want to know if I'm overlooking
anything obvious.)

 

[B. Chernick]

Not quite sure what you mean by 'single value'. Basic big picture is that
I'm creating new records for insertion, each record with about a dozen
fields. No updates at all.

 

[William Vaughn \(MVP\)]

Ah, you have chosen one of the least efficient ways to move rows to SQL
Server. I suggest investigating the SqlBulkCopy class. It will be
dramatically faster and does exactly what you're describing.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[B. Chernick]

Efficiency is relative. I whipped up the program in an hour from recycled
code and as it turned out, the problem was the network. When we moved it
local (to the office where the server is physically located), the process
time dropped to about two minutes. More importantly, the boss is happy.

(And I neglected to mention that I first have to get the data out of an
Excel spreadsheet and massage it a bit.)

But, seriously, thanks for the tip. I'll have to try that technique.