Scripts Sent to Gateway to Execute

Ignition
1

One problem I have is when I need to run several or many database queries that depend on each other in the client. This takes a long time because of the network traffic. Each query has to wait for the one before to reach the gateway, execute, and come back to the client with the results.

For instance I might have a Save button that a user needs to press to save some information. Let’s say I have an event script on the button that needs to run 5 database queries. This causes the button to work slowly because of the network traffic of having to send and receive 5 queries back and forth between the client and the gateway.

Normally if I ran into such a circumstance I would write a stored procedure in the database that would contain the various database queries in it and I would call the stored procedure from my event script. This eliminates the back and forth traffic and can speed things up dramatically.

Instead of writing a stored procedure it would be very nice to be able to write a script or function and be able to send it to the gateway and have it run on the gateway, not the client, and then send the results back to the client.

There are gateway scripts of course, but gateway scripts don’t have a good way to communicate with clients. I am suggesting that there be a way for a client to send code to the gateway to execute and then return the results back to the client for the purpose of eliminating needless traffic overhead of the back and forth of the client and gateway.

2

Or perhaps a way to call a gateway script from a client and return the results to the client.

3

Hmm… what about using separate Tag Change Scripts, and then trigger them with the same tag? Seems a bit cumbersome, but it should reduce your turnaround time. :scratch:

That’s an “off the top of my head thought”. They’re usually not worth much. It’s why I give them away for free! :laughing:

4

I think some form of this is a good idea. The ability to call gateway scripts and get the result sounds good. The only downside right now is that there isn’t really a good separation between gateway and client scripts- the custom script modules get loaded in both. Anyhow, maybe a new “system.util.runGateawayScript(“name”, [params…])” could work.

Regards,

5

Colby,
That sounds great. And if system.util.runGateawayScript() would return a value from the gateway script or function that would be great.

6

If I understand this correctly, for it to return a value it would need to be run synchronously which means the client would either need to invoke later in a wrapper so that the result can be placed in some “other property” or the client would still appear “stuck” while waiting for the return.

Perhaps two Gateway runners? one that returns a value & one that doesn’t :scratch:

As you can see I too like to put in my two bobs worth :laughing:

7

Can you wrap the 5 queries up as a transaction?

8

No, because each of the 5 queries would be individually going to server and back to client, which is the network traffic that I want to avoid.

9

What is the nature of the queries?

Using embedded queries you can combine multiple selects into a single query.
eg:

select (select id from table_a),
         (select col2 from table_b where index = 5),
         (select 2*col4 from table_c where col7 = 'text'),
         col_c
from table_42
where col_a = col_b
   and (col_d < col_f)
10

Thanks Robert. That’s a good idea.

It reminds me of MySQL’s capability to make multiple insertions in one insert query. I’ve done that before to make multiple insertions faster.

11

I did this once with about 8 separate complex queries. I ended up with a 50+ line select statement that looked really ugly that executed blazing fast. When you do this, not only do you reduce the round trip traffic time, the query optimizer in the DB can combine index searches and table lookups to run the query in less time.

Of course profiling is the key because if you do something wrong you can end up with a query that returns the entire database :frowning: (also something I have done (not one of my finer moments :laughing: ))