sorting performance with joined tables

Posted by pauldownie on 13-Feb-2014 11:51

Hi,

I have a need for data from two tables to supply records to a browser. The main table has a one to zero or many relationship with the secondary table which supplies a single field to the browser. None of the fields are updatable. I am currently using a SDO with the two joined tables. It is important that the field from the secondary table can be used for sorting, but performance when sorting on this field against large data sets can result in long delays and a "not responding" message. This is the only real performance problem with this browser. Can this performance be improved if I were to use a SBO instead, or is there some other way to improve performance sufficiently on large data sets to solve this problem (all fields referenced and displayed from the secondary table are indexed). If that is not possible, is there a way to prevent a sort from being attempted when the data set is of a certain size? Sorting is really only necessary after filtering has been applied.

thanks,

Paul

v. 10.2b

Windows 7

All Replies

Posted by Håvard Danielsen on 13-Feb-2014 18:15

Using an SBO would not improve performance. It is an SDO container.

Performance of joined queries can often be improved by dynamically changing the order of the tables in the query based on filter and sort criteria. If the secondary table has indexes that support the filter and sort criteria directly and there is an index that supports the join in the other direction then moving that table first in the query when it is sorted and not filtered or  when included in the filter criteria will likely eliminate many slow cases.

In theory this can be achieved by using dynamic SDOs on a stateless Appserver, but I don't think it is trivial.  

Another alternative is to use a ProDataset and replace the SDO with a DataView. I believe it is easier to implement a datasource that can change the order of the tables in the query based in filter and sort criteria than managing this in an SDO. But you would need to implement the service adapter and the code to manage the prodataset  on the server.  

A more brute force approach is to customize the browser or fix your filter to give errors or warnings for certain sort and filter criteria.  

I believe it rather difficult to implement a solution that checks the number of records first and then disallows a sort. This would probably require you to read all data first without the sort ....

Posted by pauldownie on 14-Feb-2014 09:08

Thank you. That is just what I needed to know before going any further on this. I think for me, at this point, providing a warning when selecting that particular sort on unfiltered records will probably be the best approach.

Posted by pauldownie on 14-Feb-2014 11:01

So I now have code to check the filter status of the SDO in the browser when the sorting is applied, but I also need to find a hook to check the sort status when the filter is applied. I can't seem to find a good one. Are there any suggestions? I am using a smartFilter.

thanks,

Paul

Posted by Håvard Danielsen on 17-Feb-2014 13:03

The SmartFilter does not seem to have a hook suited for this. You could consider reimplementing the ApplyFilter method (in a customfilter super or your own version of dynfilter.w) as an override and manage it there.  

Another alternative is to override openQuery in the SDO and do the checks before you call super. This may seem a bit scary since openQuery is called from many places, including on server. I would add some checks to ensure the validation only is done on the client. This is typically accomplished by checking that  AsDivision <> " server". It may also be a good idea to check that you have at least one DataTarget to ensure the check only is done when the SDO is a data source of a visual object.  

A third alternative is to combine the two approaches and override (not reimplement) ApplyFilter to just set a flag in the SDO before the call to super and set it false again after the call to super and then fix the check in openQuery to only be done when called from the filter (when the flag is set).  

Posted by pauldownie on 17-Feb-2014 15:03

Thank you. I'm not sure this is what you had in mind but I was thinking along the lines of using the getQueryColumns and getQuerySort methods to determine if there is a filter active and what fields are in the sort. It seems that if I use an override on the applyFilter in the smartFilter I'll either be too early to determine what filter criteria is about to be applied or too late to stop the query from executing with the sort. The best I have been able to come up with is to use the CHOOSE OF btnBlank trigger on the smartFilter to publish an event and determine at that time whether I need to clear the sort. It will mean that the sort will be lost regardless of whether new filter criteria was entered before the filter was applied but I think I can live with it.

Posted by Håvard Danielsen on 17-Feb-2014 16:08

Yes, I think getQueryColumns and getQuerySort can tell you the filter and sort criteria when you use assignQuerySelection (also used by the SmartFilter). But to be able to stop the execution you would need to reimplement ApplyFilter (copy existing code and add necessary behavior)  or handle the validation in openQuery. You should not need to clear anything, but rather leave it to the user to clear or cancel the filter when the validation fails. (If you do want to clear filter or sort I believe you should be able to use removeQuerySelection and/or setQuerySort ("") using getQueryColumns to figure out the input to removeQuerySelection).

Posted by eugenel on 17-Feb-2014 18:29

This is a generic reply and may not be directly related to your data source. If the result without sorting is not affected by the ORDER BY clause in the sql, performance may be better without the ORDER BY clause and the result can be sorted by any column in the client side of a browser, if needed. To see a demo how the sorting works on the client side, please click this link below:

http://nbmoreliu3/test/test_tablesort.htm

If the link does not work for you, please let me know. I'll find other ways to show you.

Posted by pauldownie on 18-Feb-2014 09:31

I don't think I understand what you mean by reimplementing ApplyFilter but I have been able to use the getQuerySort and getQueryColumns in openQuery and stop execution when necessary. I followed your earlier advice of setting a flag in the SDO to signal when that check should be made.

Posted by pauldownie on 18-Feb-2014 09:34

I wasn't able to open the link. It sounds interesting but I am not using SQL so I don't know if it is really applicable to my situation.

Posted by Håvard Danielsen on 18-Feb-2014 11:14

It is a general solution that also can apply to ABL queries. If you are not batching then sorting the data on the client could be faster than sorting on server. This could be worth trying in in your case, since the overhead seems related to the server joins.  I believe the SDO sorts on client if it all data is on the client, but you would need to remove the sort expression from the server request somehow to make this work. .  

---

Edit: You can of course sort on the client when you are batching also, it just does not make any sense...

Posted by pauldownie on 18-Feb-2014 11:40

Unfortunately there are too many records not to use batching.

Posted by Håvard Danielsen on 18-Feb-2014 12:12

> I don't think I understand what you mean by reimplementing ApplyFilter

The solution you chose is probably better, but I meant copy the entire filter.p ApplyFilter to an override that does not call super and edit it to validate the query before the SDO is called. (It is generally not recommended to edit directly in adm2 code)

This thread is closed