# Slow query on table

**URL:** <https://forum.inductiveautomation.com/t/slow-query-on-table/10941>\
**Category:** General Discussion\
**Created:** [December 18, 2015, 9:25pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941 "2015-12-18T21:25:35Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [December 18, 2015, 9:25pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/1 "2015-12-18T21:25:35Z")

</div>

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. 🙂  
 → 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). 😢

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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:")

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [December 28, 2015, 4:21am UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/2 "2015-12-28T04:21:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [December 28, 2015, 8:28pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/3 "2015-12-28T20:28:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [December 29, 2015, 3:53pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/4 "2015-12-29T15:53:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [December 29, 2015, 4:46pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/5 "2015-12-29T16:46:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [December 29, 2015, 4:50pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/6 "2015-12-29T16:50:29Z")

</div>

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:

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

is something like this:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [December 31, 2015, 6:43pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/7 "2015-12-31T18:43:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 3, 2016, 10:29pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/8 "2016-01-03T22:29:28Z")

</div>

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

Thank you very much for your suggestions ![:thumb_right:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":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… 😕

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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":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:

```auto
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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [January 4, 2016, 2:05pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/9 "2016-01-04T14:05:44Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 4, 2016, 3:48pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/10 "2016-01-04T15:48:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [January 4, 2016, 4:17pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/11 "2016-01-04T16:17:34Z")

</div>

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

```auto
{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.

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [January 4, 2016, 5:26pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/12 "2016-01-04T17:26:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 5, 2016, 9:19pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/13 "2016-01-05T21:19:33Z")

</div>

Thank you vert much Carl. I will do it.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 8, 2016, 3:07pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/14 "2016-01-08T15:07:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [January 8, 2016, 3:26pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/15 "2016-01-08T15:26:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 8, 2016, 7:05pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/16 "2016-01-08T19:05:24Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 10, 2016, 4:22pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/17 "2016-01-10T16:22:56Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [January 10, 2016, 6:05pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/18 "2016-01-10T18:05:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\
**Post date:** [January 11, 2016, 2:29am UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/19 "2016-01-11T02:29:03Z")

</div>

[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.

---

<div class="post-metadata">

**Author:** ![jruben84](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jruben84](https://forum.inductiveautomation.com/u/jruben84)\
**Post date:** [January 21, 2016, 3:28pm UTC](https://forum.inductiveautomation.com/t/slow-query-on-table/10941/20 "2016-01-21T15:28:48Z")

</div>

@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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:")