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

jambrose's avatar
jambrose
Frequent Visitor
9 years ago

I gave this a shot, and it didn't appear to make a significant difference in the memory load or execution time.  My understanding is that having it nested inside the SUMMARIZE implicitly filters in some similar manner anyway.  Or at least, the results I get (from either version) find the same value as desired - the top value out of only the rows with the given ID.  Thanks for taking a stab though!