Forum Discussion
Improving performance of Point Queries in Lakehouse SQL Endpoint
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).
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).
Hi frithjof_v,
Thanks for your quick response!
These tables are replicated using D365 Link to Fabric and they are OPTIMIZEd internally by process (meaning no small files).
We did not try Warehouse because of the copy process involved again. Theoretically, LH and WH almost operates the same way (Delta Lake storage and STATISTICS). May be the engine in WH might be different!
SQL query is not that complex. It has a filter and 2-3 joins on tables.
Do you have many Lakehouses and Warehouses in the same Workspace?
Having fewer SQL Analytics Endpoints and Warehouses in a Workspace is beneficial for performance, according to these docs: https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance
Optimally just one Warehouse or one SQL Analytics Endpoint per workspace, for performance.
Hi frithjof_v,
Yes. We just have one LH and one WH (for storing data that has been transformed using complex SQL Queries).
If we don't have any other option, the best approach for us would be to try materializing the data that the SQL query produces and see if it atleast brings the query time down. But the SQL query is very simple. A filter is passed from application to the SQL Query which reads that record from a table and joins it with 2 more tables.