Forum Discussion

jambrose's avatar
jambrose
Frequent Visitor
9 years ago

Improving Summarize Efficiency

I'm trying to pull information from a large (~20 mil records, 48 colmuns) table, and I have a working method, but it's on the verge of what my machine can handle, and so I'm worried it won't scale up as I potentially need to expand the data.  What I currently use is:

 

Summary Table = SUMMARIZE(Big_Table, Big_Table[ID], "First Value",
     MINX(TOPN(1, Big Table, [Sort1], ASC, [Sort2], ASC, [Sort3], ASC), [Value]) )

 

So essentially, from records with the same ID, I need to pull a single value based on the first record after sorting by three different columns.  There are at most around 6000 records under one ID.  The machine can do one of these operations at a time, but if I try to do 2 (for example, pulling both first and last value) in the same Summarize, I get out of memory errors.  The machine has 16gb RAM, and I've checked to confirm it's actually capping out.  I am new to the system, so bear with me if this is a dumb question, but why is this a taxing operation in the first place?  I could understand a long processing time but not the huge memory load compared to the ~3gb size of the file.  Second question is - is there a more efficient way to accomplish what I'm trying to do?  I may need to do this on an even larger set of records, so any improvements that scale up are welcome.  The three sorting columns are a date, an alphanumeric, and an integer, and I would prefer to avoid adding another column unless absolutely necessary.

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Generally any time you see a MINX it throws up warning flags around performance but in your case, you are only returning a 1 row table to it so it only has to iterate over 1 row. So, my guess is that it is all of the sorts you have going on. I wonder if it might be better for you to pull this in via another query and do what you are doing in DAX in M instead.

    • jambrose's avatar
      jambrose
      Frequent Visitor
      9 years ago

      I'd be open to trying it that way, but I haven't so far been able to find a way to do the same process (pull a top value based on sorting 3 other columns) on the query end of things.  Any ideas?

      • dedelman_clng's avatar
        dedelman_clng
        Community Champion
        9 years ago

        (CAVEAT: This is just an observation - I don't have much experience with SUMMARIZE and TOPN)

         

        Have you tried doing a FILTER on BigTable inside the TOPN function?  It looks like the TOPN is looking over the whole table, not just the part of the table that has the ID you are looking for.  And if it's anything like SQL, the TOPN function has to do all kinds of distincts and interations in the execution plan, which is probably what is chewing up memory.

         

        So something like this:

         

        Summary Table =
SUMMARIZE (
    Big_Table,
    Big_Table[ID],
    "First Value", MINX (
        TOPN (
            1,
            FILTER ( Big_Table, Big_Table[ID] = EARLIER ( Big_Table[ID] ) ),            [Sort1], ASC,
            [Sort2], ASC,
            [Sort3], ASC
        ),
        [Value]
    )
)

        If you get an error that there is no "EARLIER" to be referenced, try wrapping the MINX function in CALCULATE.

         

         

        Hope this helps.

        David