Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ravisher
Starting Member
1 Post |
 Posted - 2007-05-30 : 11:44:57
|
| Hi there gurus!I have the following situation:Table with hierarchical data:ID (int) ParentID (int) TypeA (bit) TypeB (bit)-------------------------------------------------------1 NULL 0 12 1 0 03 1 1 04 2 0 15 3 1 1Now I have a query that brings back the data from this table, easy enough, but I want to add two extra columns to the result set that will indicate if the selected record has any children (or children's children, N-levels deep) that are of TypeA or of TypeB.I've created a UDF (dbo.getInheritsTypeA(@id)) that returns, for a given ID, whether that record has any children that is of TypeA, and another UDF dbo.getInheritsTypeB(@id) that returns whether that record has any children that are of TypeB.These two UDF's in turn both use a UDF that returns the ID's of all a record's children. My problem is that the execution time on this query is 30s, optimized to 11s with a Non Clustered Index on the ParentID column, on 600 records. I'd like to get that down to about 1 or 2 seconds if possible. What is the best approach for this?Thanks! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-30 : 11:56:19
|
| UDFs are very inefficient for this, as you are finding out. Use a stored procedure implementing an accumulator table instead, as illustrated here: http://sqlblindman.googlepages.com/returningchildrecordsThis can be adapted to your requirements, and executes only once per level in the hierarchy rather than once per record.e4 d5 xd5 Nf6 |
 |
|
|
|
|
|
Better performing Recursive Query