Querying a KQL database in an efficient manner

[pmscorca]

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

[v-junyant-msft · Accepted solution]

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]

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

[v-junyant-msft]

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

[lbendlin]

SQL to Kusto query translation - Kusto | Microsoft Learn

[pmscorca]

Hi, thanks for you reply but it does't answer to my question.

[lbendlin]

The answer is: Always use KQL

[pmscorca]

Hi thanks for your reply. Have you matched the execution time between a KQL query versus the corresponding SQL query?

Moreover, I've posted a second question.

[lbendlin]

For your second question: 

T-SQL - Kusto | Microsoft Learn

Best practices for Kusto Query Language queries - Kusto | Microsoft Learn

[pmscorca]

The second question is this: using the SQL language to query the KQL db are there any best practices to follow in order to have optimal performances?

In order to help the community it is preferable to give a reply more discursive and not too concise. Thanks