# How to run Script faster **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective, scripting, database, ignition80\ **Created:** [December 21, 2021, 6:06am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343 "2021-12-21T06:06:31Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\ **Post date:** [December 21, 2021, 6:06am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/1 "2021-12-21T06:06:31Z")
Hello all, I need a help to stop freezing my screen! Before I begin I did try to achieve result with invokeAsync but still facing some delay. **Scenario** : Okay so I am pulling a data from _DB\_1\_Connection_ having more than _5000 rows_. I pull this data based on tons of filters which I perform on screen. Now I am pulling daily some data from _DB\_2\_Connection_ having more than _40-50 thousand of rows_ (which are going to be in lacks in future). In my script I am checking if my applied filters are present in one of column of the data I fetched from _DB\_1\_Connection_ and later I verify each row from _DB\_1\_Connection_ in one of column in _DB\_2\_Connection_ which returns some combined dataset to be displayed on a table. As you must have guessed this freezes a screen when operator changes filter selection. I thought of showing progress bar as I found on a forum but unable to achieve. Also if script is running in the backend and operator changes filter in between, that goes into queue and adds more delay to return the result. I am still looking to do this by using later.py posted by Phil. Any suggestions are appreciated. Happy Holidays everyone! ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [December 21, 2021, 6:13am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/2 "2021-12-21T06:13:21Z")
Sound ideal to enable cache property for that named query. Are you using named query right? ---
**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\ **Post date:** [December 21, 2021, 6:14am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/3 "2021-12-21T06:14:37Z")
Yes but I don’t face much delay while getting the record from DB. The problem comes with script transform because of lengthy data ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [December 21, 2021, 6:21am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/4 "2021-12-21T06:21:42Z")
I would first try to optimized your script. If data is loading fast from DB maybe the bottle neck is happening in your script. Post it to give it a look. Sometimes is not a good idea to used scripting transform from binding with such large amount of data. Try to use custom methods instead triggered by (when data is loaded from db). If that doesn’t work maybe I would use [system.util.sendRequestAsync - Ignition User Manual 8.0 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC80/system.util.sendRequestAsync) as last resource. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [December 21, 2021, 8:34am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/5 "2021-12-21T08:34:27Z")
Then we need to see the script. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [December 21, 2021, 1:14pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/6 "2021-12-21T13:14:25Z")
If you have any `print` statements, comment them out - they can actually slow your script down quite a bit especially if they’re in your looping of the data. Better to put that stuff into a logger anyways (which does not slow your script down like a print does). But as mentioned, post your script otherwise we can only speculate. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [December 21, 2021, 1:43pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/7 "2021-12-21T13:43:50Z")
You could try to use the db query to filter the data and not with scripting. Here is an example, you define all your filters as parameters for the named query and when you run the query, supply only the parameters (filters) that you want to use. ```auto select * from pillow_history where (:_user is null OR :_user in (user,user2)) AND ((:_start is null AND :_end is null) OR t_stamp between :_start and :_end) AND (:_fabric is null OR fabric = :_fabric) AND (:_ackno is null OR ackno = :_ackno) AND (:_serial is null OR serial = :_serial) AND (:_style is null OR style = :_style) ``` ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [December 21, 2021, 1:45pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/8 "2021-12-21T13:45:54Z")
What @dkhayes117 is saying is a good rule of thumb. Your database will be faster at sorting your data via a where clause than jython will be, let the database do what it is good at and let jython do what it can. ---
**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:** [December 21, 2021, 2:00pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/9 "2021-12-21T14:00:45Z")
> [@Gaurav.Shitole](#): > > Also if script is running in the backend and operator changes filter in between, that goes into queue Do you mean "backend" as the gateway? Or did you mean _background_, as in invokeAsynchronous? Anyways, I recommend using `Thread.interrupt()` with `Thread.interrupted()` to cancel background scripts that are being superceded. ---
**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\ **Post date:** [December 21, 2021, 5:36pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/10 "2021-12-21T17:36:53Z")
Sorting in SQL is done with SQL ORDER BY not WHERE… My favorite SQL guru says that SQL Server is the second most expensive place to sort data: > **[How to Think Like the SQL Server Engine: Adding an ORDER BY - Brent Ozar...](https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-adding-an-order-by/)** > > We started out the How to Think Like the Engine series with a simple query with a WHERE clause: Now let’s add an ORDER BY: \[crayon-61c21039183ac133915041/\] Here’s the updated plan – note that the query cost has tripled to $17.72 Query Bucks. Let’s... ---
**Author:** ![JR7](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@JR7](https://forum.inductiveautomation.com/u/JR7)\ **Post date:** [December 21, 2021, 6:04pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/11 "2021-12-21T18:04:30Z")
Hi Gaurav, Have you tried to increase the size of the innodb\_buffer\_poolsize of your database? Increasing innodb\_buffer\_poolsize requires to restart the database to take effect. ---
**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\ **Post date:** [December 21, 2021, 9:59pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/12 "2021-12-21T21:59:36Z")
> [@dkhayes117](#): > > You define all your filters as parameters for the named query and when you run the query, supply only the parameters (filters) that you want to use. > > ```python > select * > from pillow_history > where (:_user is null OR :_user in (user,user2)) > AND ((:_start is null AND :_end is null) OR t_stamp between :_start and :_end) > AND (:_fabric is null OR fabric = :_fabric) > AND (:_ackno is null OR ackno = :_ackno) > AND (:_serial is null OR serial = :_serial) > AND (:_style is null OR style = :_style) > > ``` Just for my (and possibly others') understanding, can you elaborate a bit on this query? Let's say for example you don't supply a username to the named query. What happens? Would the query return the results of the rest of the filters, regardless of the username, or would it only return the results with the rest of the filters that have a null username? ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [December 21, 2021, 10:11pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/13 "2021-12-21T22:11:37Z")
Sure, this named query is setup for `system.db.runNamedQuery` to _ **only** _ supply the parameters you want to filter by. Notice that the `is null` part is looking at the passed parameter, not the user field in the database. When you omit passing a username, then the user parameter is null, so `:_user is null` will be true and the `:_user in (user,user2)` part will be false. Since these two clauses are ORd together, it is essentially a `1=1` scenario so the null parameter will have no effect on the query. It is important that each _short-circuit_ evaluations are ANDs. If they were ORs, the entire database would be returned. ```auto system.db.runNamedQuery('getPillowHistory',{'_serial':serial}) ``` This example query, will return the record matching the serial param ```auto system.db.runNamedQuery('getPillowHistory',{'_style':style, '_fabric':fabric}) ``` This query will only return records where both style and fabric fields are matches. ---
**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\ **Post date:** [December 21, 2021, 10:13pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/14 "2021-12-21T22:13:16Z")
This is great. Thanks! ---
**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\ **Post date:** [December 22, 2021, 6:01am UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/15 "2021-12-22T06:01:20Z")
Thank you guys for the tips! I updated the queries also as suggested by @dkhayes117 that really helped. Also I smoothen the script using @pturmel `later.py` which is running fine. Only thing I faced for one longProcess which I called with `later.callAsync`, I got this _**java.util.concurrent.CompletableFuture@3251f305[Not completed]**_ However I did achieve the result by making inner function and calling the same (wo invokeLater as Ignition gave me an error that invokeLater not defined) and later calling my long process with `system.util.invokeAsynchronous` Thanks people! ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [December 22, 2021, 2:15pm UTC](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/16 "2021-12-22T14:15:03Z")
While what your SQL guru touches on in the article is true, any DBA worth their salt will tell you that through careful analysis of your query patterns, you can optimize the DB tables with the appropriate indexes such that the DB query optimizer will choose the appropriate indexes for scans such that your data comes out sorted for the intended ‘ORDER BY’ clause. And even if an appropriate index isn’t available, other steps in the query plan can be taken by the optimizer to make the sorting very fast. And yes, even after all that it may still have to do some ‘expensive’ brute sorting, but except for trivial cases, the DB will be much faster at it than the jython in your scripting. HTH. -Shane