WebDialog: extremely slow query on Linux

  • Xojo web application (2023r4 or 2024r11)
  • MS SqlServer (2019 or 2022) on Ubuntu 22.04

a very strange thing happens to me.

a query (which extracts max 200 rows from a table that contains about 150000 rows) runs with good (or at least decent) response times using SqlCommandMBS, either in debug mode (macOS) or with the Linux executable, IF within a WebPage or within a Container.

If, on the other hand, the query is executed within a WebDialog, in debug mode the response times are acceptable, BUT from the compiled Linux application there are no results until the timeout (about 10 minutes).

has anyone had similar experiences or are there any suggestions?
thank you

I add some information:

  • the query is not complex at all: with SSMS or other Sql clients, it ends in fractions of a second.
  • even the application compiled for macOS (Intel, in my case) works normally.
  • in conclusion, the trouble only affects the WebDialog on Linux, but the failure stops the entire application flow, in production environment.

thanks for your attention.

That’s very strange, I can’t see why a WebDialog could be affecting a database query. Something you could try is fetching the data using a WebThread, and then displaying the result from its UserInterfaceUpdate event.

1 Like

Fwiw I never noticed a difference in speed with selects in webdialogs. I’d suggest turning the profiler on and place startprofiling and stopprofiling instructions around the selects and see…every access to my PG16.3 server (which runs in the same machine as the webapp) takes around 0.1 to 0.3 seconds. I also dabbled with executing queries in Webthreads as Ricardo suggested but didn’t see a great speed up.

1 Like

Yes, create a sample code with a SQL dump of a sample DB used on it, allowing Xojo to see the case and @Christian_Schmitz to replicate the problem. Report the issue with the sample. Wait for a response.

1 Like

thanks for the suggestions.
I have several webdialogs, structured the same way (by the way, with a listbox and a timer that I use to display a progresswheel during the execution of the query), which obviously refer to different tables.
my problem concerns (so far) only one webdialog.
probably because the number of rows and columns in the table is relevant.
no problem with the other webdialogs.
as I wrote before, the strange thing is that the anomalous behavior occurs only when publishing to Linux.
if I am doing something wrong, so far I have not detected it.
I did several tests, before and after creating the topic.
I will update you.

P.S. I didn’t want to create this topic, because I know that if I had read similar things (written by another person) I would have a hard time believing it…

Your problem seems tied to some blocking operation + timers. Something is in a wait state and your timer should fire at some point and can’t, and who knows if something blocked a chain of expected events. Events that In a Mac the event loop can process things in some wait states at specific times and in other OSs it may not, and to make it worse, it may be tied to a specific design using specific DB driver for a specific OS (Linux). :man_shrugging:t2: How to observe what’s going on will need a sample.

How are you displaying the results? Is it a WebListBox?

@Ricardo_Cruz yes, the results are in a WebListBox (8 columns: name, surname, date of birth, address, etc).
all the other WebDialogs (the ones that don’t cause me any problems) have a WebListBox with 2-3 columns (code, description).

thank you @Rick_Araujo

it’s not easy for me to create a sample (webapp+backup of the anonymized MSSQL DB).
plus the whole package (including the DB) should be installed on Ubuntu.

I would add that:

  • the Windows executable does not create any problems
  • the MBS SQL plugin uses the same drivers for macOS and Linux (FreeTDS ODBC)

to summarize, everything works smoothly on macOS, Windows and even in debug mode.
the problem is only on Linux.

I also remember that the same structure (controls, methods and Sql query) works normally on Linux as well, if executed in a WebPage or in a WebContainer.

I’m beginning to believe that the cause is either the WebTimer or the WebListBox. or more likely both.

because it’s the only WebListBox with 8 columns (in all other WebDialogs the WebListbox has 2-3 columns).

But didn’t you initially say that is is the query itself that’s slow? Why then are you now suspecting the Weblistbox or the Timer? Maybe you need to make more measurements to understand what’s happening.

it is true.
I thought the query was slow (within the webapp), but I had also pointed out that executing the same query, with SSMS or other Sql clients, ended in fractions of a second.
after further testing (and also thanks to your suggestions), I shifted the focus to the timer and to the listbox.
always keep in mind that my problem is only on Linux and only with a specific webdialog, for now.

If you’re showing a lot of rows in a WebListbox, give it a try to using a WebDataSource. It will work much better.

2 Likes