June 5, 2018 at 5:05 am
Hi Guys,
I have a situation like In CLR Function(.DLL) I need to send one XML parameter coming from XML returning UDF(this function gives all records without any condition from 4 tables as XML) so that this CLR will refer this xml and do some calculation for each values from cross apply table and give the result. At present CLR is referring a hard corded table inside DLL. But business people want them to be dynamic.
I don't know C#, but another team who does C# is requesting me to create a function which will send XML as input parameter to CLR.
I was going through google and i found the below syntax
CREATE FUNCTION GetEmpFirstLastNames(Id int)
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;
But I don't see any option for sending input parameter to DLL in below code
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;
Can some one help me how to send XML as input parameter to CLR function DLL ? This DLL should take XML only once and use it for all rows from table which is cross applied .
I also tired to send XML data as input parameter to function like below ,but for each row the whole XML is passing which is giving performance problem like running for more than 2 days for for few records so for full records 20days CREATE FUNCTION GetEmpFirstLastNames(@Id int, @XMLdata nvarmax )
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;
So please guide me . Thanks
June 5, 2018 at 7:43 am
First, why are you insisting on using XML ? Second, how exactly is your function going to be called? Even if that function is CLR-based, calling it for every row of some large table because it's a table-valued function is going to be a nightmare because of the overhead. Trying to process XML back and forth into and out of actual tables for every single row of some other large table is kind of like trying to wade through mud but expecting to be able to run very very fast and arrive at the other side of the bog in sub-second timing when the bog is 100 feet wide and your waders just sprung a sizable leak, and the mud is flowing in ...
We need a lot more detail on exactly what the function needs to accomplish and how it's expected to be used before a viable solution might be developed. I see a lot of poor designs where XML is concerned. A web developer starts using it to send messages to a web service, and suddenly thinks it's the best thing since sliced bread, and then wants to start passing it to a database any which way and thinking that the response will be the same as with the web service. Then they want to blame the database for poor performance when the real problem is the crap design.
As to actually coding the function in whatever .Net language you choose, you should search the web for "C# CLR function examples SQL Server 2012". The coding the of the T-SQL side of the function does have to reference your assembly, but it's up to your assembly to be properly coded to handle the incoming parameters of the T-SQL function.
EDIT: any particular reason to want NVARCHAR(4000) for first and last names? Those things tend to never exceed 50 characters...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 8, 2018 at 6:29 am
sgmunson - Tuesday, June 5, 2018 7:43 AMFirst, why are you insisting on using XML ? Second, how exactly is your function going to be called? Even if that function is CLR-based, calling it for every row of some large table because it's a table-valued function is going to be a nightmare because of the overhead. Trying to process XML back and forth into and out of actual tables for every single row of some other large table is kind of like trying to wade through mud but expecting to be able to run very very fast and arrive at the other side of the bog in sub-second timing when the bog is 100 feet wide and your waders just sprung a sizable leak, and the mud is flowing in ...We need a lot more detail on exactly what the function needs to accomplish and how it's expected to be used before a viable solution might be developed. I see a lot of poor designs where XML is concerned. A web developer starts using it to send messages to a web service, and suddenly thinks it's the best thing since sliced bread, and then wants to start passing it to a database any which way and thinking that the response will be the same as with the web service. Then they want to blame the database for poor performance when the real problem is the crap design.
As to actually coding the function in whatever .Net language you choose, you should search the web for "C# CLR function examples SQL Server 2012". The coding the of the T-SQL side of the function does have to reference your assembly, but it's up to your assembly to be properly coded to handle the incoming parameters of the T-SQL function.
EDIT: any particular reason to want NVARCHAR(4000) for first and last names? Those things tend to never exceed 50 characters...
Thanks sgmunson. I reduced the number of rows inside xml with condition now it is performing better
June 8, 2018 at 6:49 am
JoNTSQLSrv - Friday, June 8, 2018 6:29 AMsgmunson - Tuesday, June 5, 2018 7:43 AMFirst, why are you insisting on using XML ? Second, how exactly is your function going to be called? Even if that function is CLR-based, calling it for every row of some large table because it's a table-valued function is going to be a nightmare because of the overhead. Trying to process XML back and forth into and out of actual tables for every single row of some other large table is kind of like trying to wade through mud but expecting to be able to run very very fast and arrive at the other side of the bog in sub-second timing when the bog is 100 feet wide and your waders just sprung a sizable leak, and the mud is flowing in ...We need a lot more detail on exactly what the function needs to accomplish and how it's expected to be used before a viable solution might be developed. I see a lot of poor designs where XML is concerned. A web developer starts using it to send messages to a web service, and suddenly thinks it's the best thing since sliced bread, and then wants to start passing it to a database any which way and thinking that the response will be the same as with the web service. Then they want to blame the database for poor performance when the real problem is the crap design.
As to actually coding the function in whatever .Net language you choose, you should search the web for "C# CLR function examples SQL Server 2012". The coding the of the T-SQL side of the function does have to reference your assembly, but it's up to your assembly to be properly coded to handle the incoming parameters of the T-SQL function.
EDIT: any particular reason to want NVARCHAR(4000) for first and last names? Those things tend to never exceed 50 characters...
Thanks sgmunson. I reduced the number of rows inside xml with condition now it is performing better
Glad I could help. You may still benefit further with looking at the overall design to see whether any of your data elements are over-sized. First and Last names at 4,000 characters is some pretty serious overkill, and then if you have, say a million rows, the wasted memory on such queries can be enormous because the optimizer sees the potential for 8,000 characters, and grants memory on that basis, and NOT on the potential fact that not one of those elements is probably longer than 50 characters. Over-sizing such columns is a really bad habit that can easily destroy performance because then queries are wasting memory left right and sideways. Do enough of that and you can bring a server to it's knees....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply