Forum Discussion
Querying a KQL database in an efficient manner
Hi,
I've to design some queries against a KQL database, in an efficient manner.
In order to obtain as good as possible performances, is it better to write these queries in the KQL language or SQL one?
Moreover, using the SQL language to query the KQL db are there any best practices to follow in order to have optimal performances?
Thanks
Hi pmscorca ,
Thanks for lbendlin's reply!
For your first question:is it better to write these queries in the KQL language or SQL oneIt is better to use KQL instead of T-SQL, becasue the query environment offers limited support for T-SQL. The following table outlines the T-SQL statements and features that aren't supported or are partially supported:
https://learn.microsoft.com/zh-cn/kusto/query/t-sql?view=microsoft-fabric#coverage
But in terms of query performance, they are about the same.
For your second question:are there any best practices to follow in order to have optimal performancesI checked the official documentation and it is recommended that you use stored functions to minimize T-SQL code and improve performance.
https://learn.microsoft.com/zh-cn/kusto/query/t-sql?view=microsoft-fabric#run-stored-functions
So I still recommend that you use KQL instead of T-SQL, unless you need to use some tool that doesn't support KQL.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi pmscorca ,
Thanks for lbendlin's reply!
For your first question:is it better to write these queries in the KQL language or SQL oneIt is better to use KQL instead of T-SQL, becasue the query environment offers limited support for T-SQL. The following table outlines the T-SQL statements and features that aren't supported or are partially supported:
https://learn.microsoft.com/zh-cn/kusto/query/t-sql?view=microsoft-fabric#coverage
But in terms of query performance, they are about the same.
For your second question:are there any best practices to follow in order to have optimal performancesI checked the official documentation and it is recommended that you use stored functions to minimize T-SQL code and improve performance.
https://learn.microsoft.com/zh-cn/kusto/query/t-sql?view=microsoft-fabric#run-stored-functions
So I still recommend that you use KQL instead of T-SQL, unless you need to use some tool that doesn't support KQL.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, thanks for your reply.
I hope that a KQL database could offer a major support for T-SQL language.
In terms of query performance, is it better using the KQL language than T-SQL one or not?
I asked the second question because T-SQL language could be required from some customers.
I've noticed that running the same KQL query or the same T-SQL query more times the execution time changes: is it a normal behaviour?
Thanks
Hi pmscorca ,
I have tested that the performance of KQL query and T-SQL query is almost the same. The official documentation does not provide much information on using T-SQL in KQL Database. I suggest you open a support ticket to get more detailed information and a dedicated Microsoft engineer will come to solve the problem for you.The link of Power BI Support: Microsoft Fabric Support and Status | Microsoft Fabric
For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Fabric Community
And to ask the engineers about your requirements to see if T-SQL query can be better supported in the future.
Best Regards,
Dino Tao
