Slow query on table

Hi,

I have a question for you guys. I have a table and this table has a SQL binding.
This is what happens:
→ The table changes dynamically its background (mapped to a column). That’s okay. :slight_smile:
→ The polling mode of the binding is 1 sec.
→ When I perform the query inside the Database query browser, the query goes fast (less than 1 sec). But when I perform the query inside the binding of the table, the query goes slow (sometimes about 10 sec). :cry:

I don’t know why updating on table is slow. Any suggestion or comment will be appreciate.
Thank you.
Have a nice day guys! :thumb_right:

How are you measuring the query time when it is going through the binding?

Can you add a few screenshots of your property binding menu and other helpful windows It will be much easier to see whats going on. 10s doesn’t sound right. If it doesn’t take long in the DB query browser, it shouldn’t take long anywhere else.
There are many helpful people on here but it’s hard to help with limited information.

Thank you very much.
Of course, I attached the screen shoots. One of them, is the ‘Database Query Browser’, in this window we can view that the spend time for perform the query is about 0.026 s.

For the other hand, this is the table that uses the query

And my sql query is: (for the table ‘Construccion’ (3))

SELECT Descripcion, Trim,
CASE
WHEN Trim > 100 THEN ‘> 100’
WHEN Trim < 100 THEN ‘< 100’
WHEN Trim = 100 THEN ‘= 100’
END AS ‘EdoTrim’,
Add_ON, VReal AS Real,
CASE
WHEN VReal < Obj_PSD THEN ‘<’
WHEN VReal > Obj_PSD THEN ‘>’
WHEN VReal = Obj_PSD THEN ‘=’
END AS Estado,
Obj_PSD AS PSD, Obj_TLX AS TLX
FROM adhesivos_cons_PE08({Root Container.Root Container.ControlProceso.Ollas.Addons.presentacion.selectedValue})

I can understand that sometimes, some latency can be exist and the network is used too much. Sometimes the table refresh takes few seconds, and sometimes about 10 seconds.

Thank you very much for any suggestion or help.

Thanks for the screenshots, I will have a look and hopefully have an answer for you soon. One quick suggestion, when putting a code in your post use the Code option It basically puts whatever you have in between the two blocks in a nice separate block, It looks clean and also easier to read. Just a suggestion.
Alright, Let me see what I can figure out from your pictures.

jruben84,
So your query gets executed whenever you change the value in the dropdown right? So basically when you change the drop down, it takes about 10 seconds for the actual data in the table you are showing to change?
One more thing so this:

FROM adhesivos_cons_PE08({Root Container.Root Container.ControlProceso.Ollas.Addons.presentacion.selectedValue})

is something like this:

FROM adhesivos_cons_PE08(Some Integer)

