# 2 sessions, but 4 queries **URL:** \ **Category:** Ignition\ **Tags:** ignition78, database, vision, scripting\ **Created:** [November 12, 2024, 6:01pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530 "2024-11-12T18:01:36Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 6:01pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/1 "2024-11-12T18:01:36Z")
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. ![Screenshot 2024-11-12 095029](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/85fc961f51f397ea1f4b24d720268ba69b635801.png) Any ideas what's going on here? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 12, 2024, 6:19pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/2 "2024-11-12T18:19:14Z")
Your queries are taking \> 30 minutes. I'm surprised they haven't timed out. The WHERE clauses don't look that complex. 1. Are the t\_stamp columns DATETIME types (and not STRING)? 2. Are you sure your database has indexes on `main.t_stamp` and `sub.t_stamp`? ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 6:38pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/3 "2024-11-12T18:38:05Z")
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: ![Screenshot 2024-11-12 103459](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/2/8237f5af5d52393d54d2b62e4987c73c52de5c9e.png) the date compare is off a text box, and i read the text in the container. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 12, 2024, 6:44pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/4 "2024-11-12T18:44:30Z")
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. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 8:35pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/5 "2024-11-12T20:35:31Z")
Here is a pic of the DB datatypes ![Screenshot 2024-11-12 123309](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/0/308c7f010be724086c1eb0a2b752d45eb70b5ad9.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 12, 2024, 8:38pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/6 "2024-11-12T20:38:15Z")
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? ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 8:46pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/7 "2024-11-12T20:46:10Z")
ahhhhh. I don't see sub and main tables. ![Screenshot 2024-11-12 124537](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e9cb05a2c04b39f3c918b72c2e2b8082ef7016b5.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 12, 2024, 10:08pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/8 "2024-11-12T22:08:25Z")
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. 1. What flavour of SQL are you running? MariaDB / MySQL, MS SQL, PostgreSQL, Oracle or something else? 2. Do you have administration rights to the database - or a contact who does? 3. Can you find the administration tool? e.g., For MySQL / MariaDB MySqlWorkbench is popular. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 10:17pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/9 "2024-11-12T22:17:40Z")
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. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 12, 2024, 10:57pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/10 "2024-11-12T22:57:49Z")
Let's see if we can figure it out using Ignition's Named Query editor. Try this: - Go to Project Browser | Named Queries. - Create a new blank query "IndexCheck". - Open the Authoring tab. - From the Database Connections dropdown select your database. Your `main` and `sub` table names should now appear in the Table Browser on the right of the window. - In the Query text area, enter the following: `SHOW INDEXES FROM xxxxxx` Replace `xxxxx` with the name of your either your main or sub table (listed in the Table Browser). - Open the Testing tab. - Hit Execute Query. Post a nicely cropped screengrab here, showing the column headers and data for columns Table through to Collation. It should look something like this. ![Indexes](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/8/f8898e376e2233579cc6fc5d80096850b90f6725.png) When you have time, take a scan through this document. (It's a random one from a quick search on the topic.) [MySQL Show Indexes: Practical Examples and GUI Techniques](https://www.devart.com/dbforge/mysql/studio/mysql-show-indexes.html#:~:text=SELECT%20DISTINCT%20TABLE_NAME%2C%20INDEX_NAME%20FROM,tables%20in%20the%20specified%20database). ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 12, 2024, 11:06pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/11 "2024-11-12T23:06:37Z")
I am using version 7.8.5 which is prior the release of named queries. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 13, 2024, 6:24am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/12 "2024-11-13T06:24:50Z")
![Screenshot 2024-11-12 222008](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/9/998a3b5b1209169b37764f5feda1fcc0eb865bb5.png) I was able to get data types in the query browser. Having a tough time with indexes ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 14, 2024, 4:21am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/13 "2024-11-14T04:21:57Z")
@Transistor Got this of the index from inside designer. ![Screenshot 2024-11-13 201943](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/7/67ae17c8396304dddc08c982ec7744c4de9345af.png) Definitely no access to where the MySQL is hosted. But sent request to IT. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 14, 2024, 6:55am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/14 "2024-11-14T06:55:53Z")
From post #9: > [@Transistor](#): > > In the Query text area, enter the following: > `SHOW INDEXES FROM xxxxxx` > Replace `xxxxx` with the name of your either your main or sub table (listed in the Table Browser). Did you try that? `SHOW INDEXES FROM wastewaterlog` in your case. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 14, 2024, 6:58am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/15 "2024-11-14T06:58:20Z")
> [@Transistor](#): > > SHOW INDEXES FROM wastewaterlog Yes, ![Screenshot 2024-11-13 225731](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/5/a5fccd3b44d96fbf5d8f0f7f248cddab720506ee.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 14, 2024, 7:07am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/16 "2024-11-14T07:07:56Z")
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. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 14, 2024, 7:22am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/17 "2024-11-14T07:22:04Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 14, 2024, 12:56pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/18 "2024-11-14T12:56:32Z")
> [@Transistor](#): > > responds in a different way That old Ignition version can only do query/update autoselect, and it gets it wrong for that kind of meta-command. ---
**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\ **Post date:** [November 20, 2024, 11:54pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/20 "2024-11-20T23:54:04Z")
IT got me access to the serve, I was able to pull this from the command line tool. ![Screenshot 2024-11-20 155309](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/7/f7d24dfef62ed026098c6abd9305af081c3200fc.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [November 21, 2024, 12:00am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/21 "2024-11-21T00:00:31Z")
Can you cut and paste that as formatted text, please? See [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). That way the columns will line up and my head won't hurt. In the jumble it appears that wastewaterlog has - Key\_name: WastWaterLogt\_stampndx based on - Column\_name: t\_stamp so that should be indexed properly. Wasn't there a second table to be checked? [Next page](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530.md?page=2)