Performance issue with running long queries on both lakehouse and data warehouse

[marcoG]

I'm not sure if anyone can help me? I have a performance issue with running queries on both lakehouse and data warehouse. Specifically, the queries I run do not show results incrementally; instead, the entire query is executed first and then the results are displayed. This causes several problems, and I'll mention just one: In paginated PowerBI reports, the loading of rows during report generation is no longer displayed, giving the impression that the report generation has failed. The same query executed on any other SQL database, however, shows incremental results loading during the query execution. Does anyone know what could be causing this behavior? Thank you.

[Anonymous]

Hi @marcoG ,

Thanks for using Fabric Community.

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

[marcoG]

is there any news?

[Anonymous]

Hello @marcoG ,

Apologies for the delay in reply from our side. We haven't got any update from the internal.
Appreciate your patience.

[Anonymous]

Hi @marcoG ,

Apologies for the delay in reply from our side. We have an update from internal.

instead, the entire query is executed first and then the results are displayed

This is typically the way paginated reports need to work. Often grouping, sorting or something simple like a footer with "page x of y" means that the entire report needs to be processed in terms of what items fit onto each of pages and this can only be done once the entire resultset is available.

[marcoG]

I'll be more specific:
Why does SQL Server return some rows during query execution but Microsoft Fabric doesn't?

In general there are some operators that require all rows to come to them before they can start processing them and pass them downstream, there are other operators that are able to start streaming or pass the found rows immediately.

This can happen due to row destinations introduced by the user, by the optimizer or rather because, for some reason, a wrong plan is chosen for example due to insufficient statistics.

When queries begin returning data immediately, but do not finish immediately, it is usually a sign that the optimizer has chosen a plan to quickly locate and return some rows using operators that have a lower startup cost.

Why in Fabric, even using a connection with sql server management studio, does it never happen that the results of a long query are shown before the query is completed?

[Anonymous]

Hi @marcoG ,

Can you please share Statement ID of the query you are running against Fabric Warehouse?



We can check on our side and breakdown a query into individual operators. Perhaps, that would help explain this. 

[marcoG]

The same query takes about 30 seconds on Synapse serveless pool and the results are shown incrementally straight away while on fabric I have to wait the full 4 minutes before seeing the first row.

Statement ID: {15DAE41C-FB1B-4BCD-9122-D979C81368CB} | Query hash: 0xE208E8F7E40F146E | Distributed request ID: {B3013F99-8933-44B0-92BA-8DDED2D21911}

Msg 15806, Livello 0, Stato 1

(104709 record interessati)

9:07:49 AMTempo di esecuzione totale: 00:04:03.573

I can't send email to AzCommunity@Microsoft.com because i got an error:

Remote server returned '554 5.7.0 < #5.7.133 smtp;550 5.7.133 RESOLVER.RST.SenderNotAuthenticatedForGroup; authentication required; Delivery restriction check failed because the sender was not authenticated when sending to this group>'

Bye

[Anonymous]

Thank you for sharing the details. Let me check with internal team.

[Anonymous · Accepted solution]

Hi @marcoG ,

Once again thanks for sharing the details. I think we need to define the problem statement here:

1. You think the query is slow in general and taking too long to execute, and shouldn't take 4 mins to complete/execute from start to end?

2. Or, is the issue why is pagination not working? Why does one has to wait for all results and why can't it 

For (1), we can check if there are any inefficiencies in the query execution. 

For (2), AFAIK - pagination is not supported. What you have observed where all results must be returned is expected behaviour. We had same behaviour on Synapse dedicated SQL Gen2. Perhaps this blog may help?

Paginating results in Synapse SQL - Microsoft Community Hub

[Anonymous]

Hello @marcoG ,

We haven’t heard from you on the last response and was just checking back to see if you got a chance to last reply.

You can send us the information through email to AzCommunity[at]Microsoft[dot]com with the above details.

Subject of the email: ATTN: PRADEEP - Performance issue with running long queries on both lakehouse and data warehouse

Thread Link: Performance issue with running long queries on bot... - Microsoft Fabric Community

Thanks.

[marcoG]

I can't send email to AzCommunity@Microsoft.com because i got an error:

Remote server returned '554 5.7.0 < #5.7.133 smtp;550 5.7.133 RESOLVER.RST.SenderNotAuthenticatedForGroup; authentication required; Delivery restriction check failed because the sender was not authenticated when sending to this group>'