The subject of this post may be a little misleading. When writing CLR code in SQL Server 2005 that requires querying the data, you should connection back to the data using the connection string "context connection=true". However, there are occasions when this is not your best option…
I was working on a project recently that used a CLR-based user-defined type to persist some specialized data and a CLR-based function that would aggregate the data (why a function and not a user-defined aggregate is another post for another day). By default, the connection back to the database to fetch the UDT data was using the context connection string. There was a large amount of data being pulled, and the only apparent bottleneck was disk access when fetching all that data to process. But, after some inquiries, I found out that in this situation, it was more efficient o use a non-context connection string ("data source=server; initial catalog=database, etc…").
Why? Well, the context connection is written such that it only fetches a row at a time, so for each of the 20 million some odd rows, the code was asking for each row individually. Using a non-context connection, however, it requests 8K worth of rows at a time.
Now in many cases, the additional overhead of fetching a single row at a time is not overcome by the use of the already existing connection, but if you are fetching many narrow rows, then you will likely see better performance by using a non-context connection. Most scenarios, however, don’t do intense row by row operations on millions of rows, so by default, use the context connection, and then, if you want to see if you can get a little performance boost, try the non-context connection.
If you want to try this out yourself, you can use the following code in a SQL Server project (C#) in Visual Studio 2005 and deploy it to the AdventureWorks sample database. You will need to make the database permission level
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ConnectWithContext()
{
Object[] rowData = new Object[2];
using (SqlConnection sqlConn = new SqlConnection("context connection=true;"))
{
sqlConn.Open();
SqlCommand cmd =
new SqlCommand("SELECT ContactID, ExtraText FROM dbo.Contact", sqlConn);
cmd.CommandTimeout = 240;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dr.GetValues(rowData);
}
sqlConn.Close();
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ConnectWithContext()
{
Object[] rowData = new Object[2];
using (SqlConnection sqlConn = new SqlConnection(
@"Data Source=SERVER;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
sqlConn.Open();
SqlCommand cmd =
new SqlCommand("SELECT ContactID, ExtraText FROM dbo.Contact", sqlConn);
cmd.CommandTimeout = 240;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dr.GetValues(rowData);
}
sqlConn.Close();
}
}
--Peter