Usage of Function in SELECT statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Usage of Function in SELECT statement

Hi all, I am having a SELECT query to fetch data for SQL Server Reports. The SELECT query is having a recursive function to retrieve value for each record. Because of that recursive function I am getting the error "Maximum Nesting level 32 is exceeded". So I converted the recursive function to a normal function using while loop and it is also taking more time to fetch the data. When I run the same SELECT query to fetch the data without using the function it only takes 2 minutes. How to do performance tuning for this scenario. Please can anybody help me in this problem. Thanks and Regards,
Ram Kumar N D
what you mean is that the connection used to open the query is not yet close and you want to query that table again?
What kind of recursion are you doing? There are often alternatives to recursion, where you’re not limited to 32 nesting levels. We need to see the relevant table structure(s), some sample data, and the expected results for that sample data. By the way, if a straight SELECT without the UDF is already taking 2 minutes, then what number of rows are you talking about?
The table that I am going to fetch contains around 11,000 records. The recursive function finds teh first parent of the current child value. So it will recursively find the parent value of a column until the parent value is null. This can be changed by writing while loop to achieve the same task. But still it takes more than 15 minutes. The SELECT query involved joining of 15 tables.
Refer this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html Madhivanan Failing to plan is Planning to fail
Madhivanan’s link is one way of doing it. Another option would be this: — table to hold ChildId and ParentId
create table #t (childid INT PRIMARY KEY, parentid INT)
create index idx on #t (parentid) — copy all ChildId that have a ParentId
insert into #t
select t.childid, t.parentid
from tblChildParent t
where t.parentid is not null — check if there are ParentId values in #t that also exist as ChildId
— (meaning there is another parent)
while exists
(select t.childid from #t t
where t.parentid in (select x.childid from #t x))
begin — update the ParentId to the ParentId of the parent ChildId
update t1 set t1.parentid = t2.parentid
from #t t1 inner join #t t2 on t1.parentid = t2.childid
and t2.parentid is not null if @@ROWCOUNT = 0 begin BREAK end end — now you have the ultimate ParentId for each ChildId in #t
select * from #t
I edited my previous post – the code was missing an exit point for the loop, so it got into an infinite loop. Apologies for the inconvenience!
And here’s some sample data … insert into #t
select t.c, t.d
from
(select 1 c, null d
union all select 2, null
union all select 3, 100
union all select 4, null
union all select 100, 1
union all select 300, 2
union all select 400, 3
union all select 500, 4) t And here are the results for this sample …
1NULL
2NULL
31
4NULL
1001
3002
4001
5004
]]>