# Stored Procedure call overhead-way to batch? **URL:** \ **Category:** Ignition\ **Created:** [September 30, 2024, 8:12pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630 "2024-09-30T20:12:38Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![gregory.sachs](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gregory.sachs](https://forum.inductiveautomation.com/u/gregory.sachs)\ **Post date:** [September 30, 2024, 8:12pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630/1 "2024-09-30T20:12:38Z")
We have a data query application that calls a stored procedure within a script, potentially 90 times for a single execution. There appears to be some overhead associated with the creation and execution of the stored procedure, making the script itself take sometimes 10+ seconds. Without moving away from the stored procedure approach, is there a way to batch these calls to optimize performance? Generically, we pass a couple of values to the stored proceudre, then write the response to a plc tag. That is currently done via individual system.tag.write operations, and I believe we would get some improvement via switching to system.tag.writeall and batching the writes, but the developer is saying that the bottleneck is the SP call. Thoughts? ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [September 30, 2024, 9:03pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630/2 "2024-09-30T21:03:14Z")
> [@gregory.sachs](#): > > potentially 90 times for a single execution If your data allows it, modify your stored procedure to be able to take a variable number of parameter sets and return a single dataset with all the necessary data back. Have your script build all the parameter sets and pass it in one call to the stored procedure, and then pick the data you need out of the response. > [@gregory.sachs](#): > > get some improvement via switching to system.tag.writeall You most certainly would, but your main performance hit is the 90 calls to the same stored procedure. If you can't change anything else at the very least try to consolidate all your tag writes into a single `writeBlocking` (or `writeAll` if you are on ignition 7.9) at the end of your script. ---
**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:** [September 30, 2024, 9:19pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630/3 "2024-09-30T21:19:32Z")
Consider nesting that stored procedure inside a new stored procedure that accepts array parameters. It would call the original SP for each, and assemble the result from each into a row of a response dataset. One call to process many operations. You'll need a competent database (with UNNEST or equivalent support): > [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4): > > If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound dataseā€¦ ---
**Author:** ![gregory.sachs](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gregory.sachs](https://forum.inductiveautomation.com/u/gregory.sachs)\ **Post date:** [October 1, 2024, 5:54pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630/4 "2024-10-01T17:54:57Z")
Thanks, have tossed the idea to the DB expert. The backend is DB2, I'm not sure if that counts as competent. šŸ¤· ---
**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 1, 2024, 5:57pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-call-overhead-way-to-batch/96630/5 "2024-10-01T17:57:55Z")
Yes, competent, [according to its documentation](https://www.ibm.com/docs/en/db2/11.5?topic=functions-unnest). As long as its JDBC driver doesn't break the arrays on their way **to** the DB.