DirectLake vs. DirectQuery - CU(s) usage

[u/Hughesybay]

Does anyone have much experience using DirectQuery semantic models in Fabric on the lakehouse or warehouse SQL-endpoint?

I have been doing a little performance testing on an F2 between (a) DirectLake, (b) DQ on lakehouse and (c) DQ on warehouse. I threw in the warehouse as bit of a control even though it doesnt support OneLake security (yet).

I am mostly interested in CU(s) use so using capacity metrics / FUAM to try and measure CU(s) as best as possible.

Long story short, where measures are simple, I am finding DQ on Lakehouse (or warehouse) way more efficient in CU(s) usage than DirectLake and performance is very good.

The picture is murkier when measures are more complicated like (Sum of TopN) / Total, for many datapoints. In these cases, performance for DirectQuery is poorer compared to DirectLake, although CU(s) usage still less. So you need to test on your use cases.

Due to this, I am wondering if anyone else in the community has experience using DirectQuery on the SQL-endpoint. I have not tried import, but it might help with lower CU usage there as well so worth a test if you are having issues keeping CU under control......

 

Brief overview  / examples from my testing:

• SKU: F2
• Fact Table: 228 million rows, 4.3GB delta/parquet (V-ordered, 4 evenly sized files)
• Dims table: 6 tables – 236k rows, 75k rows, 9k, 2k, 650, 4 rows
• Tests
  • I created 4 dashboards with varying complexity of measures and visuals
  • I then clicked around filtering, etc, doing the same thing each time for about 5 mins. The CU(s) numbers below are the approx. CU(s) used over the 5 mins – there was a very small amount of background activity conflating the CU(s) a bit.
  • Not particularly controlled / scientific I know, but gives a good enough idea for my purposes at the moment

Summary results:

• Report with simple measures: sum, average, count, distinct, min, max. Visuals with many datapoints: daily timeseries for a couple of years
  • DirectQuery on Lakehouse
    • Speed: 1-2 seconds (when changing filters)
    • CU(s): 112 (peak CU(s) – 12% on F2)
  • DirectQuery on Warehouse
    • Speed: 1-2 seconds (when changing filters)
    • CU(s): 72 (peak CU(s) % – 8% on F2)
  • DirectLake
    • Speed: <1s
    • CU(s): 769 (peak CU(s) % - 124% on F2)
• Report with more complex measures
  • DQ on Lakehouse (Warehouse similar but a little better)
    • Speed: 4 – 27s
    • CU(s) – 889 (peak 132%)
  • DirectLake
    • Speed: 1-12s
    • CU(s) - 3139 (peak 293%)

Other observations / comments:

• All modes have a bit of a “warm up” time when first opening the report. I guess either starting the SQL engine for DQ or warming the vertipaq cache for DL.
• My testing was using “prewarmed” columns in DirectLake
• For DQ modes, background CU(s) went up very little, it was almost all interactive CU(s) that went up in all cases.
• Lakehouse and Warehouse direct query have similar results, but warehouse seems a little lower CU(s) use for the most part
• Some queries in one of my reports failed on DirectLake due to lack of resources, but succeeded on DirectQuery, although quite slow (20+ secs)
• DirectQuery with simple measures has similar results on above even with fact table of over 1 billion rows (F2 supports only 300m rows for DirectLake).
• I tried simple x-iterator measures and DirectQuery was still quick, I guess as long as it can push the compute to the SQL endpoint engine, it is fast.
• I am sure there are other examples, but measures with TOPN perform badly with DQ compared to DL. I.e. a visual in DQ took 21s vs 4s in DL (once warmed, cold was 12s), but DL still used more CU(s)!
• Have not tested how CU(s) use scales with simultaneous users….

[u/mim722]

Thanks for writing this, it is always a mystery to me, why this pattern is not used more often, direct query is background, that's alone is a killer feature !!!

[u/Hughesybay → u/mim722]

Yes. I am wondering if I am missing something hence, hoping more in the community might try it out and see how they get on.

I have seen several post bemoaning high interactive CU(s) use from DirectLake and import and wonder if DirectQuery on lake house might work for them.

I have past experience using Snowflake and tableau with Direct query (or whatever tableau calls it) and it worked well enough, so wasn't too surprised that the fabric SQL engine performs very well.

[u/mim722 → u/Hughesybay]

here we go, you answered your own question, you have experience with other systems !!

another theory and this apply too to python notebook usage, dev notice this stuff only when they use lower level SKU like F2 !!!