Hi All,
I have created a CDT using Database View. Assume that the view is returning 10000 rows in DB. I have a rule with query entity with filter and a paging with batch size 10.
Will Appian pulls all 10000 records first and applies filter/batch?
OR
It applies filter and only pulls 10 records?
How the query works in Appian side?
Thanks,
Varsha
Discussion posts and replies are publicly visible
Hi,
Appian first apply filter in the view and pull the record based on the batchSize.
Thanks
Vinay
Help Varsha,
Internally the query entity is creating a query before hiting the database.
So if you explicitly request the paging info(1,-1) <- where batcsize as -1 means “bring everything”, internally it will execute a query without paging or limits.
So if you are quering just for a!pagingInfo(1,10) you will get 10 rows(batch size 10). The interesting part of this and I recomend to take a look is the indexing used on the view and the use of filters to make the query performs well. Else the query could perform poorly because it has to match the joins everytime against all the tables.
To understand that use something like the “explain select * from view”, but If you are interested in the same we can provide more details.
Hope this helps
Jose
Hi josep,
Does this mean if we navigate paging for paging grid 5 times to view 5 different pages, Appian will query database 5 times?
Yes, that is correct. What actually happens when you click the next page button is that the start index is updated to 11 and the query is executed again with the new paging information. A new query is also executed when you change sorting or update filter criteria.
If for some reason you don't want to query for every page, you can create a larger initial batch size and then use a!dataSubset() or todatasubset() to create a subset of the larger query that can be used in the grid. However, it's usually a good idea to only query the data for the current page to ensure the first query runs as quickly as possible.