Introduction
MARS is a new feature in ado.net 2.0 and Sql Server 2005 that allows for multiple forward only read only result sets. With this feature in ADO.NET 2.0 you can have multiple active commands on one connection. By using MARS, you can open a connection to the database, open a new command and process some results, open a second command and process those results, then go back to the first command and process more results. No blocking happens between the commands that share the database connection. You can use MARS support only with SQL Server 2005 systems. Thus using MARS allows you to avoid seeing the dreaded “There is already an open DataReader ..” exception when executing on separate SqlCommands associated with the same connection. You can have multiple SqlDataReaders open on a single connection each reader must be started on a new SqlCommand though and you don’t have to worry about Transaction isolation level scope locks. This feature has been supported by most of the databases and has been a thorn for Microsoft for long which has been removed with SQL Server 2005 release.
MARS makes life easy for developer by removing the overhead of opening new connections but with this approach the performance can actually go for a toss still in some scenarios there is a significant performance improvement thanks to a something called Session Pooling not you all must be wondering what Session pooling is all about. We all have heard about how connection pooling and object pooling keeps the object in memory instead of destroying them completely and reuses them when there is a need for a new object. In fact whenever need arises object is resurrected from the pool. With MARS SqlCommand becomes a valuable resource because we have to associate it with a Sql Server 2005 batch to enable MARS functionality. Session Pooling keeps up to 9 of these valuable disposed SqlCommands in a pool and hands them out the next time you create a command associated with the same connection. This clearly means that you should not use more than 9 SqlCommands per connection, if you do you will be forcing us to create/dispose very very expensive SqlCommands and you will definitely notice the performance drop.
When to use MARS
Mars should be used in two core scenarios,
1) When using MARS results in cleaner looking code
2) When using Transactions and there is a need to execute in the same isolation level scope
Mars comes with a cost in fact there are a lot of hidden costs associated with this feature, costs in the client, in the network layer and in the server. On the client we run into an issue where creating a new batch is not free, we kind of work around this issue by pooling mars commands but it is still expensive if you don’t used the pooled functionality. On the network layer there is a cost associated with multiplexing the TDS buffer, opening too many batches can be more expensive than opening another connection. On the server all MARS batches run in the same scope, worst case scenario your queries will end up running in the order received.
MARS feature can be enables or disabled by the connection string keyword “MultipleActiveResultSets”.This property can be set to true or false depending on whether you want MARS or not.
SqlConnection con = new SqlConnection(“Here goes your ConnectionString“);
SqlDataReader reader;
SqlCommand cmd = new SqlCommand(”Select * From tableName where pk = WhateverField”, con);
if(con.State != ConnectionState.Open){con.Open();}
reader = cmd.ExecuteReader();
while reader.Read(){
DataReader reader2;
SqlCommand cmd2 = new SqlCommand(“Select *from ChildTable where pk = @pk“, con);
cmd2.Parameters(“@pk “).Value = reader.GetString[0];
while reader 2.Read()
{
//DoSomething else
}
}
|
No responses found. Be the first to respond and make money from revenue sharing program.
|