Batching and sorting sample

I guess I should give a short summary of where the actual batching code is as the attached data service and access code includes both basic and advanced stuff intended for all FCS samples, and less important for the batching.

The actual running sample is Batching/BatchingForm.cls. You can run it directly from OEA, but there is also a runner.p as in the other samples allowing you to run this in any progress session or directly at the start with -p. The difference from the other samples is that the parent directory of the Batching directory is the start and propath directory.

Presentation Layer

The BatchingForm has an UltraGrid and some controls that allow you to test batching sizes and sort options. The controls are somewhat irrelevant for the batching topic, except that BatchSize is set in the service on the ValueChanged event of one of the controls.

The actual code that manages the batching and sorting is implemented in the UltraGrid's AfterRowChange and the BindingSource's OffEnd events and the GetOrderLineDataSource method that starts and fetches data in an OrderLineService.

The BindingSource's Batching property is set from a HasLast method in the Service after any request.

Logic Layer/Adapter (temp-table layer) - DataServices directory.

The OrderLineSource used in the sample inherits a DataBatchingService, which implements IDataBatchingService with properties DatasetHandle, BatchSize and HasLast and methods FetchData(), FetchNextBatch() and ResortQuery().

FetchData retrieves the first batch from the Data Access Object while FetchNextBatch calls the Data Access to do the actual bathing. ResortQuery deals with local sort and calls FetchData with the new query string if it is batching and need to do the resort in the data access.

The methods use two private members to keep track of the current sort expression and the next batch rowid.

Data Access - DataSources directory

The DynamicDAO is a dynamic class that handles the data access. It implements IDataAcess which has three fetch methods. We use the same FetchBatch method for all data access.

Attachment

I'm attaching a new edited and slightly reorganized readme.out file with a bit more details about the actual code. I've also added a Performance and sorting and/or batching section, that points out that sorting on large tables without index support can be slow and that this might be an issue on each batch request. These are issues that most ABL developers are aware of, but might forget when moving away from the database.

Minor edits by:

Havard Danielsen

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/3288.mEdtScr.w.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/2766.mEdtScr2.w.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/7701.mEdtScrLaunch.p.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/1680.test1Form.cls.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/1641.test1Form.resx.zip:550:0]

Hi Havard,

I tried your sample and the DataService class did not work in the beta. I have attached a fix.

Nice sample! The next challenge is to include filtering... we are working on that now.

-peter

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/6332.mEdtScr.w.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/8231.mEdtScr2.w.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/7801.mEdtScrLaunch.p.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/7446.test1Form.cls.zip:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/4213.test1Form.resx.zip:550:0]

Ouch this turns out to be a can of worms... AFAICS there is no such thing as a BeforeFilter event. The documentation talks about a 'Filter Evaluation Trigger' but how do you intercept it?

Clarification: the ultraGrid comes with a nice filtering feature. You can enable filtering on the headers or even have an automatic filter row (see attachment). The same rules that apply to sorting apply to filtering (in combination with batching): it has to be done on the server. But how do you do that?

Havard,

I get a code parser error when attempting to open.

"The language did not provide a code parser for this file. Please make sure that this file type supports a designer.

Could not load BatchingForm.cls".

If I use the ABL Editor I can see the code just fine.

Then you probably did not place the provided assemblies.xml in your project root directory.

Then you probably did not place the provided assemblies.xml in your project root directory.

Another victim of the big pause... Any progress on the filtering part?

Okay, this was the issue. I had to replace the assemblies that was already there.

The dataservice.cls does not seem to like the following:

dataColumn = dataTable:Columns:Add(fieldHandle:Name,NetType(fieldHandle:Data-type))

Any ideas.

I forgot the details:

Paramter 2 for method ADD is not type compatible with its definition (12905)

I posted the fix in this thread yesterday.

I saw that and downloaded the cls with the same results. I will try again.

Must be the guy sitting in the chair!! I cut the code from the forum this time and pasted and it works.

Thanks!!

Thanks for the beta fix, I forgot that you guys don't have the luxury of a fresh install each morning...

The BeforeRowFilterChange and AfterRowFilterChange fire when you AllowFiltering on a grid column. I assume they also work with the Fixed RowFilter feature, but i have not tested this.

