What's the most efficient way to parse these 500k rows?

Question

I currently have a database full of ACL entries which looks like so:

I need to go through and parse out the difference between a root node (like \\chrlcltsvr02\AYY_LMO\ClientServices) and it's child nodes (ex. \\chrlcltsvr02\AYY_LMO\ClientServices\Client1).

I've attempted doing this in C# code by using an ORM and raw T-SQL like so (I do in fact know that opening a session per row is a horrible idea):

foreach (string path in distinctPaths)
{
    using (session = sessionFactory.OpenSession())
    {
        string query;

        query = String.Format("SELECT DISTINCT UserGroup, AllowDeny, Permissions FROM FilerACLs WHERE FullPath LIKE '{0}'", path.Replace("'", "''"));

        var parentACLs = session.CreateSQLQuery(query).SetResultTransformer(Transformers.AliasToBean<ShareACLEntry>()).List<ShareACLEntry>();

        query = String.Format("SELECT DISTINCT UserGroup, AllowDeny, Permissions FROM FilerACLs WHERE FullPath LIKE '{0}\\%'", path.Replace("'", "''"));

        var childACLs = session.CreateSQLQuery(query).SetResultTransformer(Transformers.AliasToBean<ShareACLEntry>()).List<ShareACLEntry>();

        if (childACLs.Except(parentACLs, comparer).ToList().Count > 0)
            Console.WriteLine("{0} has diffs!", path);
    }
}

And finally comparing the resulting data to see whether the child nodes differ from the root node.

By differ, I mean if I have an ACL for group "CLT-AD\Full Access Shared-CHRL" with allowed full control on the parent node and not on the child node, I'd like to note that the ACL exists on the child but not the parent.

Unfortuantely, this process is far too slow to parse the 500k rows in any decent amount of time.

I'd like to know if anyone has an idea for efficiently determining if there is differences in the data--be it using T-SQL directly, a SQL CLR function, or a better algorithm in general.

Please let me know if clarification is required.

Thanks!

EDIT

Since I've gotten a fair amount of hate on this question let me re-clarify exactly what I'm looking for minus the failed approaches I've outlined above.

I recently performed a scan against ~1,000 shared folders on a Windows server. This scan recursed from the top level directory all the way down the hierarchy of folders, and for each folder recorded a row for each ACL.

The database therefore looks like the screenshot above.

What I need to do is pull a report from this database which details the difference (or even whether there is any) between the ACLs recorded from a top level directory and the ACLs recorded for any directory under this top level directory.

Hopefully that makes more sense.

Answer 1

Here is some TSQL,

DECLARE @parentFullPath NVARCHAR(260) = N'\\chrlcltsvr02\AYY_LMO\ClientServices';
DECLARE @childFullPath NVARCHAR(260) = N'\\chrlcltsvr02\AYY_LMO\ClientServices\Client1';

SELECT
            [UserGroup],
            [AllowDeny],
            [Permissions]
    FROM
            [ACLs]
    WHERE
            [FullPath] = @childFullPath
EXCEPT
SELECT
            [UserGroup],
            [AllowDeny],
            [Permissions]
    FROM
            [ACLs]
    WHERE
            [FullPath] = @parentFullPath;

It may or may not do what your require, its hard to tell.

---

To find all the parent child pairs,

WITH [paths] AS (
SELECT
             [FullPath]
    FROM
             [ACLs]
    GROUP BY
             [FullPath])
SELECT
            [P].[FullPath] [ParentFullPath],
            [C].[FullPath] [ChildFullPath]
    FROM
            [paths] [P]
        JOIN
            [paths] [C]
                ON
                        [C].[FullPath] <> [P].[FullPath]
                    AND
                        CHARINDEX([P].[FullPath], [C].[FullPath]) = 1;

so you could in fact do it all at once, something like this.

WITH [paths] AS (
SELECT
             [FullPath]
    FROM
             [ACLs]
    GROUP BY
             [FullPath])
SELECT
            [PC].[ParentFullPath],
            [PC].[ChildFullPath],
            [D].[UserGroup],
            [D].[AllowDeny],
            [D].[Permissions]
    FROM (
            SELECT
                        [P].[FullPath] [ParentFullPath],
                        [C].[FullPath] [ChildFullPath]
                FROM
                        [paths] [P]
                    JOIN
                        [paths] [C]
                            ON
                                    [C].[FullPath] <> [P].[FullPath]
                                AND
                                    CHARINDEX([P].[FullPath], [C].[FullPath]) = 1;
        ) [PC]
CROSS APPLY
(
SELECT
            [UserGroup],
            [AllowDeny],
            [Permissions]
    FROM
            [ACLs]
    WHERE
            [FullPath] = [PC].[ChildFullPath]
EXCEPT
SELECT
            [UserGroup],
            [AllowDeny],
            [Permissions]
    FROM
            [ACLs]
    WHERE
            [FullPath] = [PC].[ParentFullPath]
) [D];

---

Ultimately, if you want this code to run efficiently you'll need to normalize your schema somewhat. As long as the parent child relationship exists only by inference through string comparison this will be a relatively slow operation.

Answer 2

If you want to do it for the whole list in one go you might write an SQL expression (for example using substring()) to get_parent_path_from_child_path and run a following SQL structure. It is not clear from your question how to separate parent from child in a general case. So I am just giving you a wireframe code.

(
SELECT -- parents
            [UserGroup],
            [AllowDeny],
            [Permissions],
            [FullPath] as parent_path
    FROM
            [ACLs]
  WHERE add a filter for parents here

minus 
SELECT -- children
            [UserGroup],
            [AllowDeny],
            [Permissions],
            get_parent_path_from_child_path([FullPath]) as parent_path
    FROM
            [ACLs]
    WHERE add a filter for children here

)
union 
(
SELECT -- children
            [UserGroup],
            [AllowDeny],
            [Permissions],
            get_parent_path_from_child_path([FullPath]) as parent_path
    FROM
            [ACLs]
    WHERE add a filter for children here
minus
SELECT -- parents
            [UserGroup],
            [AllowDeny],
            [Permissions],
            [FullPath] as parent_path
    FROM
            [ACLs]
  WHERE add a filter for parents here

)