Efficiently finding the index of a target record

Classic Servoy
1

On arriving on a form I want to find the current index for a record (not necessarily the current record) with a particular ID. Since the record count is usually no more than 50 I’ve been doing this via looping, comparing the current record with the target. However performance is very bad.

I am reliably told it’s because I’m using the getMaxRecordIndex() command just ahead of the loop. Is there a technique for identifying the target record’s current index without looping?

2

Just an idea:

What about getting the pk’s into an array using the databasemanager, and looping through the array to look for your record?

3

Not performance tested but I think I may have found a solution to my own problem.

var size = foundset.getSize();
for ( var i = 1 ; i <= size; i++ )
{
	var record = foundset.getRecord(i);
	if ( globals.gcrid == record.crid )
	{
		break;
	}
}
controller.setSelectedIndex(i);
4

So “foundset.getSize()” is faster than “getMaxRecordIndex()”? Interesting.

5

david:
So “foundset.getSize()” is faster than “getMaxRecordIndex()”? Interesting.

You yourself pointed out by phone how expensive getMaxRecordIndex can be in terms of performance. Switching to foundset.getSize() has indeed cut times dramatically.

In many cases the two commands produce identical results.

I’m not sure but I suspect getMaxRecordIndex() attempts to load all possible records to perform its calculation, while foundset.getSize() just returns the record count of the current set, all that’s really wanted most of the time. I’m gropping around for an explanation of the two architectures, but something along those lines is probably at work.

Perhaps Jan Blok or Maarten can elucidate.

6

As far as I see that, those two are the same…

7

looping through the foundset Object is generally faster cause foundset doesn’t update the GUI after each record loop

This can be illustrated by placing “myField” in a table/list view
and then running these 2 scripts:

for(var i=1 ; i<= foundset.getSize() ; i++)
{
var record = foundset.getRecord(i)
record.myField = "abc"
}

for(var i=1 ; i<= controller.getMaxRecordIndex() ; i++)
{
controller.setSelectedIndex(i)
myField="def"
}

The foundset was added in later versions of Servoy
in order to boost performance when looping through recordsets.

So try to stick with foundset Object when doing loops.

8

There are circumstances in which I want to process ALL available records, to keep loading, not just process the current set. Am I correct in assuming only the following will do that?

for(var i=1 ; i<= controller.getMaxRecordIndex() ; i++)
{
controller.setSelectedIndex(i)
myField="def"
}
9

it will loop through your foundset, NOT all records in your table

example
assume you’ve done a search with result of 1000 records (5000 rec total in table)
var counter = foundset.getSize()
// counter will have value of about 200 (currently present in memory)
so next loop will only go through part of your foundset

for(var i=1 ; i<=counter ; i++)
{
//only loops 200 times
}

To make sure you go through the whole foundset do:

for (var i=1 ; i<= foundset.getSize() ; i++)
{
// the value of "foundset.getSize()" increases until end of foundset is reached. (1000)
}

If you want to loop through all records in table
first do controller.loadAllRecords() before stsrting loop

10

Thanks. That clarifies things considerably.

It would be helpful to have advice along those lines in the docs for these functions.

11

You’re right.
I will ask Marc Norman to put this example in the docs.

12

I have a question along these lines

for (var i=1 ; i<= foundset.getSize() ; i++)
{
// the value of “foundset.getSize()” increases until end of foundset is reached. (1000)

Will a relation based counter only loop through 200 records?

for (var i=1 ; i<= orders_to_lineitems.getsize() ; i++)
{
}

Thanks,
Erich

13

no, same behaviour as foundset.getSize()