improving executenonquery times

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi,

We are new to ADO.NET, and any help would be appreciated.

The scenario is as follows: we have a large number of objects
(thousands) to write to a database. Each object has a method that
returns the SQL query which will update a relevant table in the
database.

We had opened a connection to the databse, and for each such string
did executenonquery (on that one connection). The results were
horrible. Then we had moved on to aggregating all the queries,
separated by newline in a one huge string (with StringBuilder of
course) and doing only one executenonquery per all the objects - that
was a big improvement.

Is the paradigm of doing one large executenonquery instead many small
ones, for one connection is indeed optimal ?

Is the paradigm of having one connection (instead of say, thousands,
each per object) optimal ?

Do you have any idea how the times may be improved further ? Is there
any other method ?

Alex & Misha
 
Hi Alex,

No, the batch update is the biggest improvement.
You might gain some performance using stored procedures or sending values
using parameters.
It won't help you using more connections (ok, it depends on the server and
if the tasks can be executed in parallel) - the best way is to try it out.
 
Back
Top