Script filter a query tag on perspective page from a dropdown value

[bkarabinchak.psi]

The only downside is it takes up a little bit of extra memory to maintain the index, and INSERTs will take marginally longer I believe due to having to find it’s place in the B-Tree, but given this query is not a high frequency thing from the sounds of things, neither is really problematic here.

There’s two sides of optimizing here:

1. The database side. This is where you have power to optimize the speed and efficiency of the query. You would have to use the EXPLAIN statement as a guide as @bschroeder mentioned. Indexing seems the way to go though I haven’t seen your EXPLAIN statement

2. Ignition side - the only thing you can do on the ignition side is to try to lower the number of times you need to call said query, and cache results when you can via a named query, again to help you lower the number of times you have to hit the database. That is it. You can’t optimize the query per se from ignition.

@zacharyw.larson if this table isn’t for some high frequency INSERT situation, then the extra time it takes for insert into the table - you will never notice it.

[bschroeder]

I’ve not seen issues with it… but TBH I’m not doing hyper-fast inserts either.
I’m mainly concerned with being able to sort and join data across 4-10 tables at a time and pull that data up to the operator as fast as possible.

Generally speaking, you want to index the columns that you use in JOINS and WHERE statements. The database will use those indexes to speed up searching across the table to data to help retrieve data in a faster fashion.

I do understand though not having access to the database though…

[zacharyw.larson]

I am working with high speed machines.
I have not set aside time to test the indexing performance yet.

Thanks for explaining those options and which columns to index.

I have a few tables that some queries join.
I need to combine the transaction groups into a single table for some of the queries.

[pascal.fragnoud]

@zacharyw.larson

I think you might be undervaluing the performance improvement calling the query once per shift instead of once per session. I might be overvaluing it though.

Save the result to a tag, not a session property.

[zacharyw.larson]

I am doing both.

First it is set a query tag. Then that is bound to a session object’s custom property so I can use session objects to filter the query in a script. You helped me with this remember? You showed me the expression structure binding. Also, thanks.

[zacharyw.larson]

Solved, thanks for all the help.
Thanks for the recommendations of additional stuff to improve.
solution I went with:

1. query tag gets the query, runs once every 8 hours via driven (first 10 posts cover it I think)
2. bind the tag to custom property on the perspective component
3. bind components meant to filter the query to an expression structure binding on data
4. a script transform performs the filtering

my transform is or will be like this when I eliminate the dictionary in it:

filterLine=self.parent.parent.getChild("Dropdownrow").getChild("Dropdown").props.value	
filterCode =self.parent.parent.getChild("Dropdownrow").getChild("NumericEntryField").props.value

valueHeaders = ['Line','Year Month', 'Stops','Attempted','Palletized']
data=[]
for row in range(value.getRowCount()):
	Line= value.getValueAt(row,'Line')
	yearMonth= value.getValueAt(row,'Year_Month')		
	stops= value.getValueAt(row,'Stops')
	Attempted =value.getValueAt(row,'Attempted')
	Palletized=value.getValueAt(row,'Palletized')		
	if filterLine == 0 or Line ==filterLine:
			data.append([Line,yearMonth,stops, Attempted, Palletized])	
	
data = system.dataset.toDataSet(valueHeaders, data)
	
return data