Re: Efficient INSERT of rows- .NET

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 21:46:50 +0200

In addition to that, it might be worth mentioning below two things. In case Jon wants to stick with INSERT
statements:

1. Group several INSERT in the same transaction. Each transaction requires an I/O (write to the transaction
log). You can cut time, perhaps to some 10% by doing several in the same transaction. Not too many, though.
Start with about 1k - 5k and test from there.

2. Group several INSERT in the same batch. Each batch requires a network roundtrip, parsing etc. By batch, I
mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server
(each ExecuteNonQuery is a batch, for instance). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
news:CE3E90C4-E00D-4CC7-89F0-9632CD08FF29@microsoft.com...
> Hope this could help.
>
> http://www.winnetmag.com/SQLServer/Article/ArticleID/43063/SQLServer_43063.html
>
>
> AMB
>
> "Jonathan Porter" wrote:
>
> > Hello- I have a C++ .NET application that rips through a raw file, generating
> > thousands of INSERT statements to insert into a SQL Server 2000 database
> > through the SQLConnection class in .NET.  While this seems reasonably fast,
> > I'm wondering if it is the most efficient way of doing this.  Would creating
> > a stored procedure on the SQL Server and then passing parameters to the SP
> > through the Command object be faster and more efficient?  I've also
> > considered dumping the  contents to a flat CSV file and using SQL Server DTS
> > to BULK INSERT the rows- but that is additional overhead in creating the CSV
> > file and then launching DTS etc.  Any suggestions/comments would be
> > appreciated.
> >
> > Thanks,
> > Jon

Relevant Pages

  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Update time
    ... Because you are not within a transaction. ... SQL Server must prepare to rollback if any row fails. ... exponentially as you hit the hardware limitations of your computer. ... If *I* can figure out that updating a small batch> at a time is faster, why can't the fancy Query Optimizer figure out the> same thing, and do batching behind the scenes? ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)