Queries don't return data 100% of the time

Ignition
1

I have a window that has 2 tables and a Pie Chart, the 3 have SQL Query bindings. The data is on an MSSQL server located elsewhere on the network. The polling mode on all 3 is ‘off’. Problem is, when opening the window, all 3 do not return the requested data 100% of the time. I put a refresh button on the screen and pressing it a couple times generally does the trick. I assume it is a timing issue.
Any suggestions?

2

Those queries should run. To check you can save the window with the properties cleared out (datasets with no data, empty strings or -1 numeric values) to see if data comes through when the window is opened.

3

Yeah they should certainly run. There might be something subtle going on with various interconnected bindings? We might need to see the window to give you a definitive answer.

4

As an update to this problem, it is still occurring. But it is server/gateway dependent. I have one window in particular that has 2 tables and 1 chart, hence when it opens, the 3 queries fire essentially together. At one of our plants with newer server hardware and less traffic, the data is returned everytime. At one of our older locations with older hardware and more users, I consistantly have a problem. It is random on which query or how many of the 3 fail. They typically return only the first row of the data. I created a refresh button for each and after refreshing multiple times, the data appears. Any suggestions on what I can do to get these to hit everytime? In VB, I could loop till the data was returned. I also have a new screen that has a more complicated query and it takes several refreshes to return correctly. Just as a point of referenct, in all cases, when I run these queries in MS SQL Query Analyzer, they work everytime.

5

So it sounds like they’re running, but timing out? Perhaps your database connection pool is becoming intermittently full - check it out in the Gateway’s Status page. Maybe increasing the max size of the pool would help.

6

Thanks, I’ve increased to 12 from 8 to see what happens overnight.

7

There was no noticeable improvement after increasing the pool size. Will be adding more ram after returning from the states to see if that helps.

8

Are you getting errors in the client? Check the error log in the client under the Help > Diagnostics panel.

If a query times out you should get an error about it.