Matt Allington suggests pre-sorting results to reduce load in Power Pivot:

Imagine you have 50,000 products in your data table and you have 50,000,000 rows of data. Power Pivot will take the first 1 million rows it comes to (1 segment worth), work out how to sort and compress the columns, and then compress the data into a single segment before moving to the next 1 million rows it comes to (in the order they are loaded). When it does this,

it is highly likely that every product number will appear in every single segment – all 50 segments.If we assume an equal number of product records for each product (unlikely but OK for this discussion), then there would be1,000 records for each product spread throughout the entire data table,and each and every segment is likely to contain all 50,000 product IDs. This is not good for compression.

This is an interesting result and not something I would have thought intuitive.

Kevin Feasel

2016-02-09

Query Tuning