My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.
However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. To see if I would run into similar behavior, I wrote a simple C# program for a quick test. To simulate the concurrent exception-handling load, the test program spawns 10 background threads, each calling the following method nonstop (the complete program is listed at the end of this post):
static void TryCatch()
{
int c = 0;
for(int i = 0; i < 200000; i++)
{
try { c= i/c; } catch (Exception) { c = 1;}
}
}
Note that variable c is assigned value 0, thus forcing a divide-by-zero exception, and this exception is handled 200,000 times in a loop.
The elapsed time of the method is measured both when there is no additional background threads and when 10 additional threads are running.
On my old two-core 2GB PC workstation, the following output from the program is typical among many runs:
C:\junk>test2.exe
TryCatch() without any background thread = 15617968 ticks
TryCatch() without any background thread = 15559616 ticks
TryCatch() without any background thread = 15566064 ticks
TryCatch() without any background thread = 17472496 ticks
TryCatch() without any background thread = 15782952 ticks
Thread 0 Called TryCatch() 1000 times.
Thread 1 Called TryCatch() 1000 times.
Thread 2 Called TryCatch() 1000 times.
Thread 3 Called TryCatch() 1000 times.
Thread 4 Called TryCatch() 1000 times.
Thread 5 Called TryCatch() 1000 times.
Thread 6 Called TryCatch() 1000 times.
Thread 7 Called TryCatch() 1000 times.
Thread 8 Called TryCatch() 1000 times.
Thread 9 Called TryCatch() 1000 times.
TryCatch() with 10 background threads = 17498336 ticks
TryCatch() with 10 background threads = 17535984 ticks
TryCatch() with 10 background threads = 17664424 ticks
TryCatch() with 10 background threads = 17515200 ticks
TryCatch() with 10 background threads = 17465312 ticks
TryCatch() with 10 background threads = 17498432 ticks
TryCatch() with 10 background threads = 17508656 ticks
TryCatch() with 10 background threads = 17710856 ticks
^C
At least for this test, the adverse concurrency impact that we saw with SQLCLR--and reported in the previous post--is not observed.
Although it’s not strictly an apple-to-apple comparison between this test without SQLCLR and that described in the previous post with SQLCLR, the end user experience is so different that it calls into question why SQLCLR does not seem to handle many concurrent exceptions as gracefully. I don’t have an answer.
I have absolutely no knowledge of how SQLCLR works internally, and can’t explain the concurrency behavior observed in the previous post.
By the way, when I set variable c to 1 in the TryCatch() method, thus avoiding the exception, its concurrency impact (or the lack of) did not change much, if at all.
Anyway, here is the test program. For the output presented above, the program was compiled with .NET Framework 3.5.
using System;
using System.Diagnostics;
using System.Threading;
public partial class Test
{
public static void Main()
{
Stopwatch stop_watch = new Stopwatch();
// warming up a bit
for(int i = 0; i < 5; i++)
{
TryCatch();
}
// measure the elaped time without any additional background threads
for(int i = 0; i < 5; i++)
{
stop_watch.Reset();
stop_watch.Start();
TryCatch();
stop_watch.Stop();
Console.WriteLine("TryCatch() without any background thread = {0} ticks",
stop_watch.ElapsedTicks);
Thread.Sleep(2000);
}
Thread[] user_threads = new Thread[10];
for(int i = 0; i < 10; i++)
{
user_threads[i] = new Thread(new ThreadStart(StartTryCatch));
user_threads[i].Name = i.ToString();
user_threads[i].IsBackground = true;
user_threads[i].Start();
Thread.Sleep(10);
}
Thread.Sleep(5000);
// now measure the elaped time again with 10 additiona threads running
for(int i = 0; i < 20; i++)
{
stop_watch.Reset();
stop_watch.Start();
TryCatch();
stop_watch.Stop();
Console.WriteLine("TryCatch() with 10 background threads = {0} ticks",
stop_watch.ElapsedTicks);
Thread.Sleep(2000);
}
// this will never be reached. You have to Cltr-C to stop the program
for (int i = 0; i < 10; i++)
{
if (user_threads[i] != null)
{
user_threads[i].Join();
}
}
}
static void StartTryCatch()
{
int i = 0;
while (true)
{
if (i % 1000 == 0)
{
Console.WriteLine("Thread {0} Called TryCatch() 1000 times.", Thread.CurrentThread.Name);
}
TryCatch();
Thread.Sleep(10);
i++;
}
}
static void TryCatch()
{
int c = 0;
for(int i = 0; i < 200000; i++)
{
try { c= i/c; } catch (Exception) { c = 1;}
}
}
};