# Blocking queries from running during some time intervals **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [October 6, 2022, 6:56pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916 "2022-10-06T18:56:43Z")\ **Posts on this page:** 20\ **Page:** 1
**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:** [October 6, 2022, 6:56pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/1 "2022-10-06T18:56:43Z")
I have many pages and many queries that people can use to analyze machines. I also have reports that I run, and data that I collect at the end of some shifts. Are there "good" ways to prevent people from running queries at times around the end of shifts to, in a way, protect that data collection? ---
**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:** [October 6, 2022, 7:06pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/2 "2022-10-06T19:06:46Z")
What triggers the queries to run now? Someone pressing a button? If so add some expression logic that disables the button during certain hours. If the queries run on a schedule via gateway timer or similar, add some extra logic to an `if` condition that checks the time so it only runs during the interval you deem ok. Maybe try optimizing the queries as well with appropriate indexes and then this doesn't become as much a problem in the first place if possible. ---
**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:** [October 6, 2022, 7:17pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/3 "2022-10-06T19:17:34Z")
When the page is opened, the queries run once, unless they change a parameter, then it runs again. Though I do have a few realtime queries, that I am not trying to turn off. * * * As far as the indexing, the few queries I am worried about are tracking events that happen so fast that putting indexes in isn't reasonable I think. Though I have streamlined some of them, and some are older and I need to go speed them up. ---
**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:** [October 6, 2022, 7:21pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/4 "2022-10-06T19:21:42Z")
So these are binding's that that run queries ie a named query binding or SQL binding? If thats the case there's no way to turn a SQL binding on/off and you would have to then do this via scripting. However if these are named queries you could just add a long cache time and then they will only re-query the database when that cache time is up or if you manually ran `system.db.clearNamedQueryCache()` for it in between. ---
**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:** [October 6, 2022, 7:24pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/5 "2022-10-06T19:24:22Z")
> [@zacharyw.larson](#): > > Are there "good" ways to prevent people from running queries at times around the end of shifts to, in a way, protect that data collection? I will say though this is a bit concerning. Users shouldn't be able to influence data collection at least not just by opening a page imo. The gateway/server level that is what should be doing all your data collection via transaction groups/historian/tag change events etc etc and then any vision/perspective client only gets a view of what's been collected via select queries, and can only influence via some action like a button press, not just from navigation. Or maybe I am misunderstanding your issue here. ---
**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:** [October 6, 2022, 7:35pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/6 "2022-10-06T19:35:02Z")
I agree. You are right, thanks. Among my tables, I have a few unindexed tables with very frequent inserts that I need to split into an agile fast inserts table and an indexed long term table. I should do that instead of working on a band-aid for the interim. Put it off long enough. * * * In order to keep a rolling long term table that is indexed, I schedule a script that does this: 1. create table based on current month that includes the past months of data 2. index the table 3. drop the table that was created 2 months ago, keeping a backup if the current one is not working Is this a good way? Or what is the best practice? I put it off because it is a little intimidating. I think I asked about it a few different ways, but it is intimidating I think. Took a bit to get used to calling queries in scripts. ---
**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:** [October 6, 2022, 7:45pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/7 "2022-10-06T19:45:51Z")
Get a better DB. There shouldn't be anything "too fast" to index on the fly. If $$ is a problem, then PostgreSQL's BRIN [indices](https://www.postgresql.org/docs/11/indexes-types.html) are particularly well suited to fast ingest of timestamped data. ---
**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:** [October 6, 2022, 7:52pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/8 "2022-10-06T19:52:02Z")
I didn't test the index speed. I guessed/estimated that indexing would be too slow for the inserts. I thought if my query for 1.5 million entries takes 12seconds, then the indexing would not be fast enough. I get like 10,000 inserts a day to these tables. 20 events could occur at the same time I think. I think those events are inserted as fast as they can be when the transaction group triggers are detecting the tag changes. ---
**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:** [October 6, 2022, 7:56pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/9 "2022-10-06T19:56:21Z")
> [@zacharyw.larson](#): > > I get like 10,000 inserts a day to these tables. Nah. That's easy. It's not even one per second on average. Millions of records per day is what I would call "busy". ---
**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:** [October 6, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/10 "2022-10-06T19:57:41Z")
How many per second would you call busy? If a query for all the rows, 1.5 million rows, takes 11 seconds, won't indexing take over a minute per insert? ---
**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:** [October 6, 2022, 8:00pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/11 "2022-10-06T20:00:18Z")
No. Indexing's whole point is to be efficient in the face of large quantities. Hundreds or maybe thousands of inserted rows per second is "busy". Have you ever looked at what you get in tag historian tables when collecting at default pace for hundreds or thousands of tags? ---
**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:** [October 6, 2022, 8:01pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/12 "2022-10-06T20:01:48Z")
Is the tag historian indexed? Honest, I don't know. I think I try to collect historian data at like once in 6 seconds for most things, and only like speed I try to capture faster. So I am not sure if my usage of the historian is a good measure. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [October 6, 2022, 8:03pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/13 "2022-10-06T20:03:30Z")
Have you looked? 🙂 ---
**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:** [October 6, 2022, 8:04pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/14 "2022-10-06T20:04:38Z")
I saw the count on the gateway that says how many I am collecting. I have an idea how many, thousands each 6 seconds for me. I am not sure how to look though beyond that. Is it indexed? ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [October 6, 2022, 8:06pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/15 "2022-10-06T20:06:35Z")
You should look for yourself to get familiar, here is a screenshot from MySQL: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/515a357006fa936898a6e0b09815da84dcecc595.png) tagid and t\_stamp are indexed ---
**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:** [October 6, 2022, 8:07pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/16 "2022-10-06T20:07:47Z")
I don't think I can look. I only recently got some access to the gateway. That looks like to be from software that looks at the schema of the database outside of Ignition. I also don't know how to read that. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [October 6, 2022, 8:14pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/17 "2022-10-06T20:14:32Z")
> [@zacharyw.larson](#): > > I also don't know how to read that. I did type the answer as well. To be more specific, tagid and t\_stamp are primary keys and t\_stamp is indexed ---
**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:** [October 6, 2022, 8:37pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/18 "2022-10-06T20:37:23Z")
I was so worried, thanks. '[More indexes, slower INSERT](https://use-the-index-luke.com/sql/dml/insert)' This site says that in their testing, one index only added 1/100th of a second delay. [This site](https://dataschool.com/sql-optimization/how-indexing-works/) says that while an index is being updated, queries can't be run. However, when I watched this video: '[SQL Index |¦| Indexes in SQL |¦| Database Index - YouTube](https://www.youtube.com/watch?v=fsG1XaZEa78)' It took 4 minutes for the index to be created. I thought it would take that long each insert. Though they did have 100 million instead of just 1.5 million records. ---
**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:** [October 7, 2022, 12:54pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/19 "2022-10-07T12:54:58Z")
Yea I think you're worried for nothing regarding the index. As Phil said, it's not even one a second. Only when were talking constantly having multiple INSERT's every second would I think that I wouldn't add an 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:** [October 7, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916/20 "2022-10-07T12:56:28Z")
> [@bkarabinchak.psi](#): > > having multiple INSERT's every second would I think that I wouldn't add an index. Nah, even then I'd have an index. You need the index to access large quantities of data. What is the point of inserting large quantities of data if you cannot access it? [Next page](https://forum.inductiveautomation.com/t/blocking-queries-from-running-during-some-time-intervals/64916.md?page=2)