Re: Connection object question

William (Bill) Vaughn wrote:
The fastest way to perform an update is to do so on the server. If you're changing existing rows from an external source I suggest moving the changes (in the form of rows) to the server first. Next, execute a server-side UPDATE that pulls the changes out of the imported table and applies the changes.

Why would moving all the data to the server in the form of an imported table and then executing an UPDATE ( or INSERT ) on the server be any faster than executing an UPDATE ( or INSERT ) for each row from the client to the server ? Either way the data must be moved across the line from the client to the server. In your first way, it all gets moved at once. In the second, row by row way, it gets moved with each row.

The one advantage of your way that I see is that by moving all the rows to the server immediately at once, the logic to decide on an UPDATE or an INSERT can be encapsulated on the server. Whereas using the row by row way, I may need to first try an UPDATE based on a key and, if that fails, do an INSERT and that might take two trips from client to server just to process a row. In my actual case, most of the work is just INSERTs so this will save little time, but in areas where UPDATEs based on keys are prevalent, your method might work better.

Is there any other advantage of your method ?

Moving the data to the server is accomplished with DTC or BCP. In ADO 2.0 you'll be able to use the new SqlClient BulkCopy class--far easier. I just wrote a program that imports 450,000 rows into SQL Server--it took about 30 seconds. 

I will look into bulk copying. Thanks !


I'll be showing attendees at the DevTeach conference how to do this in my ADO.NET workshop (see my web site for details). 

What is the address of your website ?
.

Relevant Pages

  • Re: DBCC TRACEON 3604
    ... Take a Profiler trace of the server to capture the hostname and/or application name of the client machine executing the DBCC TRACEON command. ...
    (microsoft.public.sqlserver.server)
  • Server migration - NetBios alias?
    ... On "Moving Day", I could just move the server application and then spend the next few days or weeks helping people reinstall their client application, pointing them to the new host. ...
    (microsoft.public.win2000.networking)
  • Performance
    ... Seems like most of my time is the process of moving the data from the server ... Basicly the server returns a collection to the client. ... Is this just a minimum overhead? ...
    (microsoft.public.dotnet.framework.remoting)
  • Re: Joins and Scability
    ... > We have a client server application where a lot of business logic is ... > executed in stored procedures in SQL Server. ... better news is that executing business logic in SQL is generally much faster ...
    (microsoft.public.sqlserver.programming)
  • Re: question regarding bcp
    ... The command is executed on the server. ... If you want the file to be created on the client, ... > I am executing the following command from query analyzer. ... > The problem is i dont know where the output file is. ...
    (microsoft.public.sqlserver.programming)