I'm working on a POC for a customer that wants to continue to have insights in their history of an application that they will stop using. The idea is to build vieuws on top of the existing database. there is a table with more than 6 million records, and it doesn't perform. it takes minutes to load a grid of this view.
i'd expect the max_no_of_records to limit the amount of results and thus improve perofrmance, but it I doubt it is having any effect. in the dev tool I do not see any form of limiting, aside from the page size, but the amount of items is still 6000.000 where I would expect the limit I set in max_no_of_records.
Is there anything wrong in the interpretation of the max_no_of_records. Suggestions on how to improve performance whilst still being able to access all items?
Page 1 / 1
You should be able to get good performance by setting the page size. Just make sure that the sort order defined in subject settings of your view matches the clustered index of the table being queried by your view. Otherwise SQL server may have to read every single row before determining which rows should be added to page 1. The software factory automatically adds non clustered indexes for the sort orders you define, but that won’t help you if you program a view querying a table without the index.
@tiago We also use the “Max. no. of records” and it has some rules for different options how it will work.
For more details see the response by Arie V here:
For screens where we want to limit the amount of result shown, we use the limitation of “Max. no. of records”. Else we use “Pages”, with a rare occasion to show all, even without pages.
The main performance we gained with “Max. no. of records” and not using pages is that there is no COUNT(*) call also happening. In our specific case that halved the loading time.
Example of our setting:
The “-1” can probably be 0 now as well, but this is still from an older situation where the Universal GUI only looked at -1 to no use pages.
Hello @tiago,
Could you create a ticket for this in TCP so we can investigate it further?
Thank you!
Thanks for all the responses. As it was a POC I showed the solution with a top 10000 in the view. If the customer decideds to go with us, I'll investigate it further. We probably will go with a solution where we will unify all the scattered data in a table thats parte of the Thinkwise model, so we have better control ove rthe performance.
