Want to Execute Multiple Queries on a Single Connection? Go to MARS, Page 2
Advantages of MARS
MARS provides the following advantages:
- It provides a lighter weight alternative for applications that use multiple connections to overcome lack-of-MARS limitations. However, this is not always the case because multiple connections do provide parallel execution in the server (provided they're not enlisted in the same transaction).
- It enables multiple SqlDataReaders. Prior to MARS, only one command or resultset could be active at one time on a connection. This required the use of two or more connections to execute multiple queries. As the code example showed, MARS allows you to execute multiple queries against a single connection. However, note that MARS does not enable parallel execution of queries; it enables only sequential execution of multiple queries.
Factors to Consider When Using MARS
In general, you should not need to modify existing applications when using a MARS-enabled connection. If you want to use MARS features in your applications, you should carefully evaluate some of the following factors.
As you already know, MARS operations execute synchronously on the server. Even though statement interleaving of SELECT and BULK INSERT statements are allowed, data manipulation language (DML) and data definition language (DDL) statements execute atomically. Any statements attempting to execute while an atomic batch is executing will be blocked.
For example, if you submit two batches under a MARS connection, with one containing a SELECT statement and the other containing a DML statement, the DML can begin execution within the execution of the SELECT statement. However, the DML statement must be completed before the SELECT statement can continue its execution. If both statements are running under the same transaction, any changes made by a DML statement after the SELECT statement has started execution are not visible to the read operation.
MARS Session Cache
When you open a connection with MARS enabled (which is the default case), a logical session is created; this adds additional overhead. SqlClient will cache a MARS session within a connection to minimize the overhead and enhance performance. The cache and sessions contained in it are per connection—they are not shared across connections. The cache will contain at most 10 MARS sessions. This value is not configurable. If the session limit is reached, a new session will be automatically created without throwing an error. When a session is released, it is returned to the pool so that it can be re-used. However, the session will be released to the pool only when the pool's upper limit is not reached. Otherwise, the session is closed.
MARS operations are not thread safe.
MARS-enabled connections also support connection pooling and are pooled like any other connection. However, MARS-enabled connections and MARS-unaware connections are not considered the same. Because of that, they are kept in separate connection pools. For example, if an application opens two connections, one with MARS enabled and one with MARS disabled as the only distinction, the two connections will be in separate pools.
Top-level Temporary Tables
In SQL Server 2000, all the batches executed under the same connection share the same batch environment. When you make a change to the batch environment, that change is visible to all subsequent batches. With MARS, a default execution environment is associated with a connection. Every new batch that starts executing under a given connection receives a copy of the default environment. Whenever code is executed under a given batch, all changes made to the environment are scoped to the specific batch. Once execution finishes, the execution settings are copied into the default environment.
As previously mentioned, MARS does not support parallel execution of multiple commands against the database. Also, MARS is not designed to remove all need for multiple connections in an application. If your application needs true parallel execution of commands against a server, you should consider using multiple connections instead of MARS.
Get Grounded in MARS
As you can see, MARS provides a number of improvements when it comes to executing multiple queries against the database using a single connection. By appropriately using MARS in relevant places, you can not only produce cleaner looking code that is easy to maintain but also see performance improvements in some areas. The example in this article represents just the tip of the iceberg, but it should give you a kick start on this new feature. Once you get familiar with it, you will have one more option when it comes to issuing multiple queries against a database connection.
Download the Code
To download the accompanying source code for the demo, click here.
About the Author
Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at firstname.lastname@example.org.