How to run Script faster

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!

Sound ideal to enable cache property for that named query. Are you using named query right?

Yes but I don’t face much delay while getting the record from DB. The problem comes with script transform because of lengthy data

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) as last resource.

Then we need to see the script.

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.

1 Like

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.

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)
6 Likes

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.

1 Like

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.

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:

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.

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?

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.

system.db.runNamedQuery('getPillowHistory',{'_serial':serial})

This example query, will return the record matching the serial param

system.db.runNamedQuery('getPillowHistory',{'_style':style, '_fabric':fabric})

This query will only return records where both style and fabric fields are matches.

2 Likes

This is great. Thanks!

1 Like

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!

1 Like

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

1 Like