Blocking queries from running during some time intervals

Ignition
21

Right I'm just saying that that's the threshold where it might start to affect inserts regarding speed.

You can still just access you will just need to wait for the nightly report to finish its 8 hour select query :slight_smile:

1 Like
22

I indexed on t_stamp and Line.
It seems to be capturing all the events.

7 in one second, still capturing them.

Reduced my complex query time from 15 seconds to 7 seconds.

1 Like
23

7 seconds is still an eternity IMO

5 Likes
24

I am most familiar with MSSQL Server.

In MSSQL server you can include fields on an index. So say you commonly query certain fields and filter on t_stamp. You can add those fields as included fields to your index on t_stamp to tell the database to store the data in a way that makes it fast to return those fields when the user filters on the indexed field.

It's a good thing to look into if you're working with a large record count. The bigger your record count the more relevant indexes will be.

1 Like
25

Have a look at the WHERE clause in your query. Then create an index that combines all the fields in the query.

1 Like
26 
select * myTable
order by t_stamp desc

Takes 11.5 seconds.
Returns 1.5 million rows

27

When there's no where clause, you return the whole table. There's only so much gain to be had if you aren't filtering the response. As you note, the index does speed things up a little by avoiding a sorting step.

The big gains from indices is when your query includes comparisons in the where clause that involve the indexed columns. Like selecting a single days worth of data from that table.

Try it.

2 Likes
28

Do you choose virtualized for your tables that display large amounts of data?

29

I don't display large amounts of data in Perspective. /:

3 Likes
30

How do you distribute the information to many people efficiently?

I have been posting the data to pages, sometimes in tables and other times in both tables and graphs.

31

This doesn't necessarily mean display. Display modest amounts of data (or summarized data) in charts or short tables. Distribute large amounts of data in zipped CSV or XLSX downloads.

3 Likes