# Organizing SQL tables and named queries efficiently **URL:** \ **Category:** Ignition\ **Created:** [November 17, 2021, 9:45pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212 "2021-11-17T21:45:15Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 13, 2021, 7:13pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/21 "2021-12-13T19:13:58Z")
No idea either about transaction groups, but varchar columns have a fixed number of characters for their data and append space characters to the start if the data doesn’t match the character length. Use nvarchar if you don’t want prepended spaces and have variable length strings ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 13, 2021, 7:16pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/22 "2021-12-13T19:16:39Z")
Changing data types is easy if they’re getting bigger. Going smaller is an issue as data may be truncated ---
**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:** [December 13, 2021, 7:31pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/23 "2021-12-13T19:31:05Z")
> [@nminchin](#): > > varchar columns have a fixed number of characters for their data and append space characters to the start if the data doesn’t match the character length. No, that would be `char` columns. The "var" means variable length. ---
**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\ **Post date:** [December 13, 2021, 9:14pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/24 "2021-12-13T21:14:48Z")
> [@zacharyw.larson](#): > > int2 in ignition is a smallint in sql? Yes, int2 is equivalent to smallint. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 13, 2021, 11:19pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/25 "2021-12-13T23:19:28Z")
> [@pturmel](#): > > The “var” means variable length. 😬 oops! Sounds like nvarchar is the way to go over varchar though: > ---
**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:** [December 13, 2021, 11:39pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/26 "2021-12-13T23:39:56Z")
Meh. In non-braindead DB platforms, varchar can use utf-8, so its really a question of optimizations. Sometimes less data to transfer, but with a conversion, outruns more data already converted. I generally stick with varchar. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [December 14, 2021, 3:49pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/27 "2021-12-14T15:49:13Z")
> [@nminchin](#): > > Sounds like nvarchar is the way to go over varchar though: > > [sql server - What is the difference between varchar and nvarchar? - Stack Overflow](https://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) What Phil said. While SO is a great source of info, I find a number of their database answers are demonstrably wrong. Always take them with a grain of salt and a healthy dose of the docs. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [December 14, 2021, 3:56pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/28 "2021-12-14T15:56:09Z")
I wanted to say thanks for this post I thought SO answers were vetted more, didn’t know ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [December 14, 2021, 3:56pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/29 "2021-12-14T15:56:10Z")
> **[Database Administrators Stack Exchange](https://dba.stackexchange.com/)** > > Q&A for database professionals who wish to improve their database skills and learn from others in the community is a much better database focused stack exchange fwiw. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [December 17, 2021, 3:18pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/30 "2021-12-17T15:18:28Z")
Lists aren’t better than tables for getting fault descriptions into my queries correct? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 18, 2021, 11:23am UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/31 "2021-12-18T11:23:00Z")
Going to need a bit more context… Where are your fault descriptions stored currently? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [December 20, 2021, 4:28pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/32 "2021-12-20T16:28:31Z")
I had the descriptions in an excel sheet, a list, and now also in a table. I am using join for now. I think I need to read more about how to get data outside of my table into my table when I perform the binding on perspective page to know how to better ask about optimal usage or learn that. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [December 23, 2021, 4:38pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/33 "2021-12-23T16:38:22Z")
I have named queries for each machine stops and each machines rejects. These are events recorded in transaction groups. I have them combined in a table. I was trying to figure out how I will get the outfeed or infeed, then I realized I can collect those when I have the events for stops and rejects. So now I have this one big table that is very efficient. rejects, stops, infeed, and outfeed ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [December 23, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/34 "2021-12-23T19:38:40Z")
I am not sure I am setting up the transaction group correctly. I ran alter table, set the infeed/outfeed columns as bigint I made tags, they work like positive counters, datatype bigint In my transaction group, I think I have to use Int8. Is this okay? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 1, 2022, 7:19pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/35 "2022-02-01T19:19:39Z")
These queries are taking over 15 seconds. I am def doing something wrong. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [February 1, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/36 "2022-02-01T19:32:15Z")
Check out the execution plan of the query. Put the query into the database workbench and there should be something like EXPLAIN or similar - look up how to checkout the execution plan for your flavor of database. This will help you figure out where you need to add an index or where the query may be doing something repetitive. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 1, 2022, 7:45pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/37 "2022-02-01T19:45:24Z")
I think I might need to run the queries once a week or day, and store as a dataset for the various pages. Is there a rule of thumb, or chart for good query speeds? I want to know if I am asking too much of the query to be so fast, or collecting too many datapoints. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [February 2, 2022, 1:11pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/38 "2022-02-02T13:11:45Z")
If it is something you only need to run once a day/week, then 10-15 seconds isn’t really problematic imo, but you still may want to look at the EXPLAIN of the query to see if the way it executes will grow exponentially as time goes one, to see if its a very poor execution plan. The only rule of thumb I would say is the more often the data has to refresh the quicker the query should be. If it needs to be refreshed once a minute, then query that takes over a minute is not going to work. But if it only needs to be refreshed once a week, even a 5 minute query is not really an issue. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 2, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/39 "2022-02-02T14:11:23Z")
it will grow, I am collecting data points will be linear growth Right now the query refreshes whenever someone views the page. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [February 2, 2022, 2:48pm UTC](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212/40 "2022-02-02T14:48:51Z")
> [@bkarabinchak.psi](#): > > If it needs to be refreshed once a minute, then query that takes over a minute is not going to work. But if it only needs to be refreshed once a week, even a 5 minute query is not really an issue. I tend to agree, however, I generally error on the side of less network traffic is better, and don't make the database work harder than it needs to. The general rule of thumb that I use, is that data should update at half the rate of the required refresh rate. This goes for queries and data collection. If I care about what a data point is every hour, then I put it on a 30 min update. For historical data, if I need to ensure that I have data, then this is how I do it, otherwise I set it to On Change. [Previous page](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212.md?page=1) [Next page](https://forum.inductiveautomation.com/t/organizing-sql-tables-and-named-queries-efficiently/53212.md?page=3)