High Performance Web Services with ADO.NET

[J. L. Goddard]

Suppose I were designing a web service to accept many data transactions
per second and put them into a sql server database.

What design recommendations would you make ?


For examply, should I use the Context.Util transaction binding?

What about SOAPMethod in the WebMethod? Should I go with the default,
or add extra code here?

How about the SqlConnection ?

Should I establish a global SqlConnection in global.asax and use that
for all transactions?

Should I open and close a SqlConnection for each call of the web method?

If I am inserting all these records into a Sql table, is there a way to
pool them up somehow so there are not a million SqlConnections opening
and closing?

Is there a way to combine the INSERTS so that there are not a million
INSERT statements on the SQL server ?

 

[Scott M.]

Should I open and close a SqlConnection for each call of the web method?

It is always a good idea to only keep the connection open for just the
minimal amount of time necessary. If you use a DataAdapter, it will take
care of opening and closing the connection for you.

If I am inserting all these records into a Sql table, is there a way to
pool them up somehow so there are not a million SqlConnections opening
and closing?

ADO .NET uses connection pooling by default so even when you are done with
your connection object, it will go back into the pool for reuse.

Is there a way to combine the INSERTS so that there are not a million
INSERT statements on the SQL server ?

Using the Disconnected paradigm of DataSets, you would make your changes to
the disconnected copy of the data and then do one update.

 

[novelle.vague]

It is always a good idea to only keep the connection open for just the
minimal amount of time necessary. If you use a DataAdapter, it will take
care of opening and closing the connection for you.


ADO .NET uses connection pooling by default so even when you are done with
your connection object, it will go back into the pool for reuse.


Using the Disconnected paradigm of DataSets, you would make your changes
to the disconnected copy of the data and then do one update.

Maybe I wasn't clear.

This is a one way web service for a client to post data to on a per record
basis.

The only operation will be them, consuming the web service and sending data,
which will then be inserted into a sql table...