heavy performance regression in preview

[Rdataflow]

Describe the bug
following the merge of #1285 there are some heavy performance regressions observed
comparing the response time for this cube, there is a serious regression

• ~1s INT https://int.visualize.admin.ch/en/browse?previous=%7B"order"%3A"SCORE"%2C"search"%3A"imp"%2C"includeDrafts"%3Atrue%7D&dataset=https%3A%2F%2Fenvironment.ld.admin.ch%2Ffoen%2Ffab_ahst_exp_imp%2F1&dataSource=Int
• ~60s TEST https://test.visualize.admin.ch/en/browse?previous=%7B"order"%3A"SCORE"%2C"search"%3A"imp"%2C"includeDrafts"%3Atrue%7D&dataset=https%3A%2F%2Fenvironment.ld.admin.ch%2Ffoen%2Ffab_ahst_exp_imp%2F1&dataSource=Int

To Reproduce
Steps to reproduce the behavior:

1. Go to https://test.visualize.admin.ch/en/browse?previous=%7B%22order%22%3A%22SCORE%22%2C%22search%22%3A%22imp%22%2C%22includeDrafts%22%3Atrue%7D&dataset=https%3A%2F%2Fenvironment.ld.admin.ch%2Ffoen%2Ffab_ahst_exp_imp%2F1&dataSource=Int&flag__debug=true
2. observe it takes ~60s to load the preview
3. open the debug panel and preview GQL query to see the slow SPARQL query

Expected behavior
equally fast preview like on INT, i.e. near ~1s

Environment (please complete the following information):

• Visualize environment and version: TEST - after merge of ~1285

Additional context

• slow SPARQL query: https://s.zazuko.com/yx4AHz

[Rdataflow]

FTR: fixed by db optimize on INT by VSHN

[Rdataflow]

FTR: same story again on TEST v9.2.1 now with a new dataset.

• Visualize https://test.visualize.admin.ch/browse?dataset=https%3A%2F%2Fenvironment.ld.admin.ch%2Ffoen%2Ffab_ahst_exp_imp_pipeline%2F1&dataSource=Test
• SPARQL https://s.zazuko.com/3yU5YAb

db optimize last night didn't help for this case though. 😟

[bprusinowski]

Hi @Rdataflow, I just opened two links you shared (Visualize TEST and LINDAS TEST) and observed the following loading times:

• Visualize: 21s,
• SPARQL: 19s.

It looks like the performance is the same for both sources. Can you confirm this is also a case on your side?

[Rdataflow]

we need to reopen - issue revives with new cube

• see https://test.visualize.admin.ch/browse?dataset=https%3A%2F%2Fenvironment.ld.admin.ch%2Ffoen%2Ffab_ahst_exp_imp_pipeline%2F1&dataSource=Test
• missing #pragma join.hash off is the cause for this
• this time db optimize didn't help
• x-ref https://control.vshn.net/tickets/SBAR-1059

[bprusinowski]

Thanks for clarifying @Rdataflow. It looks like the reason for #1285 was to fix some broken cube previews. When checking the broken cube preview from #1285, it's still the case that we have a cartesian product generated when having #pragma join.hash off applied.

Technically I could just hard-code to always take the first ten observations, even though the SPARQL query should already return only that. Is there no hope for a proper fix on the database level to avoid workarounds on our side (by fixing the performance we introduce another bug that's not really on our side)?

[Rdataflow]

@bprusinowski can you share some example queries where you get more results than expected?

[bprusinowski]

Sure, this is a query that made us introduce #1285 in the first place: https://s.zazuko.com/6AcHrD

I am only aware of this one example at the moment, but can't guarantee there aren't more cases like this 👀

[Rdataflow]

I asked VSHN to clarify how to fix the observed 1000 results...

[bprusinowski]

Great, thank you! 💯

[Rdataflow]

@bprusinowski

• Plan A: that's being reworked anyway per https://gitlab.ldbar.ch/bafu/visualize/-/issues/579
• Plan B: if not - as per https://control.vshn.net/tickets/SBAR-1059 we need to reintroduce #pragma join.hash off again for previews...

cc @ClaudioDiGallo

[bprusinowski]

Closing as we have rewritten the preview query completely.