March 3, 2009 at 4:52 am
Hi All
I am working with Scaler function / Views in the following way...
DB Objects :
1 - .net CLR Scaler function - fnCLR(ID) will Return Name
2- vwName (view)
Execution Process :
select fnCLR(id) from vwname : ID is a column from vwname
This will generate an output name.
Problem : I am using the same view in the select as well as in the function. this works fine if i have few rows in my table but when i try to
featch the 1000 .. Records then this will degrade query output performance.Below is the function defination.
function fnCLR
@ID,
{
declare @formattedname
select @formattedname =name from vwName where id = @id
--Will follow some condition.
if @formattedname ="XX"
begin
set @formattedname = "Dr."
end
else
@formattedname
return @formattedname
}
March 3, 2009 at 5:40 am
Theoretically the opposite should occur. I have no idea, but this can probably be answered if you start looking at profiler and dmvs/dmfs to see exactly what's going. Give us some more information, such as reads, writes, cache hits, recompile, scans, and other perfmon counters, etc. that have been put in for CLR code. Also, throw 100k rows at it and see if it continues to deteriorate. Understand what is going on behind the scenes to debug one like yours.
Good luck,
Lee
March 3, 2009 at 6:15 am
Why not just pass name to your function? The function will then not need to do a lookup.
March 3, 2009 at 6:57 am
1st thanks to lookin.
-You are correct we can pass name and no all is required the function
is used by some IIIrd party component which can must be an
scaler function and can not have more then 3 input parameter.
-Name i have just taken as a e.g. but my view contains more then 10-15 fields and can not have more then 3 input parameter.
--select fnCLR(ID) from vwname
--fnCLR(ID) itself is having a database call with vwname where ID=@ID
which returns single name+.. and primarly its an scalar return and i have to scan through all the records from the vwname..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply