Hi Community,
I have a question on the use of the with() function on interfaces. More to do with how memory is managed when load and with is used. Say we have the following code
with( local!Variable1:rule!retrieveDataFrmCustomerTable(Filter1,Filter2),
)
What I expect it to so is when my page loads the output of the query retrieveDataFrmCustomerTable with 2 filters is stored in the Variable1. Now if i just call the variable2 in my code lets say 10 times, will APPIAN execute the "rule!retrieveDataFrmCustomerTable(Filter1,Filter2)" ten times or will it just keep playing with the initial data which was called when the with function was executed.
The reason i ask is to seek clarity on the fact whether the Variable1 is just a pointer to the retrieveDataFrmCustomerTable query and is called each time Variable1 is called which will make a call to the back end data base each time or since i have used Variable1 alongside with() the retrieveDataFrmCustomerTable query will be run just once and it will be kept in the memory for APPIAN to use.
Discussion posts and replies are publicly visible
When you call a query in a with() it runs the query every time the user does anything. ANYTHING. Whether it has anything to do with the variable in question or not. You are right that the query doesn't get reevaluated every time you use the local! in your code, but that should be far less than the number of times a user will interact with the form. You can use the same local! 8000 times and it only gets rerun once every time a user does anything.
Let's say you query in a with() on the top of a page with a grid and some text fields. You query the data and use it to populate the grid.
The query gets run when the page loads. The query gets run when the user clicks on the form. The query gets run when the user checks one of the rows. The query gets run when the user clicks on one of the text fields. The query gets run again EVERY TIME THEY TYPE A LETTER INTO THAT FIELD. So if their name is 12 letters long, that's 12 more queries. The query gets run again when they click on the interface to loose focus on that text field. It gets run again when they click on the next text field, and for every letter in that one too. It gets run again when they press the submit button. Even though none of this stuff updates what the query actually returns.
Don't use a query inside with(). I remember a team on a project I was on finding a slow query that was being run an average of over 1000 times every process.
The solution is to load all the data from the database in a load(). You'll want to filter to only what you need for the particular grid as much as possible at that level. Get all of it once when the page first loads. Then, put that data into a datasubset variable in a with(). Now, page through the datasubset, not the query. You'll reevaluate the stuff in your datasubset every time the user does anything whatsoever, but you won't hit the database again. Now your paging and sorting on the grid will work without destroying your connection pool, and you'll have a much faster form to boot.