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).
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.
Hi govindarajan_d,
Materializing the data is a good approach to improve query performance. May I inquire if this issue has been resolved?
If it has been resolved, please mark the helpful reply or share your solution and accept it as the solution. This will be beneficial for other community members facing similar issues.
Regards,
Vinay Pabbu