Ignition 7.8.5
I have a query to populate a table. And it seems the database faults when too much data is queried.
I also noticed 4/8 and at one point 7/8 DB connections. Even though I have 1 project session and 1 designer open. So at max I'd think only 2/8 should be active.
Your queries are taking > 30 minutes. I'm surprised they haven't timed out.
The WHERE clauses don't look that complex.
main.t_stamp and sub.t_stamp?If i look at a days worth of data it is fine, but times out when larger pools are done.
This is how the values get into the DB:
the date compare is off a text box, and i read the text in the container.
Your screengrab shows the structure of your transaction group which is putting data into your database. Your original post shows problems with SELECT statements which are getting data out of your database.
I'm suggesting that you examine the database structure, check the t_stamp column types and the presence or absence of indices on the t_stamp columns.
OK, the t_stamp column is DATETIME so that's OK.
Can you use your database administration tool to find if there is an index on the t_stamp columns in the main and sub tables?
I think you're missing some database management skills here - or maybe you don't have permissions to administer the database. We still don't know if your t_stamp colums are indexed.
During database installation it is normal to include an administration tool which allows creation / editing / deletion of database and tables and, what we're looking for, the indexes. The indexing creates a list of all the records sorted by one or more columns specified in the index definition. Setting this up allows much, much faster query times.
I think its MySQL 5.1
I don't have access to the DB. I'm going to have to coordinate with IT and the in house guy there.
Let's see if we can figure it out using Ignition's Named Query editor. Try this:
main and sub table names should now appear in the Table Browser on the right of the window.SHOW INDEXES FROM xxxxxxxxxxx with the name of your either your main or sub table (listed in the Table Browser).Post a nicely cropped screengrab here, showing the column headers and data for columns Table through to Collation. It should look something like this.
When you have time, take a scan through this document. (It's a random one from a quick search on the topic.)
I am using version 7.8.5 which is prior the release of named queries.
@Transistor Got this of the index from inside designer.
Definitely no access to where the MySQL is hosted. But sent request to IT.
From post #9:
Did you try that?
SHOW INDEXES FROM wastewaterlog in your case.
That responds in a different way to my 8.1 system as shown in post #9.
Let's see what your IT guys can demonstrate.
Going to put an archived version of workbench on the pc I have access to and hope it lets me do a remote connection. I'll keep you posted.
That old Ignition version can only do query/update autoselect, and it gets it wrong for that kind of meta-command.
(post deleted by author)
