Jarne Staelens (Dochy Glas) asked a question.
In my application, I have a lot off these code:
- FOR EACH <db_record> NO-LOCK:
- CREATE <temp_table_record>.
- BUFFER-COPY <db_record> TO <temp_table_record> .
- END.
And then I have a query and a Progress.Data.BindingSource for that temp_table.
A few lines later, I open the query and assign it to the BindingSource:
- QUERY q_<temp_table>:QUERY-PREPARE("PRESELECT EACH <temp_table>").
- QUERY q_<temp_table>:QUERY-OPEN().
- bindingSource_<temp_table>:HANDLE = QUERY q_<temp_table>:HANDLE.
This works well for small database tables, but when the number off records in the database increases, then this becomes very slow.
If there are less than 2000 records, it is ok. The from then loads instantly. But with more then 2000 records, then it becomes very slow and my form then takes a couple off second to load.
How can this be improved, if the database has many records, like 15 000 or 20 000 records. I can add a WHERE-clause to the for each, but then my users complain that there is data missing (data that is filtered out with the WHERE-clause.
Can this be improved, so that if I have 20 000 records, that the from than takes less then 1 second to load?
Do you know where which code is slow? The temp-table population or the form visualisation? If you are reading an entire DB table that is going to take time, and the larger the table, the more time.
I think you are going to want to look at batching / paging the temp-table data. The user can only see so many rows anyway (50? 100?).
The article at https://community.progress.com/s/article/How-to-bind-a-bindingsource-to-a-datagridview-with-batching talks about ProDataSets but you should be able to apply the principles to plain temp-tables.
The short answer is: You have to implement batching/paging. The ProBindingSource can be set to batching and then the OffEnd will fire when the initial let's say 100 records have been scrolled beyond and that's the request for you to add the next 100 records.
A temp-table with 20000 records is far from efficient. So you'd only be dealing with what the user really sees.
Beyond that look for passing your data by-reference to avoid deep copies.
https://community.progress.com/s/article/How-to-bind-a-bindingsource-to-a-datagridview-with-batching
Thank you, I will try to implement this. So if you temp-table contains 1000 records, and the batch-size is set to 100, then only 100 records will be visible in the datagridview, and the remaining 900 will still be in the temp-table but not visible ?
The batch size should match what's in the temp-table. 1000 is IMHO already a lot of overhead. Most users will never scroll that far.
The scrolling is not the problem, my users want to filter the data.
I have a customer table in my DB, and the name in alphabetical order. If a user filters a customer with name "a*" then he would see results, but if he wants to filter something that start with "z*", then that would not be in the temp-table, if the temp-table is not big enough.
Because off the filtering I would then need a temp-table off 3000 records.
Is there any reason not to refill the temp-table (ie re-query the DB table) when the uses selects "a*" or 'q*' for a name? So the temp-table only contains records that match the current filter criteria, and if that criteria returns more than (taking the ADM2 default) 50 records, then batch the results.
When the user changes the filter criteria, get new data and repeat.
No, I tought that that would be very inefficient. Eah time the user filters, re-query the DB table, and the refill the temp-table. But I could try that. So then I need a dynamic query with a cWhere and make the cWhere mach the filter that the user uses. If a use a cWhere then the temp-table has no more than 50 - 100 records, then it would be more efficient, even if you contantly re-query the DB.
If code like this is common in your application, the performance problem might be compounded by caching limitations.
Temp-table data remains memory-resident in the client as long as the temp-table database buffer pool (-Bt) is large enough to hold all of the data in all of your temp-tables (and their indexes, and a bit of metadata). Once your TT data grows to the point that that is no longer true, temp-table data and index blocks are out-paged to the client DBI file on an LRU basis. So data access can look like this:
So without tuning, this can be more expensive than just reading data from the DB table and not caching it locally.
Look at the size of your client session DBI files; note that session temporary files are hidden/unlinked by default, use -t to override that. If the data is larger than -Bt, the minimum size is data size - Bt (or 8 * -tmpbsize, whichever is larger). Depending on data-access patterns, the file can grow to the full size of your TT data.
If you use OpenEdge 12.8.9+, you can use the OpenEdge Memory Profiler to get insight into your TT life cycle.
In 11.0+ you can look at the temp-table VSTs to gain insight on how large these temp-tables are growing.
Yes, thank you, I will have a look at my DBI files. This is only a problem my biggest table in my DB for my application. The other tables are smaller and not causing any delays.
Performance may be OK now and in your dev/test. But we all hope you're very successful and many users use the application in prod. Performance issues may hit suddenly, when cache sizes are exceeded and then they feel like running against a wall with a sudden stop.