I’m running a slow batch process overnight where I copy a lot of data to two remote locations. My table has 40,000 records.
Yesterday I wanted to restart the process on record 23,500… in Developer, since this is a one-off process:
controller.loadAllRecords();
for ( var i =23500; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.setSelectedIndex(i);
etc
This does not work. controller.getMaxRecordIndex() is 200, so the loop does not run. Neither does this:
controller.loadAllRecords();
controller.setSelectedIndex(23500);
for ( var i =23500; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.setSelectedIndex(i);
etc
I got around it by:
controller.loadRecords("SELECT books.booksid FROM books ORDER BY booksid OFFSET 23500",null)
But it is not very intuitive…
Is is this a Servoyism, that I have to page through every 200th record for controller.getMaxRecordIndex() to update to get to specific record in a table?
I would say that is the way it is and has always been. After a loadAllRecords you have a foundset with a max index of 200. If it was not that way, Servoy would always have to transfer ALL data to a client, which is simply stupid.
controller.getMaxRecordIndex() will only return the amount of records that the controller has loaded. As you know this goes in batches of 200 records.
You should use databaseManager.getFoundsetCount(foundset) to get the real result count.
I should add that with knowing the real foundset size you still can’t use controller.setSelectedIndex(23500) because the controller still hasn’t loaded all those records.
Therefore you should use the following code (which is faster too):
controller.loadAllRecords();
var rc = null;
for ( var i =23500; i <= databaseManager.getFoundsetCount(foundset) ; i++ )
{
rc = foundset.getRecord(i);
// from here on you refer to the rc record object like rc.columnName
etc
I totally agree with the notion of fetching 200 records at a time (otherwise goodbye client/server/wan/large row counts/etc).
However, I have always thought it would be great if Servoy could “position” itself in an SQL query/table at a “start point” for its 200 record fetch. So, if one has a table with say 400,000 records then one could go to say record 100,000 programmatically with one function call and then proceed with the default and automatic 200 record thing. In this example I am assuming that all 400,000 records are available (ie - I have not constrained the foundset via a Find/Search or via invocation of the database manager node or other filter condition).
I haven’t thought through the implications of this across different dbms systems and pre-positioning … I am just wondering if this possible for Engineering to do such a thing. I can think of a number of situations where this might be handy … especially with larger row count situations.
var rc = null;
for ( var i =23500; i <= databaseManager.getFoundsetCount(foundset) ; i++ )
{
rc = foundset.getRecord(i);
// from here on you refer to the rc record object like rc.columnName
etc
Hi Robert, this code is faster, but from this I recon I should be able to do:
rc = foundset.selectRecord(113603)
where 113603 is a pk of one of the records in my foundset, but this fails.
The function returns false.
I find this inconsistent.
It is better if Servoy can be made to cope with this, otherwise every single programmer who has to deal with large datasets will have to make up their own solution, undoubtedly reinventing the wheel many times on the way.
It is much better that the Servoy dev team writes the code to jump down to some given record in a large dataset ONCE.
again: foundset.selectRecord(xyz) can only be successful if that record is really presently loaded in the client.
If you do a controller.loadAllRecords(), Servoy will fire a SELECT * query to the database. This can result in 100 records or in 100.000.000 records. Servoy will never transfer all records to the client as you can easily understand if your table has 100.000.000 records. Now you want to jump to a foundset index 25654 or select the record with PK 765543. This is not possible if either that index or that PK is not loaded to the client.
You can say this is confusing or that selectRecord(PK) is not reliable, but I can’t see how Servoy should improve this behaviour.
I, for example, never use selectRecord(pk) unless I am 100% sure that I really have that record loaded (because then it is reliable). If I have a table with 100.000 records and want the one with pk xy, I search for that PK. And if you want to jump to a certain index, you will have to loop to make sure that index gets loaded. But that will really load all those records in the client, so this is not really a great option. Your approach with the query offset is the way to go then.
patrick:
Now you want to jump to a foundset index 25654
I would expect servoy to be smart enough to add an “OFFSET 25600” to the end of whatever query it uses to load the current dataset and load 200 records. Then we can jump to 25654 no problem. There should be no more overhead in this than crossing any other 200 record barrier.
In the case of going to a specific pk, that is more complex, but doable if number of records in table not too large.
I would expect servoy to be smart enough to add an “OFFSET 25600” to the end of whatever query it uses to load the current dataset and load 200 records. Then we can jump to 25654 no problem. There should be no more overhead in this than crossing any other 200 record barrier.
Let’s suppose Servoy does that. So you call
setSelectedIndex(25654)
and Servoy uses some smart ofset. Next you do
setSelectedIndex(getSelectedIndex()-100)
and everything breaks again.
I think the way it is is the most logical once you understand and accept the limitations.
I don’t agree, I really don’t see what the problem is.
Servoy should know that if you are on record 25654, then
setSelectedIndex(getSelectedIndex()-100)
means getting record 25554 which means it first has to load 200 records from record 25400. Basically, if the record you are asking for is not
in between
getSelectedIndex() - (getSelectedIndex() mod 200)
and
getSelectedIndex() - (getSelectedIndex() mod 200) + 199
then it has to load 200 record from
getSelectedIndex() - (getSelectedIndex() mod 200)
Again, the overhead of doing this is minimal, the same as crossing any other 200 record barrier.
The current behaviour does not represent ease of use.
As an expericenced developer I know I can work around it (but I don’t think I should have to), but developers new to Servoy may decide Servoy is not for them when they come across this issue.
Servoy tacks on a “limit ?,?” to the foundset query. When you scroll to the bottom of a list thereby triggering loading of another 200 records, Servoy modifies the parameters of the limit.
We can almost do this ourselves programmatically with the following:
//grab the foundset details
var sql = databaseManager.getSQL(foundset)
var params = databaseManager.getSQLParameters(foundset)
var size = databaseManager.getFoundSetCount(foundset)
//go to record x of size
var x = 236 //arbitrary value
if (x >= 200 && x <= size) {
//load 200 records starting with x
sql += " limit " + x + ", " + (x + 199)
var dataset = databaseManager.getDataSetByQuery(
currentcontroller.getServerName(),
sql,
params,
200)
currentcontroller.loadRecords(dataset)
}
else if (x <= 200) {
currentcontroller.setSelectedIndex(x)
}
However, this changes the SQL to the foundset you started with which is not desirable. When you scroll to the bottom of a list with this foundset loaded, the next 200 records is not loaded as you have changed the foundset to have only 200 records.
This could easily be solved if Servoy gave us a function to modify the limit parameters of a foundset ourselves without changing the foundset!
Example usage would be:
//our new proposed function
foundset.setLimit(450,650)
//go to record index
foundset.setSelectedIndex(45)
I think the way it is is the most logical once you understand and accept the limitations.
Don’t we have this going on anyway (ie - with setSelected… OR with the user selecting current record - 1 with the keyboard/mouse once they are deep in a large table and Servoy must manage the memory pool)?
I think the offset thing (Christian) is a real plus coupled with what David is saying & Robert. Yes/no?
We came up with that sample code when testing workarounds for the main issue of this thread: jumping to a record outside of the currently loaded records (and we happen to use MySQL primarily).
In the end this approach doesn’t work at all because it changes the foundset we want to manipulate in the process.
But it highlights that a foundset.setLimit(a,b) function from Servoy would fix this whole issue beautifully. Already Servoy changes the limit syntax it uses depending on the backend database so I’m thinking this would be an easy function to add.
//go to record index
foundset.setSelectedIndex(45)
This worries me: is the 45 absolute or relative to 450?
With this functionality I foresee some spectacular bugs!
Much simpler if Servoy sets the limit automatically when we ask for a record outside the current limit.
45 would be the index within the limit – not required. if you just specify the limit parameter the default record index would be at one.
I agree it seems like it should be easy for servoy to automatically reset the current 200 record limit when asked for a record outside the current range.
A function like foundset.setLimit(a,b) would give the developer a way around the 200 “limitation” in selected circumstances.
can we ask for an enhancement request of the loadAllRecords method to have an optional parameter called [forceLoadingAllRecord] and to be defaulted to false ?
loadAllRecords([forceLoadingAllRecord])
If it has been set to true the limit clause is to be neglected.
Do you agree on this ??
MSibai:
can we ask for an enhancement request of the loadAllRecords method to have an optional parameter called [forceLoadingAllRecord] and to be defaulted to false ?
loadAllRecords([forceLoadingAllRecord])
If it has been set to true the limit clause is to be neglected.
Do you agree on this ??
MSibai:
New support ticket has been opened with the number 99657.