Script Timing Questions

Ignition
1

I have a loop of between 100 and 500 db.runUpdateQuery statements and it is taking my system about 1.3 seconds to finish inserting them all into the database. (A portion of the script is below). This loop of updateQuerys happens every cycle of the machine.
1.) What would happen if the script was called again before the first loop had a chance to finish?
2.) what would happen if the script were called consistently at a rate faster than the time it takes to finish the updateQuery loop?

		time20 = System.nanoTime()
			
			# perform all database inserts
		for elem in queryList:
			system.db.runUpdateQuery(elem, "MySQL_Conn")
		
		time30 = System.nanoTime()
2

You won’t get what you think. Event scripts generally cannot run in parallel with themselves. They can run in parallel with other events. You will basically fall behind. Presumably the event collects the data that will be sent to the DB–at some point you’ll be crossing cycle completion and get data from different cycles mixed together.

I would use a handshake to prevent the process from replacing the data until it is collected. Which won’t happen on time if the prior cycle’s inserts are holding it up. There’s a couple things you can do:

  1. Use a stored procedure or multi-row inserts to reduce the number of round-trips to the DB that have to run sequentially.

  2. Collect the data from the process in the event routine but pass the resulting list to an asynchronous task to perform the actual inserts. Such tasks can run in parallel.

{ Oh, and you really ought to be using runPrepUpdate, not runUpdateQuery. Or even better, a named query. }

1 Like
3

The handshake is easy. I'll start adding that right now.

This, I would like to do, but I haven't found a viable strategy yet. Because the number of rows changes upon each execution, the logic involved in creating the (dynamic) query string seemed to get really hairy. (If you can think of a manageable method, I'd love to do it though.)

I'd like to do this as well. Where, or how do I do that? Are you referring to Project Library scripts?

4

If your large collection of inserts is really qty 20 repeated five times, or some combination of repeats, I'd construct multi-insert statements that do 20 at a time. You'll have to share more details for a better analysis.

Not precisely, though you should consider moving the bulk of your code to script modules--much more maintainable, along with predictable global scope rules. The key is constructing a function that you can run via system.util.invokeAsynchronous(). If you search this forum for "later.py", you'll find some of the discussion about how such things work, and how to simplify their usage.

5

Here is my attempt to make a multi row INSERT. It’s actually much simpler than it would have been with db.runUpdateQuery. Here is a portion of the gateway event, tag change script. Note: “writeSize” can vary between 100 and 500, depending on the number of samples recorded by the PLC on each part.

		# contruct database query strings
		sqlString1 = "INSERT INTO data (timeVal, PromessPosition, promessForce, Temp0, Temp1, Temp2, Temp3, Temp4, Force0, Force1, Force2, Force3, Force4) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"
		sqlString2 = []
		for i in range(0, writeSize):
			j = (i*5)
			myList = [timeList[i], promPosList[i], promForceList[i],tempList[j],tempList[j+1],tempList[j+2],tempList[j+3],tempList[j+4],forceList[j],forceList[j+1],forceList[j+2],forceList[j+3],forceList[j+4]]
			sqlString2.append(myList)
	
		# DB Insert call
		system.db.runPrepUpdate(sqlString1,sqlString2)

but for simplicity, I’ve sterilized it a little bit

		# contruct database query strings
		sqlString1 = "INSERT INTO data (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) VALUES (?,?,?,?,?,?,?,?,?,?)"
		sqlString2 = []
		for i in range(0, writeSize):
			myList = [val1List[i],val2List[i],val3List[i],val4List[i],val5List[i],val6List[i],val7List[i],val8List[i],val9List[i],val10List[i]]
			sqlString2.append(myList)
	
		# DB Insert call
		system.db.runPrepUpdate(sqlString1,sqlString2)

Can I use this “sqlString2.append()” and insert it right into db.runPrepUpdate() like this?

6

No. You have add , (?,?,?,?,?,?,?,?,?,?) to the SQL string for each row you will insert in the block. Then you have to make the list of arguments a flat list, not a nested list.

7

See this thread for some inspiration on properly formatting a multiple insert statement.

8

Thanks for all your help, this is going to work great.