topic Re: Active Directory Users Query is Slow in Developer

Active Directory Users Query is Slow

isaachimanga — Fri, 30 Sep 2016 15:26:28 GMT

The query below takes 30-60 seconds for 10k users, consuming a lot of processor and network bandwith while it runs. Is there a faster way to write this query? I suspect that I am downloading the entire set of tables in #"records" but I am not sure how to select the user table directly.

let
    Source = ActiveDirectory.Domains("domain.com"),
    #"records" = Source{[Domain="sub.domain.com"]}[#"Object Categories"],
    #"select user table" = #"records"{[Category="user"]}[Objects],
    #"get principal name" = Table.ExpandRecordColumn(
        #"select user table", 
        "top", {"msDS-PrincipalName"}, 
        {"Principal Name"}),
    #"filter principal name" = Table.SelectColumns(
        #"get principal name",
        {"Principal Name"})
in
    #"filter principal name"

Thanks in advance..

Re: Active Directory Users Query is Slow

v-chuncz-msft — Tue, 04 Oct 2016 03:52:46 GMT

@isaachimanga,

Based on my research, it seems that Query Folding is not supported. Try to change the sequence of the last two steps and check if it helps.

let
    Source = ActiveDirectory.Domains("domain.com"),
    #"records" = Source{[Domain="sub.domain.com"]}[#"Object Categories"],
    #"select user table" = #"records"{[Category="user"]}[Objects],
    #"filter top" = Table.SelectColumns(
        #"select user table",
        {"top"}),
    #"get principal name" = Table.ExpandRecordColumn(
        #"filter top", 
        "top", {"msDS-PrincipalName"}, 
        {"Principal Name"})
in
    #"get principal name"

Re: Active Directory Users Query is Slow

isaachimanga — Tue, 04 Oct 2016 19:00:18 GMT

Thanks for the suggestion, unfortunatelly it didn't appear to load any faster, or if it did the difference smaller than the variation in just running the same query twice.

Isaac