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 ?


--
incognito @ http://kentpsychedelic.blogspot.com

[Scott M.]

Re: High Performance Web Services with ADO.NET

> 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.
[color=blue]
> 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?[/color]

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.
[color=blue]
>
> Is there a way to combine the INSERTS so that there are not a million
> INSERT statements on the SQL server ?[/color]

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]

Re: High Performance Web Services with ADO.NET

Scott M. wrote:
[color=blue][color=green]
>> Should I open and close a SqlConnection for each call of the web method?[/color]
>
> 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.
>[color=green]
>> 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?[/color]
>
> 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.
>[color=green]
>>
>> Is there a way to combine the INSERTS so that there are not a million
>> INSERT statements on the SQL server ?[/color]
>
> Using the Disconnected paradigm of DataSets, you would make your changes
> to the disconnected copy of the data and then do one update.[/color]

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...

--

http://kentpsychedelic.blogspot.com