July 23, 2009 at 5:51 am
Hi all,
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 http://sqlblog.com/blogs/peter_debetta/archive/2006/07/21/context-connection-is-slow.aspx 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 25, 2009 at 6:09 am
The blog post you linked to seems to explain the difference well.
It is interesting, but I am not sure how relevant it is. It is generally better to use T-SQL for operations where the overall cost is dominated by data access - the whole point of CLR is to extend the functionality of T-SQL and allow more efficient processing of many compute-intensive operations.
Therefore, in any sensible usage of CLR, the overall performance of the routine should be dominated by the processing, and not by data access. Comparing raw performance when scrolling through a large result set seems to be missing the point rather 🙂
As you know, we are encouraged to use the context connection for most purposes (see Regular vs. Context Connections). The key word is 'most'.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 26, 2009 at 11:35 pm
Many thanks for the reply. Maybe my first post wasn't clear - apologies.
The "doing stuff" that I mentioned in the previous post is computationally intensive and hence the choice of CLR (the comparitive trials with equivlant T-SQL code have proven this to be the correct choice), and the regular context connection has already been choosen, lead by the excellent blog posted.
My question was the significant different in performance, from exactly the same code accessing exactly the same data on exactly the same server -except the code wrapped up in a windows forms application is nearly twice as fast as the same code wrapped up in a SQL CLR SP. This seems very strange to me - and has completed stumped me.
Anyone got any suggestions to try?
many thanks
Mark
July 27, 2009 at 12:44 am
Hey Mark,
My initial thoughts are that (a) the application version is being compiled to more optimized code; or (b) because the external connection executes outside the hosted environment, it is pre-emptively scheduled and is suffering from the cost of the associated context-switches and the substantial overhead of calling out of the hosted environment. That would be made worse if your SQL Server has lightweight pooling enabled.
My expectation is that (b) is the dominant factor, but I'm just speculating really.
New questions:
1. Where are you running the stored procedure version from? Management Studio? The web application?
2. Which environment are you using to compile each version, Visual Studio 2005/2008? BIDS?
3. Are you using the same version of the framework with both?
To eliminate some of the variables, I would be tempted to compile your test rig into a stand-alone DLL and use that to ensure you are calling exactly the same code from both environments. For clarity, I mean that both the application and the CLR stored procedure should make a method call to the same DLL.
My inclination would be to stay with the context connection - in spite of what you say. The difficulties associated with threads calling out from the hosted environment can produce some subtle and difficult to diagnose behaviours further down the track.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 27, 2009 at 12:57 am
1. Where are you running the stored procedure version from? Management Studio? The web application?
SP is run from SQL Management Studio
2. Which environment are you using to compile each version, Visual Studio 2005/2008? BIDS?
VS 2008 on SQL 2008 Developer Edition (sorry - should have mentioned this in the first post)
3. Are you using the same version of the framework with both?
Yes - 2.0
To eliminate some of the variables, I would be tempted to compile your test rig into a stand-alone DLL and use that to ensure you are calling exactly the same code from both environments. For clarity, I mean that both the application and the CLR stored procedure should make a method call to the same DLL.
Good idea - I'll code up and report back
My inclination would be to stay with the context connection - in spite of what you say. The difficulties associated with threads calling out from the hosted environment can produce some subtle and difficult to diagnose behaviours further down the track.
You might be right - but the performance advantage I'm seeing is a significant factor
Thanks for your ongoing help
regards
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply