x

Finding Circular looped relationships

I have a single table that contains data about relationships. The when I run a query to trace the relationships I get caught in loops because some of the relationships are incorrectly nested because logical loops exist in the data. I want to build a query that would identify these invalid looped relationships so they can be called out to be fixed.

Table has about a million rows. Each row is a one to one relationship. Query worked fine on a limited amount of test data but failed to complete on the full table.

Here is revised code without the prints that seem to have held it up the first time. Hopefully the revised presentation below will read better.

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON Declare @current varchar(1000) declare @Currentrow int -- declare @numberofrows int declare @sql nvarchar (500) declare @totalrows int declare @domainacct table( acct varchar(128), rownum [int] IDENTITY(1,1) NOT NULL) insert into @domainacct (acct) select distinct (rtrim(DomainADAccount) + '' + ADAccount)  from dbo.ADMemberOF join adgroups ON admemberof.adaccount = adgroups.lansama and admemberof.domainadaccount = adgroups.domain  where admemberof.rowtype = 'G' and admemberof.DomainADAccount = 'UP'  and cast(adgroups.grouptype as numeric) < 1  order by (rtrim(DomainADAccount) + '' + ADAccount)

set @Currentrow = 1 set @totalrows = (select count(*) from @domainacct) select @totalrows while (@Currentrow) < @totalrows begin set @current = (select acct from @domainacct where rownum = @Currentrow) SET NOCOUNT ON DECLARE @lvl int, @line varchar(1000) declare @usertree table (founditem varchar(1000), foundlvl int) declare @stack TABLE(item varchar(1000), lvl int) --Create a tempory stack. INSERT INTO @stack VALUES (@current, 1) --Insert current node to the stack. SELECT @lvl = 1 WHILE @lvl > 0 --From the top level going down. BEGIN IF EXISTS (SELECT * FROM @stack WHERE lvl = @lvl) BEGIN SELECT @current = item --Find the first node that matches current node's name. FROM @stack WHERE lvl = @lvl SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name. insert into @usertree (founditem, foundlvl) values(@current, @lvl) DELETE FROM @stack WHERE lvl = @lvl AND item = @current --Remove the current node from the stack. INSERT @stack --Insert the childnodes of the current node into the stack. SELECT distinct DomainMemberOf + '' + ADMemberOf, @lvl + 1 FROM dbo.ADMemberOF WHERE DomainADAccount + '' + ADAccount = @current and DomainMemberOf + '' + ADMemberOf not in (select founditem from @usertree) -- THIS IS THE TABLE THAT WILL CONTAIN ACCOUNTS THAT HAVE A PROBLEM INSERT invalid --Insert the childnodes of the current node into the stack. SELECT distinct DomainADAccount + '' + ADAccount, DomainMemberOf + '' + ADMemberOf, @lvl + 1 FROM dbo.ADMemberOF --dbo.INV_AEX_DIS_Memberof_AD WHERE DomainADAccount + '' + ADAccount = @current and DomainMemberOf + '' + ADMemberOf in (select founditem from @usertree) IF @@ROWCOUNT > 0 --If the previous statement added one or more nodes, go down for its first child. SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes. END ELSE SELECT @lvl = @lvl - 1 --Back to the level immediately above. END --While set @Currentrow = @Currentrow + 1 end select * from invalid
more ▼

asked Feb 10, 2010 at 08:41 AM in Default

Jockel gravatar image

Jockel
11 1 1 2

Wow this needs a whole lot of editing to get it looking nicer. To format it as script you need to add four spaces before each line. I would do it but I don't have the required access yet.
Feb 10, 2010 at 08:49 AM Ian Roke
I have to say, I think you'd get a better response by explaining the data model, rather than asking people to figure it out based on your query... especially when the formatting is as it is.
Feb 10, 2010 at 10:51 AM Matt Whitfield ♦♦
You are right - Issue is that I have a table full of data that has relationships - These are accounts/groups assigned to groups. We have found that some of the groups have been unintentionally assigned in a circular fashion. This causes or searches for full memberships to loop back on themselves indefinitely.
Feb 12, 2010 at 05:21 PM Jockel
We have cleaned up the query- the "prints" cause the initial failure. The final query ran for 23 plus hours to run through a table with 900K rows of relationships. Source table has essentially two sets of columns, One defines the group the second defines the member of the group. One row per relationship assignment
Feb 12, 2010 at 05:24 PM Jockel
Results were written to a new table that ended up with 400K plus results. I think the logic may have misidentified invalid nested relationships. Base Hierachy code was picked up from this site.
Feb 12, 2010 at 05:26 PM Jockel
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x24

asked: Feb 10, 2010 at 08:41 AM

Seen: 1257 times

Last Updated: Feb 12, 2010 at 05:45 PM