what are the performance differences between table.Rows.Add to insert into directly to table? RRS feed

  • Question

  • hi everyone,

    i have 15 records that i need to insert into an oracle table.

    what should i best use:

    Insert into X 15 times using executenonquery?

    OR use a datatable, have 15 table.Rows.Add  and then UpdateDataSet?

    I am using Data access application block.



    Sunday, May 25, 2008 11:02 AM

All replies

  • While I'm not familiar with the Data Access application block, the normal DataAdapter.Update() method does the same thing "behind the scenes" ...


    IOW, DataAdapter.Update() will spin through the 15 Rows in your DataTable and send 15 individual inserts to the Oracle database. I'm assuming that this Data Access application block does it the same way ... AFAIK, there's no other way to do this.

    Sunday, May 25, 2008 7:46 PM
  • There is no difference between 15 INSERT statement and calling Update method of DataAdapter, since DataAdapter uses same INSERT SQL statement in a loop. What you could probably do is to use some sort of batch utility (similar to SQL Server BCP) to dump data into Oracle. One more potential solution is to pass all the data (or in chunks) as XML content to Oracle stored procedure/batch and deal with it on server side. I am not familiar woth Oracle, but those ideas might help you.

    In a case if you have SQL Server installed, you could use SQL Server SSIS (in SQL Server 2005) or SQL DTS (in SQL Server 2000) to transfer data into Oracle. SSIS/DTS allow to transfer data between different data sources (not just SQL Server), but that library comes with SQL Server installation.


    Monday, May 26, 2008 9:50 AM