Pros and Cons of Increasing servoy.foundset.pkChunkSize

Questions and Answers on installation, deployment, management, locking, tranasactions of Servoy Application Server

Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby chaitanyas » Sat Oct 22, 2016 1:29 am

Hi Everyone,

My application is big in size (In terms of no.of records, for ex: more than 300k) and we are facing performance issue when load list view of forms. Yes, we tried removing onRender events, calculations on the list but still the performance doesn't improve in a big manner. We need to search the list view of the forms alot, which takes ages sometime.

I found , servoy.foundset.pkChunkSize property, which will be used to increase the foundset chunksize from default 200 to xxx. Can you please explain what are the consequences of setting up this property and what could be the recommended size and is this leads to another problem ?


Thanks In Advance
chaitanyas
 
Posts: 101
Joined: Tue Jul 26, 2011 8:28 am

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby Bernd.N » Sun Oct 23, 2016 12:30 pm

That would not be the first thing I would try. Maybe some questions help...
Which system do you use (SC, WC, NG)?
Is the performance problem new?
Is it necessary to hold 300k records in that one table? :)
You said "list view", did you also try the "table view"?
Do you have the same problems when you create a very easy table form with just one normal field, without any other (maybe related) fields?
When you have the form open, does it have a specific order, and is there an index on the table that supports that order?
Finally, did you check the Application Server regarding the SQL performance statistics? You can delete those, invoke then the one action with the table listing and have a look which SQL command will be executed (reloading of the AppServer-page is necessary). That way you would detect for example a full table scan, which could be the reason for the slow performance.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby chaitanyas » Mon Oct 24, 2016 12:30 am

Hi Bernd,

Thanks for the reply. I am using smart client and no, the performance problem is not new.

Sorry, I am actually using "Table View", I was mean to say , List and details views.

Is it necessary to hold 300k records in that one table? -> Actually, we have filters and these filters are very slow to retrieve the results, sometimes, filter need to hit the very old record and at that time then the list loading very slowly.

When you have the form open, does it have a specific order, and is there an index on the table that supports that order? -> yes, it has specific order by some db field.

Finally, did you check the Application Server regarding the SQL performance statistics? -> Yes, I did checked performance statistics and none of them seems to run slowly, all are good.
chaitanyas
 
Posts: 101
Joined: Tue Jul 26, 2011 8:28 am

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby Bernd.N » Mon Oct 24, 2016 2:19 am

You said it has specific order by some db field, but you was not explicit regarding indexes.
So I do not know at the moment if you guys use indexes to support fast data retrieval.
In any case a look at
http://use-the-index-luke.com
may boost up things.

Actually, we have filters and these filters are very slow to retrieve the results, sometimes, filter need to hit the very old record and at that time then the list loading very slowly.

My feeling is that the slow performance is not related to Servoy's pkChunkSize, but more on the filtering.

I do not know your exact use case, so I can not tell if the 300K table could be splitted into an archive table and a "current_data_table" to make it faster.
In any case the filtering should be optimized with the use of indexes if not yet done.

Also you could try if a use of directSQL makes sense for some filtering, I mean not to use the Servoy find mode but to select the needed records with an optimized SQL-SELECT into your foundset. Also test the namedFoundset=empty property, so that the table form starts up fast with an empty foundset and waits until the user tells it which records should be shown.

My bottom line feeling is that performance can be optimized. In case the above suggestions did not help, a consultant with a lot of Servoy experience may be of help, as he/she brings in other perspectives and other experiences that could add value to your experiences.

I just had a look at your website, and I am now curious where this 300K record table stems from, I mean what you store inside that. For a drug table it seems to be too big, e.g. in Germany we have 50K of different drugs... (pharmaceuticals). :)
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby Bernd.N » Mon Oct 24, 2016 2:56 pm

One more suggestion:
The users will for sure not need to scroll through all data. There might be fields like "category" and a date field in your table, that they could provide upfront to narrow their search, or you know that it makes sense to apply that.

In any case, on those standard fields to narrow the search, there needs to be an index. Then you have to make sure that the Servoy find() or the directSQL you use will use that index. You can do that by looking at the Server admin page under "Performance Data". Then you could copy that SQL and use EXPLAIN in front of it, to find out if the database will use that index.
So it should be something like WHERE category_field = 'xyz' AND date_field > <<some_date, e.g. begin of last year>>>.

When the WHERE narrows down your search with those two indexed fields first (so they should really come as first fields after the WHERE, afaik), then the DB can use the indexes and the other filters need to operate only on a small part of those 300K records. That should boost up things, in comparison to a full table scan that might be involved currently.
Hope it helps.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby chaitanyas » Tue Oct 25, 2016 1:38 am

Hi Bernd,

I will check indexes and can you please share what are the consequences of increasing foundset ph chunk size ?
chaitanyas
 
Posts: 101
Joined: Tue Jul 26, 2011 8:28 am

Foundset Chunk Size

Postby Bernd.N » Tue Oct 25, 2016 3:00 am

Sorry, I am not an expert regarding that topic.
But I also think it is not really necessary, as the normal chunk size of 200 gives users enough records for browsing, so that they should be busy for a while.
It might be a problem of filtering leading to full table scans, which takes place on the server.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby rodney » Tue Oct 25, 2016 5:24 am

Hi

If you are using only smart client can I suggest using the table bean to display the data. This is dramatically faster than servoy table views and we use it all over our application:

Advantages:
Speed (much faster) can load many thousands of records in a blink as it loads only the columns you tell it and not related data etc.
no chunk limit, slowing down the scrolling of records when system tries to grab another chunk. User can scroll a table of 14k records for example from start to end in a blink
Much nicer formatting options and can do per cell, per row, per column conditional formatting easily
Will automatically/dynamically resize text areas to hold all text in field without a scrollbar(setting for this) see screenshot

Disadvantages:
Need to load it with SQL (this can also be a plus!)
Setup of the table is a bit more work in servoy to set formatting etc. to make it look pretty (but is more powerful)
Not editable
doesn't data broadcast (but is so fast you can refresh it often)

BTW doesn't help if your query is not optimised or missing indexes as discussed previously
Attachments
table bean.png
table bean.png (27.47 KiB) Viewed 7369 times
rodney
 
Posts: 8
Joined: Wed Dec 02, 2015 8:04 am

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby Bernd.N » Tue Oct 25, 2016 11:14 am

It could also be that a relation that is involved is slow because it does not use a primary key.

To convince yourself that a table view is not generally slow, I would create a test table with just one plain text field of the DB table.
Then you could show it without any filtering. That should be snappy.

Then you can add filters step by step, first those which have index support.

After that you add other fields (just with copy/paste from your original table), until you have the complete original setting.

And somewhere during that test you should see the performance problem.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Pros and Cons of Increasing servoy.foundset.pkChunkSize

Postby chaitanyas » Mon Oct 31, 2016 5:08 am

Hi Bernd,

Thanks for your suggestions, I will give a try.
chaitanyas
 
Posts: 101
Joined: Tue Jul 26, 2011 8:28 am


Return to Servoy Server

Who is online

Users browsing this forum: No registered users and 4 guests