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.
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.
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.
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.
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)
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.
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.
Thank you guys for the tips! I updated the queries also as suggested by @dkhayes117 that really helped.
Also I smoothen the script using @pturmellater.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
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.