hierarchical queries, performance problem.

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

hierarchical queries, performance problem.

Author

Topic

mtowse
Starting Member

1 Post

Posted - 2006-07-10 : 11:37:13

Hi,

We are trying to create a hierarchical structure of employees in an organisation.

We have 2 tables, one called DirectReports which holds UserId, SupervisorId. This holds information for each person to show who their manager is.

We have another table called CanSee which holds UserId, CanSeeUserId. This holds your id against everyone elses id who is lower down the hierarchy then you (therefore you have permission to view their details). This second table is mainly used to make reporting faster. I.e. not having to run through the full hierarchy everytime you run a report.

When a user logs in we want to run a query which goes through the DirectReports table and fills the CanSee table with the relevant information.

We have been looking at 2 different methods of doing this show in the articles below. Recursive queries (SQL Server 2005) and using a tmep table as a stack.

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp

The problem with both of these is the speed. If in the first article yif ou are a top level manager loging into a system with 11000 users it takes 25 seconds to run.

With the second article it takes 2 minutes 35 seconds.

Has anyone ever done anything similar or have any ideas how we could speed this up.

I hope i have explained this ok..

Thanks for any help.

Mark.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-10 : 12:02:22

Have a look for articles by Joe Celko.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 13:01:25

quote:
When a user logs in we want to run a query which goes through the DirectReports table and fills the CanSee table with the relevant information.

Why populate it when a user logs in? Why not keep it up to date at all times? It might not be appropriate, but it certainly seems like something to consider.

nr is right though - some of the techniques Joe Celko has written about might be what you need. It can be a pain to maintain the data, but the 'nested set model' might be of use, and would probably mean you could get rid of your CanSee table and use simple between queries instead.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 13:08:51

I meant to post a link or 2, and then I've reminded myself that a 'materialised path' approach might also be something for you to consider. Anyway, you need to do some reading, and some playing, and then figure out what's going to work best for your situation...

http://www.intelligententerprise.com/001020/celko1_1.jhtml
http://www.sqlteam.com/item.asp?ItemID=8866
http://www.dbazine.com/oracle/or-articles/tropashko4

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-07-10 : 13:21:31

Stay away from recursion in SQL.

Try referencing this function as a filter for viewable data:

create function dbo.CanSee(@UserID int)
returns @CanSee table (UserID int)
as
begin

insert	into @CanSee
		(UserID)
select	UserID
from	DirectReports
where	UserID = @UserID

while	@@RowCount > 0
insert into @CanSee
		(UserID)
select	DirectReports.UserID
from	DirectReports
		inner join @CanSee CanSee on DirectReports.SupervisorID = CanSee.UserID
where	not exists (select * from @CanSee CurrentRecords where CurrentRecords.UserID = DirectReports.UserID)

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources