When are changes saved?

Classic Servoy
1

Hi

I iterate over a table with more than 1mio records and update most of these rows and update or insert rows in other tables while iterating.

To iterate I use the foundset with foundset.getRecord(index) and not controller.setSelectedIndex(index). So the GUI does not commit changes when changing the record. Autosave is set to true. For each record I start a transaction and commit the transaction.

My question: What are the correct settings so that the transaction is not open for 1mio records? Should I set autosave to false if I use a transaction? Can I use autosave and have changes committed after each row?

Thanks for any help and regards
Birgit

2

Hi Birgit,

I don’t believe (but I could be wrong) Servoy will automatically save each record to the back end database when you use this technique.
When it will start to save I don’t know. I can imagine it will when some cache is at a certain size or something.
But what I tend to do is control the saving of the data myself using databaseManager.saveData().
My code looks something like this:

var rec;
for ( var i = 1; i<=databaseManager.getFoundSetCount(foundset) ; i++)
{
	rec = foundset.getRecord(i);

	// do your thing

	if ( i%20 == 0) {
		// force save to back end database after each 20 rows.
		databaseManager.saveData();
	}
}
databaseManager.saveData();

You can change the row count to fit your needs of course.

Hope this helps.

3

Hi Robert

That’s it! Thank you very much. With this save, my data is written to the database. No transaction is needed and auto save can be true.

I’m surprised, since asking databaseManager.hasRecordChanges() just before saving, evaluates to false. But anyways: it works and I’m happy :)

Thanks again and regards
Birgit

4

Hi Birgit,

birgit:
That’s it! Thank you very much. With this save, my data is written to the database. No transaction is needed and auto save can be true.

About transactions I assume you mean database transactions (using databaseManager.startTransaction()).
Depending on the back end database using a single transaction when doing bulk updates/inserts might speed up things significantly.
For instance PostgreSQL uses an implicit transaction for each insert/update you do when you don’t have a transaction going. So that will be thousands and thousands of transactions in your case.
When you start a transaction yourself and do all the updates/inserts then it all is done in 1 single transaction and therefore much faster.

Hope this helps.

5

So what is the effect of autosave off in your coding example?

Also, If I have a “cancel” button someplace do I use the database.rollbackTransaction and/or do I use the database.rollbackEditedRecords (for those records in the 20 before the savedata) .

This are seems a little confusing to read in the help guide.
(ref version 3.5.x)

What is a best practice for using the transaction and autosave?

6

rollbackEditedRecords refers to Servoy’s internal handling. You can use this to mimic a transaction yourself without using one in the database. rollbackTransaction is a database command.