Skip to main content
Solved

max_no_of_records

tiago
Captain
Forum|alt.badge.img+5

Hi All,

 

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? 

Best answer by J. de Lange

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

4 replies

J. de Lange
Hero
Forum|alt.badge.img+4
  • J. de LangeHero
  • Hero
  • 69 replies
  • Answer
  • March 17, 2025

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.

Freddy
Ricky
2 people like this
  • Freddy
    Freddy
  • Ricky
    Ricky
M
Superhero

​@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.
 

Freddy
Ricky
2 people like this
  • Freddy
    Freddy
  • Ricky
    Ricky
Alexandra Egri
Moderator
Forum|alt.badge.img

Hello ​@tiago,

Could you create a ticket for this in TCP so we can investigate it further?

Thank you!

tiago
Captain
Forum|alt.badge.img+5
  • tiagoCaptain
  • Author
  • Captain
  • 47 replies
  • March 25, 2025

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. 

 

 

Ricky
J. de Lange
2 people like this
  • Ricky
    Ricky
  • J. de Lange
    J. de Lange
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings