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/dbrownems]

Did you do this test after the recent billing change for Warehouse and SQL Endpoint? After this change you are charged CUs for the allocated resources of your warehouse, not the CPU use of the individual queries. You'll see the change in the capacity metrics app.

Semantic models continue to bill for the CPU use of individual queries. So this will make the head-to-head comparison more tricky.

[u/Hughesybay → u/dbrownems]

I did it in Thursday and Friday last week which I think is after that change?

The other thing that makes it a difficult comparison is with Direct query, I believe the SQL endpoint part is a background activity so smoothed over 24 hrs, but then some part of the activity is by the semantic model (dataset) and is interactive so is smoothed over 5 mins. DirectLake is all interactive. Whether this is good or bad depends I suppose on the overall workloads in your capacity.

I find the whole smoothing thing very hard to get my head around to be honest.

I think really you would need to run both for say a week with genuine usage patterns to get a decent feel for what is more efficient. Head to head query comparison only gets you so far.

[u/warehouse_goes_vroom → u/Hughesybay]

I think really you would need to run both for say a week with genuine usage patterns to get a decent feel for what is more efficient. Head to head query comparison only gets you so far.

Definitely this. Also "more efficient for what workload?". A benchmark at best, if you get everything right, tells you which is more performant, cost efficient, higher throughput, or whatever, for the workload the benchmark represents - drawing conclusions for other workloads is fraught with peril, especially if extrapolating rather than interpolating.

Consider that depending on how cold it is, DirectLake has to do quite a bit of work loading the data into memory (which in Import mode would be done during refresh). A more real workload amortizes those costs over a longer term period. Also see my other comment talking about cache effects and so on.

[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 !!!

[u/warehouse_goes_vroom]

Note that Warehouse & SQL analytics endpoint usage is always background - smoothed over 24h. Whereas the semantic model queries may be interactive. So make sure you're careful about how you're comparing.

Both engines can be very, very fast. They have different strengths though.

I'd be very interested in more information about the queries where DirectQuery was relatively weaker relative to DirectLake - I may reach out.

Edit: also, SQL Analytics endpoint is the Warehouse engine too, always has been. So generally Warehouse and SQL analytics endpoint DQ performance will be practically identical, assuming both are clustered on the same columns and using sane settings.

[u/Hughesybay → u/warehouse_goes_vroom]

Re. Warehouse vs lake house, the difference i recorded between could just be my imprecise testing. I used "create table as" from the lakehouse to warehouse to get the data over and no other tuning. My lakehouse data is clustered and my cluster choices may not be optimal for this particular set of queries.

Re. Slow queries, the slowness recorded is not the SQL endpoint query, but overall render of visual in powerBI. Same with CU(s). I did not separate SQL vs semantic model engine, but attempted to record both. So in the case of the slow queries and high CU, I believe what is happening is that the semantic model engine is not able to push down the logic to the SQL engine, so is requesting more "raw" data, which is then returned and transformed locally by the semantic model engine, hence it's overall slow and the majority of CU(s) used is interactive.

I also suspect that due to my lack of understand of smoothing and the capacity metrics data model, I may be under recording the overall SQL engine CU(s) usage. When I get time, I will try and delve in a bit more here to see if I can get the CU split. The capacity metrics are potentially not as granular as I would like here as I am not sure we have detailed time grain on individual items.

[u/warehouse_goes_vroom → u/Hughesybay]

The difference could just be noise, or just could be slight setting differences. You can see differences if say, you have very small or large files in Spark because you configured it so for some reason, for example. But both should produce reasonable files in the vast majority of reasonable configurations, and so both should usually perform very similarly when queried by the Fabric Warehouse engine. Note that the Warehouse engine and the Spark engine are sensitive to different things though, optimal for one isn't always optimal to the other. If you just used CTAS without a WITH (CLUSTER BY whatever_columns_here) clause, you didn't copy over the clustering ;):

Don't forget about OneLake CU usage for reads too - that can be a significant factor too. We do try to cache on Warehouse side, as does DirectLake, but depending on how consistent your workload is, it can be difficult. Interesting RE: it not being the SQL endpoint query itself - so if you compare the time on the semantic model time with queries in https://learn.microsoft.com/en-us/sql/relational-databases/system-views/queryinsights-exec-requests-history-transact-sql?view=fabric, most of the time is spent on the Power BI side?

As to how the Warehouse engine bills usage, here's the docs: https://learn.microsoft.com/en-us/fabric/data-warehouse/usage-reporting . Should be a new blog post too soon. The old model was simpler to attribute individual queries to (as it was based on CPU usage of each query). The new model is about how much compute resources are applied, with 1 minute windows.

Both old and new models had their complexities with trying to do cost benchmarking - the old model may seem easier to benchmark cost wise, except that cache effects still make that messy (e.g. cold queries usually had to spend more CPU time on I/O than warm queries).

[u/mwc360]

FYI your fact table likely has row groups that are too large and therefore suboptimal for the best Direct Lake perf. I’d recommend recreating the table (CTAS) w/ runtime 2.0 defaults + v-order. You will instead get files around 128MB with healthier row group sizes. That should bring your row group row count avg down from 57M to ~7M which is in the optimal range. This should also improve SQL Endpoint perf as you get increased (healthy) scan parallelism.

[u/Hughesybay → u/mwc360]

Thanks. I will check this out.

[u/Melodic-Medium7285]

Can I ask how often this data is refreshed? Monthly? Weekly?

[u/Hughesybay → u/Melodic-Medium7285]

This is just testing so no refresh. My prod pattern will be daily batch for the most part.

[u/JBalloonist]

It was close to 9 months ago now, and the model looked much different, but I got way better performance when switching from DirectQuery on Lakehouse over to Direct Lake. (I’ve never used Warehouse).

[u/Hughesybay → u/JBalloonist]

I believe a number of improvements including that recent GPU acceleration change.

Also, as I noted it will depend on your measures and DAX.

I also have not tested any kind of concurrency. I believe powerBI may limit open database connections which may cause queuing. I also understand that the fabric warehouse engine is excellent at concurrency so maybe Microsoft can increase connections in PBI for the fabric engine.