Has someone done some testing on the speeds of SQL vs Scripting?
I don't really know the speed differences.
In a few spots I run a query at about 0.035s.
I run a script transform to add a row infrequently to spotlight some totals.
This is literally an impossible question to answer in the general case.
Network transfer speeds are likely going to matter much more here than individual optimizations. After network transfers, who really cares if a script goes 100 milliseconds faster than a DB, or vice versa? Use whichever tool is most maintainable, which is going to depend on your particular environment.
7 Likes
I mainly want to be sure I can do things in under 120ms.
If I understand you correctly, the network transfer speed is the current bottleneck for speed.
If you are doing is data crunching/filtering/group by-ing with data and both the Ignition Server/SQL Server have reasonably comparable amounts of resources then SQL will smoke a jython script, and the only reason it wouldn't is due to a slow network where the DB is on another machine.
However maintainability is a metric you should be preferential too since you will be the one maintaining the system (or at least do the next person a favor and make it straight forward).
Many people think that all business logic should be done in your application/jython layer. I am pretty partial to thinking this way as well. The end user uses your Ignition client whether it's perspective or vision so it makes sense that all the logic that will be done by Ignition is done in Ignition,
I would personally find it annoying/bad design if I was trouble shooting where this row in my database was coming from, went to Ignition to see the logic that was being run, saw it was running a stored procedure, and then saw that the stored procedure was a only an INSERT statement. Why is this INSERT statement not along with all the others in Ignition but singled out to be run via a stored procedure? It would raise questions and I'd be both afraid to touch it/very tempted to get rid of it.
Personal rule of thumb for me is if it's regarding a SELECT statement with data filtering or grouping, I try to do as much as I can within SQL. If it's an INSERT statement or more likely a series of INSERT statements inside a transaction, I declare the transaction/do all my inserts inside Ignition. Only other pretty hard/fast rule I have is if I am SELECT-ing from SQL and need to do formatting, I SELECT the raw data, and do all my visualization/formatting in Ignition. Keep your data (the SQL dataset) and view (the formatting/visualization) of the data as separate as possible.
2 Likes
I was getting the impression that all business logic, maybe I am confused about the term, was done from a data warehouse or data lake.
I have a query that gets all machine performances sorted by shift and then by machine.
Then they wanted to get totals in a row for each shift.
So I did my totals in SQL via a series of unions for a while.
Then I switched to a simpler query with a script transform.
The business logic layer is often shown as the following -
I just got this off google, so for Ignition users, the Presentation Layer is the Vision window or the Perspective page (or even a gateway script which would be business logic without a GUI but ignore that for now). The data access layer Ignition handles for us - we tell Ignition what database driver to use and the credentials but after that Ignition handles making connections for us. All so that we can get data into that database.
With this model in mind the smallest unit of business logic is a single C(R)UD type of statement - you create some data, (probably don't read data), update some data or delete some data.
When a user clicks submit and you run a script to do multiple inserts into multiple tables now, now your business logic is a bit more complex, and so you should be doing it all within a transaction so that it either all succeeds and all gets inserted, or if something fails, none of it gets inserted, so that your database doesn't have these half records, where you have some stuff in some table from the part that succeeded and missing the other parts. This sort of database, one where it's all done outside of transactions and so sometimes you have half-working business logic that sometimes fails at doing everything it needs to do, become a pain.
In Ignition the smallest most easiest way to put things into units of business logic (in such a way that you can explain it to non-technical people - a big benefit) is to put them inside a transaction and within a try/except.
unexpectedError=False
expectedError=True
try:
tx = system.db.startTransaction(**myDBTransactionArgs)
system.db.runNamedQuery("insertIntoSomeTable", data1, tx=tx)
system.db.runNamedQuery("anotherTable",data2,tx=tx)
# Pretend the next line is writing to some log-type file
system.file.writeFile('somePath.txt','someData', 1)
except java.lang.Exception, e:
# Handle database errors like perhaps a unique constraint violation or similar
# Can decide if the type that was given was expected or unexpected
except FileNotFound, e:
# Handle issue if the file path does not exist or something for writing to log file
unexpectedError=True
finally:
if unexpectedError or expectedError:
system.db.rollBackTransaction(tx)
else:
system.db.commitTransaction(tx)
system.db.closeTransaction()
And now you have things setup where you can explain the control flow pretty easily to a non-technical or technical person. What happens if you insert a record that gets rejected into table 1? You have a spot to handle that. Or table 2? You have a spot to handle that as well. Maybe not being able to write to a manually created log file isn't a show stopper so you can then change how you handle and keep things going so that it closes. But this is what in my mind constitutes a single unit of business logic and how I try to do them generally.
2 Likes