High Query per second spikes

I’ve made a CRUD page for database editing in vision. Using the configureCell extension function I change the text values of refering ID column’s to the corresponding descriptive value from their table of origin. This function seems to be causing some major unresponsiveness.
When the script is disabled everything is nice and snappy with no more than 30 max queries per second. When the script is enabled the entire page is slightly unresponsive and takes about a second to respond to any given input. There are also sudden spikes of queries, up to 160 per second.

This is the configure cell script. Masterdata is a dataset containing information about the table.

#---For columns that have a dropdown show the value of the descriptive column from the table of origin corresponding to the value in the current table. (for example, RecipeID in the table ContainerType will show the values from Recipes.Description instead of ContainerType.RecipeID)
#Prerequisite variables
	#the name of the current table
	MasterData = self.parent.MasterData
	columnHasDropdown = MasterData.getValueAt(colIndex, "hasDropdown")
	
	#if the column has a dropdown
	if columnHasDropdown == 1:
		tableOfOrigin = MasterData.getValueAt(colIndex, "tableOfOrigin")
		descriptiveColumn = MasterData.getValueAt(colIndex, "descriptiveColumn")
		
		#create an empty string to be filled with the results from the following query
		newValue = ""
		try:
			#Query the database for the new value.
			newValue = system.db.runScalarPrepQuery("SELECT "+descriptiveColumn+" FROM "+tableOfOrigin+" WHERE "+colName+" = "+str(value))
		except:
			pass
		
		#return the new value	
		return {'text': newValue}
#---END

Does anyone have an idea what might be causing this and how I can resolve the problem?

Don’t run queries or any other function that requires a gateway round-trip inside a component rendering event. (Anything that can be called when java needs to refresh the screen.) These events can be called many times per second in response to random UI needs. Arrange to have all data needed by such events pre-loaded in custom properties.

configureCell is called any time a property in the table changes, and for each cell. Because it is being ran from the event dispatch thread, it will block the ui and make it seem responsive. You will have to redesign your component to work differently. Instead of using the configureCell extension function, make a propertyChange event that invokes a similar script when MasterData changes, and invokes it asynchronously. When the script is done, set the resulting table to some other property, or data.

Thanks, Phil!

I hadn’t considered that the functions worked like that.
At the sacrifice of page loading times being a little longer I’ve changed my application to store all possible options for the columns in the table inside of a dataset that I create when the navigation button is clicked.

It works pretty well, and it’s a bit more responsive now, but still slightly less so than pages without columns that need this function.

Can you show what script you're using now?

For the best performance, you can take the code the configureCell script again in a project or shared script, and call it from the configureCell. That way, anything that's heavy but static can be done outside the function body, and be put into static variables.

F.e. you can load the query when the script is loaded, and put it in a python dictionary instead of a dataset to make searching through it a lot faster.

The configureCell function currently looks like this:

#---For columns that have a dropdown show the value of the descriptive column from the table of origin corresponding to the value in the current table. (for example, RecipeID in the table ContainerType will show the values from Recipes.Description instead of ContainerType.RecipeID)
#Prerequisite variables
	#the name of the current table
	from ast import literal_eval
	MasterData = system.dataset.toPyDataSet(self.parent.MasterData)
	columnHasDropdown = 0
	for row in MasterData:
		if row['columnName'] == colName:
			columnHasDropdown = row['hasDropdown']
	
	#if the column has a dropdown
	if columnHasDropdown:
	#retrieve the options, will be returned as a Pydataset.
		optionsDataset = system.dataset.toPyDataSet(self.parent.DropDownOptions)
		dict = {}
		for row in optionsDataset:
			if row['columnName'] == colName:
				dict = literal_eval(row['options'])

		newValue = dict[value]
		
		#return the new value	
		return {'text': newValue}
#---END

After changing around my scripts the only queries that even get fired from scripts on the page are for updates or refreshing the binding on my table.

I endorse @Sanderd17’s suggestion. You would have a project script module that looked something like this:

# project.masterdata
from ast import literal_eval

colopts = {}
pyds = system.db.runQuery("""Select columnName, options From someTable""")
for row in pyds:
    colopts[row['columnName']] = literal_eval(row['options'])

Then your configureCell function would be:

    if colName in project.masterdata.colopts:
        return {'text': project.masterdata.colopts[colName][value]}

Final note: you used dict as a variable name. Don’t! It is a built-in function. You might need it at some point and you will find it won’t work if you are in the habit of replacing it with a random dictionary.

I might misunderstand this, because i’m not familiar with project scripts, but would this still function the same way if the table and column’s are constantly changing? Even the database itself maybe?

The purpose of my application is to save our company some time not having to build database editors from the ground up for every client. So columns, tables and even databases won’t be the same.

There are ways around it to create a cache per configuration (calling a function when the configuration changes, updating variables in that script, ...).

It all requires more code, but I that's always a tradeoff you need to make: performance vs programming time.

Instead of preloading for a single case, you would include one or more additional levels of dictionaries to cache per database and table, with a function that would load accordingly when a particular table is selected by the user. If your DB guys were particularly diligent with foreign key definitions, you could even do the loading from the database schema.

The key to the approach is that project scripts are loaded once at client startup (actually, loaded at first use) and their top-level variables (like colopts in my example) are persistent. And accessible without import in event scripts and extension functions.

that makes sense. So all necessary information would be available as soon as the client launches.

I’ll be honest, I’m personally not happy with the way databases are handled and how they’re structured. Part of that is probably being used to just modifying the existing databases of customers.
That being said, I’m definitely no expert at working with databases seeing as I’ve only been working with them for about a year and a half myself, so I might be clinging too much to what I’ve been taught at college.