none
VERY slow UI in file with ~100 Queries RRS feed

  • Question

  • Hi All!

    I'd like to discuss the performance of PQ UI.

    In case of high number queries it starts to work unsatisfactory slow:

    1. UI blocks any activity for tens of seconds e.g. in case of Adding column. Please don't recommend not to use the UI, just because sometimes I can click on the ribbon occasionally. It is not a great idea to force us to play in minesweeper with the tens of seconds of waiting in case of wrong mouse click.

    2. If I add any step, or change it, UI blocks for time up to 10 seconds. I just cannot do anything.

    3. Please start to use the results of previous steps in the next steps! Now you calculate every step in Query independently one from another. So if I have N steps of a Query, PQ will make this number of calculations: S1 * N + S2 * (N-1).. Sk * 1. Due to that working with UI in tens of times slower in comparison with execution of Query not from PQ UI.

    Monday, July 1, 2019 8:20 AM

Answers

  • Hi Andrey. It's not entirely clear to me what the issue is that you're seeing when tweaking the steps as you describe above. Can you clarify (or maybe share a simple repro file with two equivalent example queries, one slow, one fast)?

    Thanks,
    Ehren

    Friday, August 2, 2019 11:38 PM
    Owner

All replies

  • Hi Andrey. Are you by any chance doing queries against a non-folding data source (like a bunch of text or csv files)? It would be good to know the kind of data you're working with, as well as the source of it (SharePoint, local, etc.).

    Ehren

    Thursday, July 11, 2019 8:17 PM
    Owner
  • All the queries are against local excel and csv files. No any sort of files with thousand of rows, just a few hundreds or less.

    But it seems that the main source of a problem is "deep" evaluation, not the data size. If I have several queries following each other, and every of them has 10-30 steps, all starts to be very slow. And not because of evaluating data, but because evaluating expression themselves. So the situation is that I don't see any requests to files in the right low corner for several minutes, and at that time PQ uses only 3 CP cores out of 6. And as soon as evaluation of expression is over, and PQ starts to work with data themselves as I see it in the right low corner, PQ starts to use all the 6 cores.

    And of course the problem is partly because you launch every step of a Query like a totally separated Query, not using results of the previous steps at all in UI (we discussed that earlier when discussed Table.Buffer). And it seems that situation with expression evaluation is even worse. In many cases, if I manually interrupt Refreshing after any change in M code, and manually re-launch it, I immediately see the results of the query. But if I just wait the end of Refresh without manual interruption, the evaluation of result in PQ may take up to several minutes or more than 10 minutes. I think that's because you reevaluate the whole expressions in PBIX file after any change in any query.

    So now I constantly stop Refresh and launch it again, guys. It's more than annoying.



    Monday, July 22, 2019 4:42 PM
  • Hi Andrey. One thing you could try would be to disable background analysis. If your queries are highly interdependent, this will reduce the number of background evaluations that are triggered when you make changes to a given query.

    Ehren

    Monday, July 22, 2019 9:58 PM
    Owner
  • Hi Ehren!

    Thanks for your answer.

    Do you mean "Allow data preview to download in the background"?

    I tried it, and it helped with UI responsiveness, and now there is no reason to stop - start refreshing, because the speed of calculation does not change if I do it. At the same time some of queries started to refresh quicker, while others not.

    In general, turning off "... background" option is better, while it doesn't solve the problem of big pbix.

    For example, I still see the strange thing. Among others, I have a query with just 5 steps. Even totally all the steps are a very simple evaluation, and even if PQ UI refreshes every of the step in the query simultaneously and indepenently, it must work for a second in order to refresh every of the steps and the whole query.

    But it takes 45 secs in order to just refresh it (I don't do any changes in M code or in data before the refresh!).

    If I put the step other than the last in "in" section, like this:

    let s1=.., s2=..,

    fakeStep=nothing in s2

    "Applied steps" on the right pain shows just the name of the Query, and - refreshing of the whole query takes just a second, like it should be.

    The Query is at the very beginning of data handling pipeline (it reads source file).

    If I cut all the next steps from the PBIX (so the PBIX starts to be a very simple one), the Query refreshes during a second either without doing the "fakeStep" thing.

    So in any case it is not the problem of the Query. It is problem of organizing the things inside PQ.

    Please could you say why is it happens, and what may be done on my side in order to improve the refreshing time in PQ?


    Saturday, July 27, 2019 9:38 PM
  • Hi Andrey. It's not entirely clear to me what the issue is that you're seeing when tweaking the steps as you describe above. Can you clarify (or maybe share a simple repro file with two equivalent example queries, one slow, one fast)?

    Thanks,
    Ehren

    Friday, August 2, 2019 11:38 PM
    Owner
  • If you don't make any changes to a query, PQ won't load all the previous steps but get the table from preview cache. However, pressing "refresh" initiates a proper refresh instead of taking the preview table from cache. What you are likely seeing with "fakeStep=nothing" is that when you make such a change, you get the cached preview from the step before fakeStep. 

    If you were to clear cache and try with fakeStep again, it's likely that it will take the full 45 seconds

    Monday, August 5, 2019 8:47 AM