Temporary records for GetSelectionFilter?

Updated 2023-07-25 – please see addition below.

Whenever you need to get a value1|value2|value4..value10|... field filter from a provided record variable, then the GetSelectionFilter method from the SelectionFilterManagement codeunit is your friend. However, using this function has one drawback, as you can read in the Remarks section:

This method queries the database intensively, can cause performance issues and even cause database server exceptions.

Too bad. But does it really mean that we cannot use it for a large amount of records? Let’s check what my dear teammate has tried out (and thank you for providing your code and screenshots!).

Here’s the use case: My teammate has created a new table (Job Line Budget), which is attached to the standard Job table. He needed to build a filter for the Unique No. field based on 3500 page records. This was his code for calling GetSelectionFilter:

    procedure GetSelectionFilterForJobLineBudget(var JobLineBudget: Record "Job Line Budget"): Text
    var
        SelectionFilterManagement: Codeunit SelectionFilterManagement;
        FilterRecordRef: RecordRef;
    begin
        FilterRecordRef.GetTable(JobLineBudget);
        exit(SelectionFilterManagement.GetSelectionFilter(FilterRecordRef, JobLineBudget.FieldNo("Unique No.")));
    end;

But running this code with 3500 lines took quite long, as you can see:

So this is, what he has tried: He looped the page records with SetLoadFields, and then saved the resulting records in a temporary record, in order to pass that record to GetSelectionFilter:

    procedure GetSelectionFilterForJobLineBudget(var JobLineBudget: Record "Job Line Budget"): Text
    var
        SecondJobLineBudget: Record JobLineBudget;
        TempJobLineBudget: Record JobLineBudget temporary;
        SelectionFilterManagement: Codeunit SelectionFilterManagement;
        FilterRecordRef: RecordRef;
    begin
        SecondJobLineBudget.Copy(JobLineBudget);
        SecondJobLineBudget.SetLoadFields("Job No.", "Unique No.");
        if not SecondJobLineBudget.FindSet() then
            exit('');

        repeat
            TempJobLineBudget."Job No." := SecondJobLineBudget."Job No.";
            TempJobLineBudget."Unique No." := SecondJobLineBudget."Unique No.";
            TempJobLineBudget.Insert();
        until SecondJobLineBudget.Next() = 0; 

        FilterRecordRef.GetTable(TempJobLineBudget);
        exit(SelectionFilterManagement.GetSelectionFilter(FilterRecordRef, JobLineBudget.FieldNo("Unique No.")));
    end;

The performance improved much more than we both would have expected:

Lesson learned: There are use cases where working with temporary records can gain a lot of performance!

Updated 2023-07-25:

Oh no, we were so wrong …

With the new code, GetSelectionFilter would always return a single 'MinSelectedValue..MaxSelectedValue', it would never build any sub-ranges. Why is that? Because GetSelectionFilter assumes that the passed record actually contains every record plus a filter. While looping the filtered subset, it would check whether there are records outside the filter. If found, they would close the current range and start a new one.
You can see it in the very first lines of GetSelectionFilter:

TempRecRef is the procedure parameter. Both Reset() and Open() ensure that no filter is being used for RecRef, the “compare record”.

Since we had called GetSelectionFilter with a temporary variable filled with only a subset of records, TempRecRef and RecRef were the same: this could never work! And now you also know the cause of the performance warning in the very beginning.

Lesson really learned: Be very, very careful when you use foreign procedures that you do not entirely understand. Don’t let a quick success or a good performance blind you.

But let’s not stop here: What did my teammate finally do, to solve the performance issue? He rewrote his function without GetSelectionFilter, but built his own, only pipe-separated filter:

	procedure GetSelectionFilterForJobLineBudget(var JobLineBudget: Record "Job Line Budget"): Text
    var
        SecondJobLineBudget: Record "Job Line Budget";
        UniqueNoFilter: Text;
    begin
        SecondJobLineBudget.Copy(JobLineBudget);
        SecondJobLineBudget.SetLoadFields("Job No.", "Unique No.");
        if not SecondJobLineBudget.FindSet() then
            exit('');

        UniqueNoFilter := '';
        repeat
            UniqueNoFilter += Format(SecondJobLineBudget."Unique No.") + '|';
        until SecondJobLineBudget.Next() = 0;
        UniqueNoFilter := UniqueNoFilter.TrimEnd('|');

        exit(UniqueNoFilter);
    end;

Result for 3500 records: faster than ever!

Even using the TextBuilder data type here for building the filter string would make no more difference to the user.

I hope you could learn from this as much as we did!
(Thank you, Fiddi, for warning us about the wrong function output!)