Forum Discussion

isaachimanga's avatar
isaachimanga
Regular Visitor
9 years ago

Active Directory Users Query is Slow

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..

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    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"
    • isaachimanga's avatar
      isaachimanga
      Regular Visitor
      9 years ago

      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