THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

context connection=slow?

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

Published Friday, July 21, 2006 2:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dis4ea said:

Now that's interesting information.  One of our projects here is using SQL CLR, I think I'll check with the database developer of that one asap :-)

Keep up the good work.
July 24, 2006 4:34 AM
 

Glen said:

Ok - we have code that creates a seperate connection but the problem is that we don't know what the server name and database name will be. So, to get around this issue we create a context connection as follows:

 SqlConnection sqlConn = new SqlConnection("context connection=true;");

Next we create a new connection as follows (remember that we need to figure out the server and database name at runtime):

 SqlConnection bConn = new SqlConnection("server=" + sqlConn.DataSource + ";integrated security=true;database=" + sqlConn.Database);

Okay so the second connection works...on a server with SQL 2005 as the default instance - however this isn't true on a machine with SQL2000 as the default instance. The problem is that in this case the sqlConn.DataSource property is empty whereas on the server with 2005 as the default instance it returns the server name.

Does SQL 2005 have to be the default instance - is their another way to getting the server name correctly?

August 13, 2007 3:46 AM
 

MPD said:

Hi there,

Very interesting read.  I have a linked problem that I wonder if anyone reading this can help with.

I have a test harness for some investigation we are conducting. It involves using a C# coded SQL CLR stored procedure to loop through the records of a defined table and "do stuff" to them. The "doing stuff" is all coded and working well.

So, just looking at the database retrieval process and the looping...

The purpose of this email is the significant performance differences I am seeing using different methods to call effectively the same code. After reading this article I changed the connection from a context one to a "full" external connection - this nearly doubled the performance - but it is still not consistent.

The code I have in the SQL CLR SP is

[Microsoft.SqlServer.Server.SqlProcedure(Name="clrTest1")]

public static void TestSP()

{

 using (SqlConnection conn = new SqlConnection("Server=(local);Database=Test;Integrated Security=true;Persist Security Info=False;"))

 {

   conn.Open();

   SqlCommand cmd = new SqlCommand("SELECT * from LargeTable", conn);

   SqlDataReader reader = cmd.ExecuteReader();

   while (reader.Read())

   {

   }

   reader.Dispose();

   reader.Close();

   conn.Close();

 }

}

and the equivalent code is a basic Windows Forms application is

private void btn1_Click(object sender, EventArgs e)

{

   using (SqlConnection conn = new SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=Test;server=(local)"))

   {

     conn.Open();                       // open the connection

     SqlCommand cmd = new SqlCommand("SELECT * from LargeTable", conn);

     SqlDataReader reader = cmd.ExecuteReader();

     while (reader.Read())

     {

     }

     reader.Dispose();

     reader.Close();

   }

   return;

 }

}

Strangely enough - this second code sample is nearly twice as fast (average of 10 runs, same machine, same database, same table).

Anyone got any ideas?

best regards

Mark

July 24, 2009 3:13 AM
 

Juha Luotio said:

I recently bumbed in same issues as Glen few years back. How to create external connection to same database than context connection is pointing. I wrote an blog post about it: http://luotio.blogspot.com/2010/04/creating-external-connection-to-same.html.

May 25, 2010 9:29 AM
 

JAnusz said:

Hey Guys!

Would You try to deploy project with Optimize flag turned on?

May 26, 2010 1:46 PM
 

Rob G said:

@MPD

I have no idea why your WinForms sample is faster than your CLR Proc example.  Maybe the "context connection" uses Shared Memory internally (or vice-versa)?

That said, if you try to run your WinForms sample from a machine other than the database server, I am pretty sure your CLR Proc will out-perform the WinForms sample.

June 16, 2010 3:48 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement