RE: Database updating issue

From: Landley (anonymous_at_discussions.microsoft.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 09:46:11 -0800

Hi,

I have done something similar with 900,000 items on an informix database.

I started a transaction on the connection class, passed this to the dataadapter, invoked the Update method on the dataadapter and finally commit the transaction.

It wasn't clear if you meant the 2.5 to 3 seconds was including the call to the stored procedures or not. If this is the case, try threading the these calls to the stored procedure. You'll need separate database connections to perform this.

Another option is to use a collection of datatables and use threading to populate them. Call the update method on an array of dataadapters in separate threads also.

The last two options will need to be outside of a transaction.

Good Luck.
     
     ----- Mircea Pleteriu wrote: -----
     
     Hi all,
     
     I have to insert to my database's table around 200 items. Actualy, for each
     new item that must be inserted an SQL stored procedure is invoked.
     The stored procedure does some tasks and then inserts the item into the
     table.
     
     The items which must be inserted are passed to my code through a DataTable
     object.
     So, each row in this object must be inserted into the database.
     
     To accomplish this task, the inserting of all rows, at the moment, I'm using
     a SQLDataAdapter object with the insert command pointing to the SQL stored
     procedure and invoking the Update() method.
     
     It works fine, the items are all inserted successfully.
     The big problem is that it takes around 2.5 - 3 seconds to insert 200 items.
     
     Do you have any idea to make it faster?
     Thanks,
     
     
     
     

Relevant Pages

  • Re: SQL Server 2000: Identity columns guaranteed sequential?
    ... SQL Server MVP ... INSERT transaction A is assigned 301 ... SCOPE_IDENTITYand @@rowcount to work out what ids were ... This will prevent anyone else INSERTING data into the table until the ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2000: Identity columns guaranteed sequential?
    ... INSERT transaction A is assigned 301 ... interlace their identity ids - I'm assuming this would break ACID. ... The only way I found to stop interlacing was a TABLOCKX hint on the ... This will prevent anyone else INSERTING data into the table until the ...
    (microsoft.public.sqlserver.server)
  • Re: problem using sqlDataAdapter wizard
    ... > your SP does not return any result to client at all and DataAdapter cannot ... >> using wizard) in which I use stored procedure to generate the data. ... Here is workaround for this bad bahaviour (invalid object #temptable). ...
    (microsoft.public.data.ado)
  • Re: SqlDataAdapter - can it be (re)used for 2 SELECT statements
    ... dataadapter for 2 different calls to the same sproc, ... the stored procedure parameter ... //create our DataAdapter object and use it to fill the dataset object ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: transaction with unknown nbr of commands
    ... then call your wrapper stored procedure, passing it all of the user ... explicit transaction with a BEGIN TRAN statement, ... >When I run a sqlcommand in a transaction, ...
    (microsoft.public.dotnet.framework.adonet)
Loading