I haven't tried to make these events work with server requests. (They are somewhat geared towards local filtering). The event argument object passed to the event has a Cancel option, that I assume you can use to take complete control. It is fully possible that this can be achieved without canceling the default filter though.

Grid filter code should ideally take into account the RowFilterMode and RowFilterAction options on the grid's Override. Some of the modes shows all data with filtered data with a different appearance and does not fit with server filtering (probably easier to implement though.) The frmFilterRow in the WinGrid/ExplorerSamples demonstrates the RowFilterMode with many other filter options).

The default filter has options like 'all', 'blank' an 'non-blank' which one would need to deal with. The frmUltraDataSource2 sample shows how to remove and add values to the default column filter options. You might want to support some of these options of course, but it probably does not make sense to let the grid add its own values to this list if you are batching or if the column filter is to apply the criteria on the server.

Havard,

Where do you find these events? I don't see them among the events and cannot find them in the documentation either.

UltraGridBase Class Members, Public Events...

You should see these events in the event tab in the properties view when the UltraGrid is selected in the Visual Designer. (As you know I don't have the beta, but they should be there to.)

I am seeing it. Don't know why I missed it before.

I am now trying to implement it which is not easy as ultrawingrid does not seem to be designed for server-side filtering. Will keep you posted.

The BeforeRowFilterChange and AfterRowFilterChange

fire when you AllowFiltering on a grid column. I

assume they also work with the Fixed RowFilter

feature, but i have not tested this.

I haven't tried to make these events work with server

requests. (They are somewhat geared towards local

filtering).

They certainly are! I have been struggling with this now for a while, and what I want to do of course is collect all filters on the client and transmit them to the server in order to reopen the query (once, not for each column entered).

However, there is no event that fires for the entire filter set and I also have not found a way to find out what the last modified filter column is.

Canceling the BeforeFilterRowChange event sounds like a good idea but the side-effect of that is that filters are not assigned and therefore not remembered. This applies especially to custom filters. However, if I don't cancel the BeforeFilterRowChange event the filter is applied locally as well. One would think that that does not matter, but it does. The Infragistics filtering results on data with special characters is quite different from the Progress result.

So all in all I have not gotten much further on this and welcome suggestions!

Is it possible to include a server based solution for grid filtering in the samples for FCS?

I think most people using the grid will need batching, sorting and filtering...

Is it possible to include a server based solution for

grid filtering in the samples for FCS?

I think most people using the grid will need

batching, sorting and filtering...

Actually, we need batching and filtering, not sorting.

It is not a good idea to combine filtering with sorting since filtering results in sorting by itself.

They certainly are! I have been struggling with this

now for a while, and what I want to do of course is

collect all filters on the client and transmit them

to the server in order to reopen the query (once, not

for each column entered).

However, there is no event that fires for the entire

filter set

The UI is somewhat column oriented, so I actually suspect users would expect an immediate response when a value is chosen in a filter combo...

In any case, you can control when the filter is evaluated by setting the UltraGrid:DisplayLayout:Override:FilterEvaluationTrigger to one of the Infragistics.Win.UltraWinGrid.FilterEvaluationTrigger options. These options include OnCellValueChange (Default), OnLeaveCell, OnLeaveRow, OnEnterKey as well as some combination of those, like OnEnterKeyOrLeaveRow.

and I also have not found a way to find

out what the last modified filter column is.

The column is exposed in the BeforeFilterRowChange event on the

e:NewColumnFilter:Column (e is the second parameter to the event)

Canceling the BeforeFilterRowChange event sounds like

a good idea but the side-effect of that is that

filters are not assigned and therefore not

remembered. This applies especially to custom

filters.

This is the supported way to suppress the grid?s built in filtering. Developers might want to use this also when all data already is on the client, since the UltraGrid filtering (of course) is slower than filtering with an ABL query. (I do not hold this against the grid, data without SQL is after all one of our key differentiators)

As you point out, if you bypass the grid's filtering then you need to keep track of the filter conditions yourself. As far as I can see the UltraGrid expose all information you need to do this. The BeforeRowFilterChange event gives you access to the recently applied ColumnFilter object through the NewColumnFilter attribute on the argument object parameter. This exposes both the Column and the FilterConditionCollection. I assume this collection have the custom filter conditions and their operator.

Note that even if you set the filter evaluation to fire on LeaveRow or EnterKey, the Before and AfterRowFilterChange event will fire for each column.

Keeping track of the filter conditions might not be trivial, and it is probably a rather bad idea to resolve this in code in each class that has an UltraGrid (somewhat depending of the complexity). But I really think you cannot have a layered architecture without client side query manipulation support with the ability to issue server requests when needed in some form. (I cannot help noticing that the UltraGrid filter BeforeFilterChange event and arguments seems to be an exact fit to adm2's assignQuerySelection and removeQuerySelection.)

However, if I don't cancel the

BeforeFilterRowChange event the filter is applied

locally as well.

This is kind of the point...

I'm do not know when it is beneficial to use the local grid filtering and when it is not. My experience from the Dataview certainly suggests that you are better off applying the query both on the client and the server. (There are too many reasons to explain, but the Service might for example not have the required granularity or you might already have the data on the client.). It is fully possible that it might be useful to extend this to the grid also. (I haven't tested how fast the built in grid filtering is if most of the data already are filtered in the underlying query)

Even if you don't want or need to apply the filter conditions to both the query and the UltraGrid, I still anticipate that there might be cases where one might want to switch from one form of querying to another, so it might be useful to know how to access the grid's filter conditions:

The band has a ColumnFilters attribute that returns a ColumnFiltersCollection. The ColumnFilters in this collection are the same kind of object that you access on the NewColumnFilter on the BeforeRowFilterChange event. It exposes both the Column and the FilterConditionCollection with the actual filter conditions.

Note that you can also Add filter conditions to the grid with code. (See WinGrid/SamplesExplorer/frmRowfiltering AddDefaultRowFilters( )).

>One would think that that does not

matter, but it does.

The Infragistics filtering

results on data with special characters is quite

different from the Progress result.

I'm not sure what you mean, but I suggest you raise this as a separate issue.

Message was edited:

- filter conditions to both the query and the UltraGrid

- some italic removed

Havard Danielsen

Batching, sorting and filtering are general tasks that need to be resolved as part of your service separate from the actual UI. This includes sorting and filtering on client data.

I still think it would make sense to show how the UltraGrid's built in filter support can fit with an ABL service even if the actual Service shown will be simplified, so I will try to see if the FCS samples can include this. I cannot promise this though, as FCS is getting very close.

If it doesn't make it then we could continue the discussion on PSDN also after FCS.

Actually, we need batching and filtering, not

sorting.

It is not a good idea to combine filtering with

sorting since filtering results in sorting by itself.

I do think you might ensure that repositioning is synchronized with the sorting, but I'm not sure I agree that you can avoid filtering and sorting together.

(The difference between filtering and repositioning is subtle; repositioning basically allows you to go fast to data that meets the specified criteria , but also navigate to data that do not fit the reposition criteria, while filtering affects the whole result set)

If the UI allows the user to sort then the sorting will have precedence for any filter criteria.

One of the benefits of a layered architecture is that you do not need to care about the sorting in the data access layer when you support sorting on the client. But if you support batching then the data access layer has to sort the data.

t doesn't make it then we could continue the

discussion on PSDN also after FCS.

Except that the "croud" will be much bigger and it will become even harder to find a consensus...

One of the benefits of a layered architecture is that

you do not need to care about the sorting in the data

access layer when you support sorting on the client.

But if you support batching then the data access

layer has to sort the data.

I don't agree with that. If the client and the server (data access layer) use different sort order/sort algorithm the result is unpredictable for the user. Even without batching.

How will you explain users, that in one case digits might come before and in the other case after letters? How about Umlaute? Progress and Microsoft have partially different oppinions about the sorting here.

Allways sorting at the server seems like the only option to me.

Hi Mike,

I think you and Håvard are saying the same thing. When you aren't using batching it doesn't matter where the sort occurs because you are sending all records to the client in one shot.

When you are using batching you are correct that you must handle sorting on the server.

Ken

When you aren't using batching it doesn't matter

where the sort occurs because you are sending all

records to the client in one shot.

But when the client is allowed to perform any sorting - the result vary from those sort result when batching is used.

That's what I call unpredicable results.

Mike is right. Like I pointed out, the filtering is different on the server. The same applies to sorting. Think about character sets, collation sequences etc.

But like I said before, sorting and filtering are mutual exclusive. So what I would like is to do batching and filtering on the server, and allow sorting only on the client and only when the whole recordset is available on the client. This way the results are predictable and consistent.

batching and filtering on the server, and allow

sorting only on the client and only when the whole

recordset is available on the client. This way the

results are predictable and consistent.

How will you explain your users the difference in sort order depending on the fact that the client has all data or only partial data yet?

How will you explain your users the difference in

sort order depending on the fact that the client has

all data or only partial data yet?

No need:

allow sorting only on the client and only when the whole

recordset is available on the client.

There is only client sorting or no sorting.

allow sorting only on the client and only when the

whole

recordset is available on the client.

There is only client sorting or no sorting.

That rule requires sorting capabilities to be disabled when there are more than ~ 1000 - 5000 records - a value that is easiely handable by the DB, AppServer and network and the Grid. Much more will just consume too much system ressources.

That means no sorting at all for larger result sets where batching is required?

The users I've met won't think that's acceptable.

Serverside sorting works well - why disable it?

Message was edited by:

Mike Fechner

That means no sorting at all for larger result sets

where batching is required?

Indeed.

The users I've met won't think that's acceptable.

Have you tried? I have and they never questioned it.

Serverside sorting works well - why disable it?

Because it conflicts with filtering.

In other words: filtering usually provides proper sorting as well.

Because it conflicts with filtering.

Can you explain? DB Queries do filter (select) and sort. Where's the conflict?

Because it conflicts with filtering.

Can you explain? DB Queries do filter (select) and

sort. Where's the conflict?

Performance.

Performance.

That may be an even better reason to go to the DB for filtering and sorting where we can leverage indexes.

Mike,

It is always hard to create a good example on the sports2000 database but here goes. Imagine the following query:

for each customer where country begins "aus" and name begins "a"

This query reads and returns 3 database records (neglecting the extra record read in this explanation for simplicity). The results are sorted by country since that is the index that OpenEdge selects in this case.

Batching is in place, batching size is 1. So the first time there is 1 record read, the second time 2 an the third time 3 (stateless or state-free AppServer). At that point the entire recordset is on the client, and I enable client-side sorting, so the user can now optionally also sort on the name.

Now you want to enable sorting by name on the server. So the query becomes:

for each customer where country begins "aus" and name begins "a" by name

This query also returns 3 records but generates 113 reads each time it is run because it forces OpenEdge to use the Name index instead of the CountryPost index. So the second example generates 339 reads in order to return all batches while the first example generates only 6 reads. That is a factor 56.5 and it is very easy to find much more extreme examples in real life situations.

For example I am currently working on a system where the customer table contains 360,000 records and there are 800 concurrent users. They used to allow filtering and sorting on the server and were constantly complaining about performance. You do the math.

The other approach is to let the user decide and use visual indicators/warnings if there is no proper index.

But yes, this can lead to performance problems and questions from (problems with) users that do not understand the technical background.

I was primarily referring to client sorting with ABL constructs, i.e. queries with temp-tables (in ProDataSets).

I'm not a code page expert, but I assume that this should allow predictable and consistent client and server sort.

The other approach is to let the user decide and use

visual indicators/warnings if there is no proper

index.

The conclusion we can draw from adm2/Dynamics is that this is almost a requirement in a general framework. (I'm not saying that we have a good UI for this. Your screen shot seems more refined). There are generally more cases where a non-indexed sort gives acceptable performance than not. I'm fully aware that there are cases where a sort on large data amounts simply can be too slow and that preventing some sort options might be a solution to prevent users from doing this. But Cancel or time-out options can also go a long way (tough to implement though).

But yes, this can lead to performance problems and

questions from (problems with) users that do not

understand the technical background.

It can also lead to identifying cases where an index is needed...

The conclusion we can draw from adm2/Dynamics is that this is almost a requirement in a general framework.

This is rather unclear. What I meant was that users seem to both want a visual clue about potential performance problems, as well as the ability to ignore the warning.

I’m not basing this on existing features alone, but also on known customer customizations and requested features and enhancements. The frameworks do not support any visual clue for non-indexed sorting, but Dynamics have a warning message dialog for non-indexed filter criteria, which some users like and others want to be able to override.

for each customer where country begins "aus" and

name begins "a"

This query reads and returns 3 database records

(neglecting the extra record read in this explanation

for simplicity). The results are sorted by country

since that is the index that OpenEdge selects in this

case.

Now you want to enable sorting by name on the server.

So the query becomes:

for each customer where country begins "aus" and

name begins "a" by name

This query also returns 3 records but generates 113

reads each time it is run because it forces OpenEdge

to use the Name index instead of the CountryPost

index.

for each customer where country begins "u" and name begins "am"

This query will also generate over 100 reads for 3 records without any sort. The index is selected alphabetically.

The sort expression only affects the index selection when the filter expression cannot be used to determine an index. This will happen when there is no filter, the filter criteria did not access any index or, as in this case, when the filter expression is not able to use multiple indexes and more than one index have the same number of range or equal matches. In the latter case the sort expression is only used if it matches one of the indexes referenced in the filter expression.

My point is that a sort expression never interferes with an index that is selected from the filter criteria and it is thus really a toss up whether the sort will pick a better or worse index compared to the alphabetical selection or primary index selection that otherwise would have been used.

It is the actual sort operation that can have a negative performance impact on a filtered query in the case when the sort does not match the index selected from the filter criteria. This is probably negligible if the query actually returns 3 records, but very noticeable with large result sets (the worst case probably being a non-indexed sort with no filter...). It will happen on each request for a new batch, so the problem is far worse with smaller batch sizes.

Disallowing sort on server will ensure that this never becomes an issue. But you can have performance problems with filter criteria and no sort and you can have fully acceptable performance with filter and sort together, so I doubt that avoiding sort on server is acceptable or necessary for all batching requests or applications. There are many ways to resolve or minimize these issues both in the UI and in the data access layer.

Disallowing sort on server will ensure that this

never becomes an issue. But you can have performance

problems with filter criteria and no sort and you can

have fully acceptable performance with filter and

sort together, so I doubt that avoiding sort on

server is acceptable or necessary for all batching

requests or applications. There are many ways to

resolve or minimize these issues both in the UI and

in the data access layer.

Disallowing sort on the server is not an option for any backend that enables batching in any kind! It's not a matter of performance. It's a matter of wrong or unforeseeable results. If the filtering criteria used on the appserver returns 1.000 rows of 1.000.000 and sorting is not covered by the server, the client will receive "some" 10 rows as the first batch- but not the first 10 rows according to the desired sort criteria.

ABL query executing and index selection takes preference for the selection - which is fine because it first eliminates 999.000 rows that do not have to be sorted in memory (or on srt files)! But that does not mean, that you can forget sorting in queries at all.

Let see batches of 10 rows in a result of 1000 rows. Let's talk about a sample, customer table with 1.000.000 rows, filtered by salesrep = "BBB", the user wants to see it sorted by custnum - no common index here.

First batch (salesrep = "BBB", if not sorted by custnum on the server).

5, 10, 65, 743, 964, 965, 974, 997, 998, 1000

the second batch

1, 2, 10, 11, 14, 16, 18, 19, 20, 999

The user would expect to see

1, 2, 3, 4, 5, 6, 7, 8, 9, 10 as the first batch and 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 as the second batch. How can this be possible without applying the same sort criteria on the server to get the batches in that desired order???

Am I missing a point somewhere?

I'm not a code page expert, but I assume that this

should allow predictable and consistent client and

server sort.

Without being a code-page and collation expert at all that sounds true.

But still this will only make sense, when the client already has all data and not only partial data (3 ot of 100 batches).

Yes, you have to do the sort on server when you are batching. This is one of the main points of the sample.

How can this be possible without applying the

same sort criteria on the server to get the batches

in that desired order???

Am I missing a point somewhere?

Not really.

You are absolutely right that batching require that the query has the same order each time. But batching also require that the where clause is the same each time, so it possible to use a strategy where the where clause decides the order and just skip the sort predicate. A request with no where clause will use primary index.