# How to run a project use less server SQL resource **URL:** \ **Category:** Ignition\ **Tags:** database, ignition80, vision\ **Created:** [September 26, 2020, 8:14am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092 "2020-09-26T08:14:40Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 26, 2020, 8:14am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/1 "2020-09-26T08:14:40Z")
BackGround: I have ignition as a tool to develop the operator interface to collect realtime data from production line. With more and more app develped, sql resource become a big issue the following picture is my sql activity’s execution picture ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/7/0740302c0f42c377f701c641cc58c27acafc819c.png) here is my sql activity read picture ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/6/065a9a7618afbf18cbff4aa89c1e95238d8d0038.png) i found there have a very big read action, perhaps that is the reason that make all my other query wait for a long time ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/a/aa234ee8c5d393403266aaa1081ca6b36dff4d8c.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/1/41f85656fe3b145533d0f3fc9bb1b20821772191.png) this table RPL\_DATA1 currently have 600K lines ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/a/6ad2880d8a89070a1d4cb510d232a3c43dd18cb1.png) these tag is Event Driven, almost have 100 these kind of tags in the Gateway. (because I have 9 machine, I firstly prepare them on the gateway tag, then use indirectly binding to show them on the vision) I don’t know why these 100 event driven tag will make the ignition tag’s sql function almost go wrong, What should I do to improve the software structure and make ignition more stable ? ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [September 26, 2020, 9:31am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/2 "2020-09-26T09:31:27Z")
It is good to separate operational data from the historical data used for reporting. You usually only need short term operational data to run a production line. So, either separate into different tables in the database (a small fast table with short term operational data, and a large slower table with historical data), or keep the operational data in ignition tags. Ether way, it will speed up data retrieval for displaying to operators running the line. Then have a different display for reviewing historical data that is not as time critical. If you haven’t already, you could also add indexes to the slow tables to speed up queries. Properly formed indexes can cut retrieval times dramatically. I have similar tables with over 60M records and adding the right indexes made a huge difference. ---
**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\ **Post date:** [September 26, 2020, 9:45am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/3 "2020-09-26T09:45:03Z")
First, make sure your table is indexed properly. Secondly, look at creating a single query to bring back all values, then bind into that dataset tag using derived or expressions tags. This will minimize the requests to your database. ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 26, 2020, 11:32am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/4 "2020-09-26T11:32:37Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fb1d526fd0c39808c8b5c7ff0e274d2498e6bb65.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a0ec3b05b78ea1d19d1e0d5f9ee9434976f06b3.png) You can see my tag, all of them the difference is the yellow highlighted difference, that is use to sorting different work time How to query to bring back all values in 1 query use the sql skill? Thanks a lot ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 26, 2020, 11:44am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/5 "2020-09-26T11:44:26Z")
I have two database as your suggestion, about use the add the right indexes suggestion , I have checked my table, I have a formed indexes, you can see the picture my current max id is 744498 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/4/64e25ab58d4c0cabe5fbba3046688976695ad4dc.png) my current query is like this ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d40d2ffdbb0df9e253c115fc392d97e393e086c.png) In my understanding you suggest me use the sql like the following picture, the yellow line is the difference. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/6/86f2a5c68e057c7315615ffe07a01dd5d37bc0bd.png) If my understanding is correct , always production keep every minutes, the max id will increase every minute, currently job always have to query the data from the last 2000 columns, this id is manually input by my self , how to keep the id update automatically every week or every month? Thanks a lot. ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [September 26, 2020, 1:38pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/6 "2020-09-26T13:38:53Z")
Hi @ [yifeiliu08](http://forum.inductiveautomation.com/u/yifeiliu08) To speed up the queries you need to add indexes in the database itself. This is slightly different depending on the type of database you are using. This is not adding an extra column to your table. What database are you using? [https://www.tutorialspoint.com/sql/sql-indexes.htm](https://www.tutorialspoint.com/sql/sql-indexes.htm) ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 26, 2020, 1:55pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/7 "2020-09-26T13:55:34Z")
Hi DavidWisely my database is Microsoft SQL ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/24ad76520b3b578beeee7b3eea234dbd819bd838.png) My current table’s id column is an identity column (PRIMARY KEY) if I insert one column this id will increase 1 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/b/bbef64ee7c41d1eb2e868f544d4c221b27fd44b6.png) I want to know if I put id in the where it will speed up? ---
**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:** [September 26, 2020, 3:22pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/8 "2020-09-26T15:22:04Z")
If there is a column or collection of columns that can be used as a unique index, consider using it (them) as the primary key instead. And ditch the id column. Your shift start timestamp is a likely candidate. ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [September 27, 2020, 8:24am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/9 "2020-09-27T08:24:13Z")
I think you are confusing your primary key with an index. (even though there is probably an index on your primary key). You could view this video to understand indexes a bit better: [![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/6/d6023aaa1396cd3d80e03829c5f9c8020234aa7b.jpeg "SQL Index |¦| Indexes in SQL |¦| Database Index") ](https://www.youtube.com/watch?v=fsG1XaZEa78) ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 27, 2020, 1:03pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/10 "2020-09-27T13:03:17Z")
Dear pturmel, thanks for your suggestion, I am not very clear about the sql update rule in ignition, here is my current status for tag side , I create the query tag ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/4/f4a8bc464b027ecbed609cf23e9649da09a51e71.png) this tag is for the purpose to sorting some data from 600K columns , and every 1 minute, 1 more column will be insert into ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/a/aa7fa175073af554d0f34c389c3a9b16c6a44517.png) current I choose the execution mode is Event Driven, (200 tags seems like this) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/3/2317d23538931f9298aa560411b01e5fb4ad606e.png) I found it seems it refresh all the time , does that means in this 600k lines table if it add 1 column , that will trigger the event driven mode, make the query active? If yes, I should change the execution mode to tag group (600s) it will reduce the update speed? ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [September 27, 2020, 1:13pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/11 "2020-09-27T13:13:00Z")
thanks a lot, this video is very clear 😀 ---
**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:** [September 27, 2020, 1:20pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/12 "2020-09-27T13:20:05Z")
In event driven mode, your query will run whenever the tags referenced in the query change. If you want it to run once every ten minutes, then yes, use tag group execution. ---
**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:** [September 27, 2020, 1:21pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/13 "2020-09-27T13:21:52Z")
> [@DavidWisely](#): > > even though there is probably an index on your primary key A primary key _ **is** _ an index. It is also a unique & non-null constraint. ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [September 28, 2020, 12:10am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/14 "2020-09-28T00:10:58Z")
> [@pturmel](#): > > A primary key _ **is** _ an index. It is also a unique & non-null constraint. Well sort of, anyway, what I was trying to impart was that adding an extra column is not the same as adding an index. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 28, 2020, 1:57am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/15 "2020-09-28T01:57:33Z")
> [@DavidWisely](#): > > > [@pturmel](#): > > > > A primary key _ **is** _ an index. It is also a unique & non-null constraint. > > Well sort of.... Is there ever a time it isn't? From everything I've seen from every database, that's true. SQLite is kind of unique and it's the same. ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [September 28, 2020, 2:21am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/16 "2020-09-28T02:21:44Z")
> [@jlandwerlen](#): > > Is there ever a time it isn’t? From everything I’ve seen from every database, that’s true. SQLite is kind of unique and it’s the same. A primary key isn't an index itself, an index is created on it. This is done automatically in most, if not all databases. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 28, 2020, 2:33am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/17 "2020-09-28T02:33:32Z")
> [@DavidWisely](#): > > This is done automatically in most, if not all databases. I guess that was my point... ---
**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:** [September 28, 2020, 11:43am UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/18 "2020-09-28T11:43:45Z")
It isn’t most. It is all. Indices are ‘keys’. The primary key is an index. As primary, it must also uniquely identify every row (a constraint). Some databases’ management tools show the primary key under a table’s list of constraints. Others show it under the table’s list of indices. MS SQL Server does the latter, fwiw. Early versions of MySQL didn’t support multi-column index behavior for primary keys, and Ignition tries to accommodate that by defaulting to a serial number primary key in transaction groups. Over-exposure to lame old MySQL may explain David’s misunderstanding. ---
**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:** [September 28, 2020, 1:17pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/19 "2020-09-28T13:17:04Z")
> [@pturmel](#): > > The primary key is an index. This is not the case. Though 'Key' and 'Index' are often used interchangeably. A key is a column or set of columns in a database that can be used to locate one or more rows in a table. An index is an implementation feature of a database used to store, search, and find rows in a table using a key. When a Primary Key is added to a table the Database Engine creates a Unique Index on the Primary Key columns to enforce the data uniqueness. In other words you can create a Unique Index without a primary key but you can not create a primary key without a Unique Index. ---
**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:** [September 28, 2020, 2:47pm UTC](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092/20 "2020-09-28T14:47:57Z")
> [@lrose](#): > > you can not create a primary key without a Unique Index. Ergo, a primary key is an index. Thanks for confirming my statement. [Next page](https://forum.inductiveautomation.com/t/how-to-run-a-project-use-less-server-sql-resource/39092.md?page=2)