SQL Server Performance Forum – Threads Archive
Performace overhead with CLR
Hi, I have an SP which involves calling of CLR UDFs in queries. As column names are dynamic, I have to write dynamic query and execute it.. In fact, this is not at all possible with SQL UDF. So I wrote a CLR function, which would construct the dynamic query and execute and return the result. Actually the CLR function is running a query in an OLAP database.. Calling CLR functions are taking time… if I comment calling those functions, I am getting the results fast. I read somewhere like, though we can perform complex operations in CLRs, running queries in CLR would take time. Is there anyway to increase the performance of CLR UDFs? In the meantime, dynamic queries are inevitable in my case….. Would you please anyone help me out…..Regards
Daniel
What does the UDF do that requires CLR involvement? If it’s just used for compiling a regular SQL statement, then by all means use a more directe approach, known as dynamic SQL: you compile the statement into a string type variable, then execute the statement from the variable. SET @SQL = ‘SELECT ………..’
EXEC (@SQL) … or better still: EXEC dbo.sp_ExecuteSQL @SQL ………. with proper parameters.
If the procedure involves both significant data access and computation, consider separating this compilation procedural code into a CLR portion that calls into a T-SQL procedure to perform data access, or a T-SQL procedure that calls into the CLR to perform computation. Are you performing any aggregation calculations against that OLAP database? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Satya, Thanks for your ideas. I will try to separate data access from CLR function. Actually there is a query in CLR UDF which is doing some aggregation operations(Summing)… I think I can write this query in SQL SP and call it from the CLR… right?
Regards
Daniel
http://sqljunkies.com/WebLog/simons/archive/2005/01/17/6468.aspx fyi on the UDFs performance. I would suggest to take help of SYSMON to capture counters as the memory is allocated by SQL Server. All the CLR allocations are delegated to SQL server for the thread pool: all the resources are handled by the CLR through SQL. I would go with Set-based operations, if you can perform them with a single set-based TSQL statement that is absolutely the way to go. Iterating over a set from CLR-based code is interesting for scenarios where you need to do per-row computation that are not easily expressible with a UDF or a UDAggregate (or some other single-statement relational operation). Whether you iterate from TSQL using a cursor or from CLR using a SqlDataReader will depend on what exactly is that you’re doing on a per-row basis.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
This is more of an extension to the underlying CLR performance question that was asked… does turning on CLR (EXEC sp_configure ‘clr enabled’ , ‘1’) affect the perfomance of SQL Server even if the CLR is not being used in any database tasks?
I have ported some string transformation functions from my .NET application into SQL Server UDFs for my own leisure but they are never called by an end user of an application using the database. I use the UDFs just to reduce T-SQL I would have to otherwise write for random update queries I run at non-production hours. I will fall back on T-SQL if turning on CLR in SQL Server has performance implications.
I don’t mind if my personal, off-hour queries are slower than the equivalent T-SQL sproc; I don’t want my end users to to suffer from a performance hit just by enabling this option.
Thanks.
]]>