View of contacts with large number of rows
Hi,
I have a table (Contacts) with more than 600k rows. Every time when I open the table it takes quite a while to see the results. I would be gratefull if you could recomend a high perfomance solution for this issue. The first thing that comes in mind - to split the "all" view for several views ("A", "B", "C",...) based on the first character in lastname of the person.I tried, but the performance is still poor. Are there other solutions?
12 replies
-
Hi EriΚ,
I've been trying to find a solution to a similar problem that has to do with managing a large amount of data, and I'm trying different approaches. I believe that creating a custom HTML table is a better option than using the default Ninox tables, as I will explain soon in the following post:
-
I found that formulas can slow down display time.
Another way is to use a Dashboard to interact with your data. You are probably filtering your table, so why not setup a Dashboard where you are typing/selecting your filter parameters and then only show those that match.
-
So - I have a table that has currently grown to over 900k records and what I have learned is this :-
Do I need to load all the records all of the time? No
Do I want to keep all, these records within 1 table? - definitely
Thus would creating a dashboard with views or simply having various prefixed table views with filters give me what I require and quickly enough. Either works okay ….
Based on me being on the web version, the thing to bear in mind is that the data is not local and there has to be a trade off re the search requests and resorting you want to do to displaying at my end - are you going to filter back and forth from the Nixox server or do you want to to do all the processing at the server end before displaying the results.
I do mine by using “ do as server” for table forms that have an embedded table views on them and I restrict the fields to only those that I need to return to view as much data as I need and with only essential formulas fields etc.
just this command alone works wonders on large data sets with select commands.
I keep my All records table as a backup but also use pre filtered table views for common searches ie all records with purchase in last 12 months etc.if you have other users that will process/access the data then you probably might want to build a dashboard with user restrictions to stop them selecting all data in one go.
Ninox claim that all fields are indexed (optimised) for their servers etc. However you will also see that in edit mode you can set a field to indexed or not - I intend to Not index any field that is not crucial to be searched on and also turn off global searching too on my large data tables.
Also if you call All of the table then usually it is only that first return of data that takes the time (of course!) and any other request is then called from that cached data).
I don’t do this now because I work a different way but what I used to do was..
add the the open table command to the db start up script so this table was loaded and displayed at the start of my working day. Thus any searches were more or less a few seconds rather than a minute plus !
I accept this adds a couple of mins to the starting of the DB but it all depends on how important to you it is to have the “all” table view showing all records …. -
Thank you guys. Appreciate your help and insights. One more thing related to perfomance: I am planning to deploy onpremise version, any ideas about server parameters (VPS vs dedicated), cores, RAM, etc? OS (Windows Server or Linux)? I so minimum requirements on ninox manual, but what about actual parameters for database with tables with up to 20mio rows?
-
Thanks a lot, guys. I have to host in my country due to local regulations related to personal data.
Content aside
- Status Answered
- 5 mths agoLast active
- 12Replies
- 126Views
-
5
Following