Forum Discussion
Improving performance of Point Queries in Lakehouse SQL Endpoint
Hi All,
I have a C# application that fires queries when user interacts with the application. These queries run and bring 1 or 2 rows from thousands of rows.
These queries are currently running in Azure SQL Database and we are in the process of re-pointing them to Fabric LH SQL Endpoint. (the data is coming from D365 through Link to Fabric). When we tried to compare the performance I see that Azure SQL just takes 2-3 seconds anytime the query is fired while Fabric LH SQL Endpoint takes 15-20 seconds for bringing the same data.
Azure SQL is at 3000 DTUs and Fabric LH is running at F32. I already have updated STATISTICS on the table in LH. Any idea on how to improve the query performance in Fabric LH? Due to unavailability of Query plan, we are also not able to see where exactly it takes most time.
Hopefully, someone else can chime in with suggestions for optimizing SQL Analytics Endpoint.
I agree, 20 seconds is not nice...
Have the underlying delta tables been optimized in some ways? E.g. have you run OPTIMIZE on the delta table?
Is the SQL query you're using a complex query?
Out of curiosity, have you tried a Warehouse instead of a Lakehouse?
Here are some relevant links for SQL Analytics Endpoint and Warehouse performance:
https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance
https://learn.microsoft.com/en-us/fabric/data-warehouse/guidelines-warehouse-performance
I'm wondering if it's possible to keep the data in the SQL Analytics Endpoint "warm" by regularly querying the tables (I'm wondering if the data will be kept in warm cache. Or perhaps that would only work if the exact same query was being run each time, idk).
9 Replies
Hi All,
We solved this problem using this approach: We ran the Stored procedures every 5 minutes with few params so that the data gets cached. Because of this, the next time Stored procedure was accessed from the external application it performed much better because of the cache.
We checked the utilization rate in Capacity Metrics app and we verified that it did not cause over-utilization.
In general, Lakehouse or Warehouse is not optimized for point queries. They use columnar storage (delta parquet tables) and are optimized for aggregations.
Have you tested/considered Fabric SQL Database?
(Fabric SQL Database is a preview feature, so not meant for production yet).
Hi frithjof_v,
Yes usually aggregation queries run much faster but still 20 seconds seems to be bad. In Snowflake, we have this Search Optimization Service which enhances point queries. So I am trying to see if something is similar is available.
We did think about Fabric SQL DB, but it is not yet ready for Production! And again, we might have to replicate the tables manually to the Database which means additional complexity!
Hopefully, someone else can chime in with suggestions for optimizing SQL Analytics Endpoint.
I agree, 20 seconds is not nice...
Have the underlying delta tables been optimized in some ways? E.g. have you run OPTIMIZE on the delta table?
Is the SQL query you're using a complex query?
Out of curiosity, have you tried a Warehouse instead of a Lakehouse?
Here are some relevant links for SQL Analytics Endpoint and Warehouse performance:
https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance
https://learn.microsoft.com/en-us/fabric/data-warehouse/guidelines-warehouse-performance
I'm wondering if it's possible to keep the data in the SQL Analytics Endpoint "warm" by regularly querying the tables (I'm wondering if the data will be kept in warm cache. Or perhaps that would only work if the exact same query was being run each time, idk).
