ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Peter — Tue, 14 Sep 2004 01:27:00 GMT

Angel,

Can you explain why you are not supporting MARS inside in SQLCLR?

It will make porting code harder, and as there's no SqlResultset it won't be possible to have a clean way of having two open SQL statements in the same CLR stored proc. One option is to use SqlClient from SQLCLR (aargh) and the other is to use the cursor API with T-SQL statements (ugggh), which is the same as SqlResultsets but harder.

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Angel — Tue, 14 Sep 2004 19:41:00 GMT

Peter,
I don't think I can really explain it other than to reiterate that this feature has been much much harder to do than you would think. I would expect this feature to be considered for a later release of the SQLCLR provider.

Tahnks for your feedback
Angel

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Andres Aguiar — Wed, 15 Sep 2004 14:25:00 GMT

Angel,

We are currently caching our SqlCommands in the TLS. Does this 'Session Pool' feature do the same thing? I mean, should we stop caching SqlCommands for Whidbey?

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Angel — Wed, 15 Sep 2004 18:14:00 GMT

Andres,
Pre-whidbey creating a new SqlCommand is practically free, I am going to have to assume that if you saw the need to cache them then it must have something to do with your exisiting architecture so I cannot know if Session Pooling will provide the same benefits.

The reason this feature was implemented is because there is a not insignifcant cost associated with using a SqlCommand in whidbey. On the client we must associate it with a session on the server, on the network layer they need to multiplex the TDS buffer for it and on the server create a new session. We found that the only way to keep performance was to pool these expensive commands (up to ten of them only, no knobs). Now when you open a connection and create a few commands for the first time it will be much slower than in v1.1, but as long as you keep the connection arround (either yourself or by relying on pooling) it will continue to reuse these cached commands. Of course this means that if you use more than 9 commands per connection you are going to notice a performance drop.

Angel

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Andres Aguiar — Wed, 15 Sep 2004 18:26:00 GMT

OK.

We reused commands with the .Text and parameters set, to avoid creating the parameters each time, etc. Our key in the cache is the sql statement.

We reuse the commands in multiple connections, but this new session cache seems to be related to caching commands in the same connection so it's probably different.

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Angel — Wed, 15 Sep 2004 18:34:00 GMT

Andres,
I see, yes completelly different. this caching has nothing to do with text and parameters and it will not affect your current architecture. We are caching the underlying resource that has become the MARS enabled SqlCommand and it is as transparent to the implementation as connection pooling.

I will get a blog on this going in the next few days, but the basic idea goes something like this:

open connection
create command and execute it //command creation is very expensive now.
close connection //connection goes back to pool, command goes to session pool.

open connection //same connection as above, retrieved from pool
create command and execute it //we use the cached command instead of creating a new one. No performance penalty.

In your scenario you can associate your cached command to the second connection just as you do today and get the benefit of session pooling transparently.

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Bill Vaughn — Wed, 15 Sep 2004 19:37:00 GMT

Angel, you say that the "qintessential" MARS configuration uses the same connection to post changes to the server while you're reading a DataReader. Yes, I can see how this would be appealing, but wouldn't it make more sense to do this processing on the server in TSQL (or CLR)? Moving data to the client to do logic-driven updates has always been frowned on. Now that one can code CLR-based logic the opportunity to do far more sophisticated logic-driven operations on the server makes more sense than ever.

I'm also somewhat disturbed by the performance hit we're having to take (by default) to participate in MARS when not a single application that exists today uses it. I think the default behavior should disable MARS unless specifically called for.

Performance in Whidbey is bad enough as it is without adding needless overhead for features we don't need except in special cases (which I have yet to see).

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

Angel — Wed, 15 Sep 2004 21:09:00 GMT

Bill,
Great comment! I completely agree with you, I am not quite sure I understand the people who dislike stored procedures (I guess I have not met their DBAs...).

The fact remains that this feature has been specifically requested by some of our most important partners to do exactly this type of logic driven updates.

The performance hit is not “By Default” per se, since there is no way to avoid it. Setting MultipleActiveResultSets=false does not perform better, it just throws an exception if you attempt to use MARS. Yes this is definitely disturbing but keep in mind that this is only a one time set up cost per connection in pooled scenarios.

Pozn

ISlavoF.Save() — Tue, 23 Nov 2004 16:17:00 GMT

Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel Saenz-Badillos — Fri, 14 Jan 2005 08:50:00 GMT

Ado.net 2.0 Mars Session Pooling. (aka stuff you don't need to know.)

Angel Saenz-Badillos — Sat, 15 Jan 2005 01:06:00 GMT

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

shafi — Mon, 12 Jun 2006 17:23:31 GMT

what is sqlservercursor? & Ado.net cursors?

re: ADO.NET 2.0 Multiple Active Resut Sets per connection in Sql Server 2005 (MARS) FAQ

shafi — Mon, 12 Jun 2006 17:23:58 GMT

nothing

Resources from geekSpeak webcast - Data with Mark Dunn

geekSpeak — Tue, 19 Sep 2006 00:01:54 GMT

Thanks for attending the webcast. We intended to also have&nbsp;Shawn Wildermuth (ADO Guy) on the webcast...

Commands are from Venus, Queries are from MARS « .Net FX3 By Jayesh Chudasama

Commands are from Venus, Queries are from MARS « .Net FX3 By Jayesh Chudasama — Mon, 25 Sep 2006 01:05:01 GMT

PingBack from http://dotnetfx3.wordpress.com/2006/09/24/commands-are-from-venus-queries-are-from-mars/

The Multiple-Record Set Problem « Will SQL for food, will blog for free

The Multiple-Record Set Problem « Will SQL for food, will blog for free — Wed, 13 Feb 2008 07:54:34 GMT

PingBack from http://willsql4food.wordpress.com/2008/02/13/the-multiple-record-set-problem/

net ado net seeing connection string

net ado net seeing connection string — Sat, 31 May 2008 22:45:30 GMT

PingBack from http://laci.stocknewsdigest.info/netadonetseeingconnectionstring.html

Exigency In Specie / A Problem with Fake MARS

Exigency In Specie / A Problem with Fake MARS — Mon, 24 Nov 2008 21:53:23 GMT

PingBack from http://www.peat.me.uk/2008/03/07/a-problem-with-fake-mars/

Angel Saenz Badillos ADO NET 2 0 Multiple Active Resut Sets per | Paid Surveys

Angel Saenz Badillos ADO NET 2 0 Multiple Active Resut Sets per | Paid Surveys — Tue, 02 Jun 2009 09:55:18 GMT

PingBack from http://paidsurveyshub.info/story.php?id=75970