In your DB browser (I can’t see the complete code so I assume since you said drop down returns an Integer.

jruben84,
I’ve tried a few big tables (100K+ rows) and still don’t see any delay in updating my table values, how do you actually measure the time it take for your table to update? from when you change the value in drop down to when the data actually changed in the table?
Also is it possible to you to take a screen shot of the SQL code you have in the DB browser?
That’s if your problem still exists. Thanks.

Happy New Year!! I wish you the best, all of you guys!

Thank you very much for your suggestions :thumb_right: . Let me apologize, nowadays I’ve been some kind of crazy with some activities here in my work, because of the end of year and start of year… :confused:

And yeah, the drop down list returns an integer.

Well, when I change the value in the drop down list, it changes the query and the query executes.
At this point, “I meausure” the time just by viewing the table, when the table’s values changes I notice that the table is updated. I don’t know how could I measure this time… well I figure out, maybe with a timer using “table’s properties loading” or something. :scratch:

I’m thinking in some configuration in gateway related to number of connections for the DB for get fast response from SQL Server, but I can’t remember where is, what do you think?

And the code is:

SELECT Descripcion, Trim,
CASE
WHEN Trim > 100 THEN '> 100'
WHEN Trim < 100 THEN '< 100'
WHEN Trim = 100 THEN '= 100'
END AS 'EdoTrim',
Add_ON, VReal AS Real,
CASE
WHEN VReal < Obj_PSD THEN '<'
WHEN VReal > Obj_PSD THEN '>'
WHEN VReal = Obj_PSD THEN '='
END AS Estado,
Obj_PSD AS PSD, Obj_TLX AS TLX
FROM adhesivos_cons_PE08(6) -- this is the integer returned by the dropdown list

Thank you very much!! :prayer:

jruben84,
I don’t think you need to change anything in the gateway because when using the DB browser the query execution happens almost instantly, if there was any connection issues or slowness you would see it there as well.
How big are these tables you are grabbing data from?

Hi Mr.K001:

The first one has: 31316 rows, and the second one has 12941. I think is not too much.

I isolated the table and create a new window. I bind the ‘Running?’ property of a timer to the ‘properties loading’ of my table. So I can measure more accurately the spent time. The results are in the screen shoot. Is too fast.

So, I think the following:

  • In the original window (not this one), I have a bunch of SQL querys and bindings. Maybe this bindings are performed in order, for example by a queue and the query on table executes is the last executed. Maybe for this reason it takes too long. If this is true, I think that It would be useful has some kind of ‘priorities’ for perform the queries.

What do you think?

Regards.

jruben84,
Obviously a lot is going on in that window but I still don’t think it should make that big of a difference. have you tried to do the same thing you did with the timer in that window and see if the numbers are different in loading time in in new window vs. old window?
20k and 30k is really not a lot at all. you shouldn’t have any issues. are you only grabbing data from one table at a time when you change the drop down? is there any other component in some other window querying data from the same tables you are using?

Also in

{Root Container.Root Container.ControlProceso.Ollas.Addons.presentacion.selectedValue}

Any special reason you have two Root Containers?
also how many values are in the drop down?
This is what I currently am working with. My query changes based on all 4 drop downs, the calendars set the time range and the other two one is changes shifts(A,B,C) and the other is different Operations(this case 12 of them) and I still don’t have lag issue.

Maybe we should trouble-shoot this from the gateway side of things:

Turn your database connection’s slow query log threshold setting to zero. Now you’ll get a log message warning you that every single query is “slow”, but more importantly it will tell you the query and the time it took to execute. Perhaps this will shed some light on the difference between the query your table is running and the query that you’re running in the query browser.

Thank you vert much Carl. I will do it.

Mr.K001,

Well. I don’t know the exactly why the system was developed in this way. I supposed that the reason was ‘hide’ a bunch of controls, for example when the user selects a different window. I know that this is not the best way to do it. Maybe it would be better if a docked window is used.

So another thought, if you have time on the side, maybe you should try to redo that window all together from scratch and perhaps you can implement things in a better way.
So when you change that drop down menu, only one SQL query gets executed right? nothing else is happening in the background? by that I mean is there some other component trying to grab data from the same table your drop down is trying to grab data from.

@Carl I’m analyzing the queries in the console.
@Mr.K001, Right. in fact different ‘SELECT’ queries are running at the same time. At least three. But let me check exactly how many queries are executed.

@Mr. K001

Well, a few ‘selects’ are executing when the dropdown changes (no inserts or updates). And sure! I need implement a better way. I don’t know why several root containers are used. I supposed that is some kind of ‘grouping’.

Thank you very much

I’m not saying the multiple queries causing the issue but it’s possible. In the DB browser when you running the query there is nothing else going on and you are talking directly to the DB in the window you have a lot more going on. Its tough coming up with a solution not knowing how exactly the that window was constructed, if I was you, I’d start creating that window from scratch and try to implement better ways of doing things so components don’t do too much at the same time when it comes to querying from a DB and see if that helps.
obviously when you started working on this someone already had the windows setup but if you are in charge of it now I’d say try to do it your way because the data is not changing, your DB is intact, but you could maybe do things in more efficient ways. good luck.

[quote=“jruben84”]
-> The polling mode of the binding is 1 sec.
[/quote]Are you looking to refresh you table’s data every second or only when the value in the dropdown list changes?

If you only need to update the table’s data when the dropdown list changes then you would turn polling off. Since your query is referencing the value of the dropdown list, the query will execute everytime the the dropdown list’s value changes even if polling is turned off.

@Mr. K001 You have right. I’m thinking in implement in a different way this window.
@Pat, sure and the reason because I’m using the polling mode to absolute it’s because the table it’s updated every certain time.

Thank you very much for your comments and suggestions!!
I think that I will implement again this part of the window.

:thumb_right: