query as source, one buffer
Time between Binding-Source = query-handle, DataSource = Binding-Source, Display, 19300 records
Clicking on a col header (sorting, int field):
Telerik: Canceled after 5 minutes, still access to table (shown in promon)
Infragistics: 5 seconds
But even Infragistics queries the table when I click on the col and the doc says something else:
Most .NET grid controls support sorting in response to some user action. However, the grids might differ on how the sort is accomplished. For example, the Infragistics.Win.UltraWinGrid.UltraGrid does its own sorting if the DisplayLayout:Override:HeaderClickAction property is set to SortSingle or SortMulti
I have the Default Value: SortMulti.
Why do I have database access?
Using the Sort Event to rewrite the query with ExternalSort is very fast for Infragistics.
But the group function, moving col in the header is still slow and the sort event is fired after the grouping.
Additionally, when I simply add an alert-box in the handler of the AfterSortChange event, the grouping doesn't work properly.
Generally speaking, a grid will not (and absolutely should not) read all the query data up front. It asks for only as much data from the temp-table/database as it needs to do its display. When you scroll down, it will read more. When you sort, ultimately all the rows need to be read in order to know what the correct sort order is. So every record (or at least the sort column value from each record) needs to be read at least once. Once the data is in memory the grid can do the sorting without further tt/db access.
Does that answer your question?
I tried to verify your statement. This would make sense but then 2 things are strange:
How does the Infragistic Grid knows the amount of records within the same time a for each needs to get the data?
(there are many threads about counting records, just one a few days ago)
I sorted by every column, I scrolled through all entries, then it should be in memory but this doesn't change anything. Still I/O on the table.
If anybody is interested in my result ...
Infragistics is faster in all, especially with unbounded data.
The advantage is that they have an own bindingsource, there I don't need to create objects within ABL.
Loading the same amount of records needs < 2 seconds (pure data read only 0.2 sec)
But then I can sort, group and everything what is possible in a part of a second.
Re I/O on the db table after scrolling, etc. Yes, sorry, my last sentence was probably not accurate. I believe the control doesn't cache records. It keeps asking the BindingSource for data each time it wants data from a row, even if it has read that row before. This is typical of how all controls work with the BindingSource. The control really can't cache all the data if the # of records is large. Yes, there could be complicated caching schemes, but that also adds time and complexity and they've had no reason to do that thus far.
Of course things are much faster when the data is stored in a .NET DataTable. That is basically just a 2-dimensional array. On the other hand, when accessing data from a Progress DB/TT, first of all it is random access. We are not going next, next, next. We are being asked to get data from a particular row, so we first need to position to that row in the query. Then we fetch the data (which may require I/O) and that data then needs to get marshalled from unmanaged to managed code. I am sort of surprised however that it is so slow with only 20,000 records (substantial, but not huge). But with sorting, the same record tends to get read many, many times, depending of course on how many swaps and compares it takes to do the sort.
In any case, we are aware of the issue. I know you already have a case open with Telerik. But even if we can improve it, it will never be as fast as binding to a .NET DataTable.
I had the same issues with the Telerik Gridview.
- Sorting was extremly slow
- Filtering didn't work with a ProBindingSource and UseFullFieldNames (workaroud was to rename the columns in the grid from "Customer:CustNum" to "Customer|CustNum".
But since some days there is a new version available : R3 2016.
It's much faster and all of the above is working now. It tried it with about 2000 records in a Temp-Table / Dataset.
Wow, thank you Thomas. Meanwhile I thought I am too stupid to use it because in 2 years after the acquirement nobody of the ABL professionals complained about that.
I wondering about that Telerik support did not tell me about it and getting this info from you first.
For my last unbounded test (R2 2016 SP1) I got the info to use BeginUpdate and EndUpdate to increase the performance. With that it's much better and now useable but compared with Infragistics own datasource, which I can fill manually, it's still > 2 times slower.
My expectation is to handle records < 100000 without problems in a grid.
I tested to use a Datatable and fill it with write/read-xml through a memory stream but it's not faster than Infragistics manual load.
I am going to get this newer version and run my tests again.
Fast test with Q3 version, I can't notice any differences with OE 11.5.1.
Install Q3, remove components and assemblies from PDS, remove control, add all controls from Q3.
Placed new radgrid on form without any events and used query direct to the database table.
create query qh.
ss = substitute("for each T60Logg no-lock where T60Logg.DatLog >= ""&1""",dt).
this-object:bindingSource1:handle = qh.
this-object:radGridView1:DataSource = this-object:bindingSource1.
Loaded (partly?) fast but sorting not really possible, it still seems to read the table thousand times ...