Forum Discussion

pmscorca's avatar
pmscorca
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

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 o...
kql database
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi pmscorca ,

    Thanks for lbendlin's reply!

    For your first question:

    is it better to write these queries in the KQL language or SQL one

    It 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 performances

    I 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi pmscorca ,

Thanks for lbendlin's reply!

For your first question:

is it better to write these queries in the KQL language or SQL one

It 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 performances

I 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.

  • pmscorca's avatar
    pmscorca
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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