September 23, 2009 at 8:01 am
I'm not sure if this topic is covered before, but surely SQLCLR developer must have came accross the situation I explain here.
I created my first CLR TVF for spliting string. Here is my .NET code
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillMethodInt", TableDefinition = "Item INT", DataAccess = DataAccessKind.Read)]
public static IEnumerable CLRFN00SplitStringToInt(string SplitString)
{
List
if (SplitString != string.Empty)
listItems.AddRange(Array.ConvertAll
return listItems;
}
public static void FillMethodInt(object listItem, out int Item)
{
Item = Convert.ToInt32(listItem);
}
private static int ConvertStringToInt(string param)
{
if (param == string.Empty)
return 0;
return Convert.ToInt32(param);
}
I use my CLR function within the EXIST function in the WHERE clause of my query. My query returns about 20011 rows. With my T-SQL string split function used same way returns resultset in around 1 sec. But when I substitute it with CLR TVF for some reason the query takes forever.
Can someone advice what impact CLR objects have in this case. Is this a known issue?
Bhavesh
.NET and SQL Server Blog
September 23, 2009 at 8:59 am
Hi
Never heard about this behavior. did you check for database locks?
Some tips for your split method:
Why do you use RegEx.Split? It's much slower than a direct string.Split.
Try to avoid the List class since Array.ConvertAll already returns a System.Ing32 array which implements IEnumerable.
Instead of string.Empty, you should use string.IsNullOrEmpty since string is a reference type.
Since your Split method already returns an IEnumerable which contains only int values, you don't need the Convert within your FillRowMethod which produces a not needed overhead.
You can remove your ConvertStringToInt method if you use a anonymous delegate (or a lambda expression in .NET 3.0).
Here a shorted (and optimized 😉 ) version
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillMethodInt", TableDefinition = "Item INT", DataAccess = DataAccessKind.Read)]
public static IEnumerable CLRFN00SplitStringToInt(string SplitString)
{
return
Array.ConvertAll<string, int>(
SplitString.Split(new char[] { ',' }),
delegate(string s)
{
return int.Parse(s);
}
);
}
public static void FillMethodInt(object listItem, out int Item)
{
Item = (int)listItem;
}
Greets
Flo
September 23, 2009 at 9:24 am
Also - be sure to set the IsDeterministic and IsPrecise attributes. They can have a big impact on how often your function is evaluated.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 10:15 am
I got following error when creating the assembly
CREATE ASSEMBLY failed because method 'CLRFN00SplitStringToInt' on type 'UserDefinedFunctions' in safe assembly 'SQLCLR.Utilities' is storing to a static field. Storing to a static field is not allowed in safe assemblies.SQLCLR.Utilities
This is due the fact we use delegate in ConvertAll. Removing it solved the error.
However same problem with executing my query
Bhavesh
.NET and SQL Server Blog
September 23, 2009 at 12:01 pm
For your error:
Strange, just deployed the function I posted on my server (as save assembly). Worked fine.
Your initial problem:
Probably it depends on the deterministic of your statement, as Matt said (very good point!). SQL Server is able to analyze your T-SQL implementation to know that it is deterministic, but it's completely impossible to analyze your CLR assembly. You wrote that you work with an WHERE EXISTS statement, so SQL Server has to call your function for each row of your source table. To add the correct attributes should solve the problem.
In addition, to ensure that your function will be called only once, change your query from an WHERE EXISTS to an INNER JOIN.
Greets
Flo
September 23, 2009 at 12:51 pm
Thats not true. Even if I add function in the inner join it will still be called for each row when it joins.
Using Exists is must better approach against inner join.
Also I forgot to mention in my previous reply that I had added those attibutes Matt mentioned, but still I get dead query performance.
Bhavesh
.NET and SQL Server Blog
September 23, 2009 at 1:11 pm
Bhavesh-1094084 (9/23/2009)
Thats not true. Even if I add function in the inner join it will still be called for each row when it joins.Using Exists is must better approach against inner join.
Also I forgot to mention in my previous reply that I had added those attibutes Matt mentioned, but still I get dead query performance.
While it is possible for other types of table-sources, it is not for JOINS on CLR-TVF's. SQL will just call it once ahead of time and dump it a spooler and then read it from there (typically, sometimes it may not need to dump it anywhere).
You may be thinking of CROSS APPLY, however, which *would* result in it being called every time. Note that for JOINS, the functions parameters are not allowed to change within the query's execution, so it only needs to call it once. However, if you need the parameters to change with every row, that's when you must use CROSS APPLY instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 2:05 pm
RBarryYoung (9/23/2009)
Bhavesh-1094084 (9/23/2009)
Thats not true. Even if I add function in the inner join it will still be called for each row when it joins.Using Exists is must better approach against inner join.
Also I forgot to mention in my previous reply that I had added those attibutes Matt mentioned, but still I get dead query performance.
While it is possible for other types of table-sources, it is not for JOINS on CLR-TVF's. SQL will just call it once ahead of time and dump it a spooler and then read it from there (typically, sometimes it may not need to dump it anywhere).
You may be thinking of CROSS APPLY, however, which *would* result in it being called every time. Note that for JOINS, the functions parameters are not allowed to change within the query's execution, so it only needs to call it once. However, if you need the parameters to change with every row, that's when you must use CROSS APPLY instead.
Do you think is there anyway I can find out how many times SQL calls my CLR TVF in the query?
Bhavesh
.NET and SQL Server Blog
September 23, 2009 at 2:20 pm
Bhavesh-1094084 (9/23/2009)
RBarryYoung (9/23/2009)
Bhavesh-1094084 (9/23/2009)
Thats not true. Even if I add function in the inner join it will still be called for each row when it joins.Using Exists is must better approach against inner join.
Also I forgot to mention in my previous reply that I had added those attibutes Matt mentioned, but still I get dead query performance.
While it is possible for other types of table-sources, it is not for JOINS on CLR-TVF's. SQL will just call it once ahead of time and dump it a spooler and then read it from there (typically, sometimes it may not need to dump it anywhere).
You may be thinking of CROSS APPLY, however, which *would* result in it being called every time. Note that for JOINS, the functions parameters are not allowed to change within the query's execution, so it only needs to call it once. However, if you need the parameters to change with every row, that's when you must use CROSS APPLY instead.
Do you think is there anyway I can find out how many times SQL calls my CLR TVF in the query?
Well, you can usually tell by looking at the query plan. However, if you don't trust it, then you could always use a static variable in your CLR-TVF to keep count of the number of times that it is called and then write a scalar CLR-UDF to return that value. Of course you'll need to make it thread-safe and use the UNSAFE permission set, but it does work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 24, 2009 at 4:54 am
Florian Reischl (9/23/2009)
For your error:Strange, just deployed the function I posted on my server (as save assembly). Worked fine.
Your initial problem:
Probably it depends on the deterministic of your statement, as Matt said (very good point!). SQL Server is able to analyze your T-SQL implementation to know that it is deterministic, but it's completely impossible to analyze your CLR assembly. You wrote that you work with an WHERE EXISTS statement, so SQL Server has to call your function for each row of your source table. To add the correct attributes should solve the problem.
In addition, to ensure that your function will be called only once, change your query from an WHERE EXISTS to an INNER JOIN.
Greets
Flo
I also tried using lamda operator but got the same error
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillMethodInt", TableDefinition = "Item INT", DataAccess = DataAccessKind.Read, IsDeterministic=true, IsPrecise=true)]
public static IEnumerable CLRFN00SplitStringToInt(string SplitString)
{
return
Array.ConvertAll(
SplitString.Split(new char[] { ',' }),
param => { return Convert.ToInt32(param); });
}
Can you please let me know if you are deploying in Sql server 2008 or 2005.
I am deploying assembly in 2005.
However my original problem with TVF using in the EXISTS make sense as TVF will be called for each row. I changed my query to use with INNER JOIN and this seems to have solved the problem.
Thanks for your help.
Bhavesh
.NET and SQL Server Blog
September 24, 2009 at 10:09 am
Bhavesh-1094084 (9/24/2009)
Can you please let me know if you are deploying in Sql server 2008 or 2005.
Tested on 2008. Just tried on 2005 and get the same error as you (still strange... ).
Glad we could help! 🙂
September 24, 2009 at 10:20 am
Florian Reischl (9/24/2009)
Bhavesh-1094084 (9/24/2009)
Can you please let me know if you are deploying in Sql server 2008 or 2005.Tested on 2008. Just tried on 2005 and get the same error as you (still strange... ).
Glad we could help! 🙂
I can see the reason why because when I create SqlServerProject in Visual Studio 2008, but default the project uses .net 2.0
May be 3.0 and higher is not support in 2005.
Bhavesh
.NET and SQL Server Blog
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply