I have been developing with SQL/CLR recently, by creating stored procedures in C# and installing these in SQL Server 2008.
It has gradually emerged that performance of the code when run in SQL Server is far far worse than when run as an ordinary .Net app.
I have benchmark C# code that runs in 0.5 seconds in a console app, but takes around 8 or 9 seconds when installed and run as a stored proc in SQL Server 2008. We are running the 64-bit edition of SQL Server on 64-bit Vista.
Now I just read this:
"Managed code may incur additional overhead necessary to maintain the execution environment and perform security checks. This is the case, for example, when running inside SQL Server and numerous
transitions from managed to native code are required (because SQL Server needs to do additional maintenance on thread-specific settings when moving out to native code and back). Consequently, extended stored procedures can significantly outperform
managed code running inside SQL Server for cases in which there are frequent transitions between managed and native code."
and this worries me, because our app uses a library that does a great deal of calling into some native API, so I know the code is transitioning to/from native code a great deal.
I have to say that I am astonished at the degree of slow down, I need to find out if there is anything we can do to reduce this excessiv
View Complete Post