# Script Timing Questions **URL:** \ **Category:** Ignition\ **Created:** [October 25, 2019, 5:08pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400 "2019-10-25T17:08:45Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [October 25, 2019, 5:08pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/1 "2019-10-25T17:08:45Z")
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? ```auto time20 = System.nanoTime() # perform all database inserts for elem in queryList: system.db.runUpdateQuery(elem, "MySQL_Conn") time30 = System.nanoTime() ``` ---
**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 25, 2019, 5:55pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/2 "2019-10-25T17:55:06Z")
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. } ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [October 25, 2019, 6:42pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/3 "2019-10-25T18:42:37Z")
The handshake is easy. I'll start adding that right now. > [@pturmel](#): > > Use a stored procedure or multi-row inserts to reduce the number of round-trips to the DB that have to run sequentially 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.) > [@pturmel](#): > > 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. I'd like to do this as well. Where, or how do I do that? Are you referring to Project Library 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 25, 2019, 6:49pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/4 "2019-10-25T18:49:27Z")
> [@brian.kehn](#): > > If you can think of a manageable method, I’d love to do it though. 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. > [@brian.kehn](#): > > Are you referring to Project Library scripts? 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. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [October 25, 2019, 7:59pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/5 "2019-10-25T19:59:04Z")
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. ```auto # 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 ```auto # 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? ---
**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 26, 2019, 12:45pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/6 "2019-10-26T12:45:27Z")
> [@brian.kehn](#): > > Can I use this “sqlString2.append()” and insert it right into db.runPrepUpdate() like this? 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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [October 28, 2019, 3:32pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/7 "2019-10-28T15:32:47Z")
See [this thread](http://forum.inductiveautomation.com/t/using-variables-in-a-sql-where-in-operator-scripting/27130/9?u=pgriffith) for some inspiration on properly formatting a multiple insert statement. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [October 28, 2019, 6:11pm UTC](https://forum.inductiveautomation.com/t/script-timing-questions/29400/8 "2019-10-28T18:11:31Z")
Thanks for all your help, this is going to work great.