Performant function to create new relation mapping

Classic Servoy
1

I need to create a new relation mapping between SQL tables. For example, I have a pkTable and a fkTable with a relation pkColumn
to fkColumn which holds the autoinc value from the pkTable. Now I want to create a new relation mapping using newPkColumn to newFkColumn which should now use the uuid. I have written the following function. The pkTable has 100000 records and the fkTable has 200000 records.

function createRelationMapping( server, pkTable, pkColumn, fkTable, fkColumn, newPkColumn, newFkColumn ) {
	var pkTableFs = null,
		pkTableRec = null,
		fkTableFs = null,
		fkTableRec = null,
		pkTableSize = 0,
		fkTableSize = 0,
		pkTableFsIndex = 0,
		fkTableFsIndex = 0,
		committed = false;

	
	pkTableFs = databaseManager.getFoundSet( server, pkTable );
	fkTableFs = databaseManager.getFoundSet( server, fkTable );
	pkTableFs.loadAllRecords( );
	fkTableFs.loadAllRecords( );
	pkTableSize = databaseManager.getFoundSetCount( pkTableFs );
	fkTableSize = databaseManager.getFoundSetCount( fkTableFs );

	databaseManager.startTransaction( );
	for ( pkTableFsIndex = 1; pkTableFsIndex <= pkTableSize; pkTableFsIndex++ ) {
		pkTableRec = pkTableFs.getRecord( pkTableFsIndex );	
		
		for ( fkTableFsIndex = 1; fkTableFsIndex <= fkTableSize; fkTableFsIndex++ ) {
			fkTableRec = fkTableFs.getRecord( fkTableFsIndex );		

			if ( fkTableFsIndex === fkTableSize ) {
				application.output( 'PK Record ' + pkColumn + ' = ' + pkTableRec[ pkColumn ] + ' / ' + 
				newPkColumn + ' = ' + pkTableRec[ newPkColumn ] + 
				' Index: ' + pkTableFsIndex + ' of ' + pkTableSize + ' checked', LOGGINGLEVEL.INFO );
			}
			
			if ( pkTableRec[ newPkColumn ] === fkTableRec[ newFkColumn ] /*&& pkTableRec[ pkColumn ] !== fkTableRec[ fkColumn ]*/ ) {
				fkTableRec[ fkColumn ] = pkTableRec[ pkColumn ];
				application.output( 'Record ' + fkTableFsIndex + ' of ' + fkTableSize + ' foreigen key written', LOGGINGLEVEL.INFO );
			}
		}
	}
	committed = databaseManager.commitTransaction( );
	
	if ( !committed ) {
		databaseManager.rollbackTransaction( );
		application.output( 'Commit transaction failed, data rolled back.', LOGGINGLEVEL.ERROR );
	}
}

In the Servoy Developer the above code is not performant.

How can I boost up the speed?

2

You are looping also through all your FK records.
You could do that, by updating ALL FK records, in one time, by using rawSQL, or foundsetUpdater

3

Harjo:
You could do that, by updating ALL FK records, in one time, by using … foundsetUpdater

How can I realize that using the JSFoundSetUpdater? I tried:

function createRelationMappingUsingFsUpdater( server, pkTable, pkColumn, fkTable, fkColumn, newPkColumn, newFkColumn ) {
	var /** @type {JSFoundSet} */ 
		pkTableFs,
		/** @type {JSRecord} */ 
		pkTableRec,
		/** @type {JSFoundSet} */ 
		fkTableFs,
		pkTableSize = 0,
		pkTableFsIndex = 0,
		/** @type {JSFoundSetUpdater} */ 
		fkTableFsUpdater,
		updated = false;

	
 	pkTableFs = databaseManager.getFoundSet( server, pkTable );
	fkTableFs = databaseManager.getFoundSet( server, fkTable );
	pkTableFs.loadAllRecords( );
	fkTableFs.loadAllRecords( )
	pkTableFs.setSelectedIndex( 1 );
	fkTableFs.setSelectedIndex( 1 );
	
	pkTableSize = databaseManager.getFoundSetCount( pkTableFs );
	
	fkTableFsUpdater = databaseManager.getFoundSetUpdater( fkTableFs );
	
	for ( pkTableFsIndex = 1; pkTableFsIndex <= pkTableSize; pkTableFsIndex++ ) {
		pkTableRec = pkTableFs.getRecord( pkTableFsIndex );
		
		
		while ( fkTableFsUpdater.next( ) ) {
			if ( fkTableFs.getSelectedIndex( )[ fkColumn ] === pkTableRec[ pkColumn ] ) {
				fkTableFsUpdater.setColumn( newFkColumn, pkTableRec[ newPkColumn ] );
			}
		}
		application.output( 'PK Record ' + pkColumn + ' = ' + pkTableRec[ pkColumn ] + ' / ' + 
		newPkColumn + ' = ' + pkTableRec[ newPkColumn ] + 
		' Index: ' + pkTableFsIndex + ' of ' + pkTableSize + ' checked', LOGGINGLEVEL.INFO );
	}
	updated = fkTableFsUpdater.performUpdate( );
	
	return updated;
}

but it seems that the index of the fkTableFs is not changed if I look in the debugger. Also the loop in the pkTable seems to consume a lot of time.

4

I think your whole setup is wrong.

You are still looping through ALL of your FK records (by using: fkTableFsUpdater.next( ))

I don’t have the time to type it out fro you, but in general:
first, you have to loop through all you PK records (to create the New UUID)
than you have to load or search for all your FK records, based on the old relation, and do the update in one statement.

Just a snippet out of my head:

		var vSQL = "UPDATE " + yourTable + " SET " + yourcolumn " = " + yourUUD + " WHERE blablabla; 
		plugins.rawSQL.executeSQL(yourserver,yourTable,vSQL);
5

Harjo:
I think your whole setup is wrong.

You are still looping through ALL of your FK records (by using: fkTableFsUpdater.next( ))

I loop through the pk records and try to use fkTableFsUpdater.next( ) to find the related records.

Harjo:
I don’t have the time to type it out fro you, but in general:

You don’t need to type it out. I just asked for help in using the JSFoundsetUpdater in this case.

Harjo:
first, you have to loop through all you PK records (to create the New UUID)

I do it. (uuid is already created in pk table)

Harjo:
than you have to load or search for all your FK records, based on the old relation, and do the update in one statement.

That’s why I try fkTableFsUpdater.next( ).

Harjo:
Just a snippet out of my head:

		var vSQL = "UPDATE " + yourTable + " SET " + yourcolumn " = " + yourUUD + " WHERE blablabla; 

	plugins.rawSQL.executeSQL(yourserver,yourTable,vSQL);

I solved it already by myself using SQL.

	UPDATE fkTable
	   SET fkTable.uid = pkTable.uid
	   FROM fkTable
	   INNER JOIN pkTable
	   ON fkTable.id = pkTable.id

But how to use the JSFoundsetUpdater in this case?

6

deezzub:
But how to use the JSFoundsetUpdater in this case?

Have a look at the fs-updater sample code.
This is quite self-explaining

However, I’d recommend plain sql for this.
It’s way faster to do this 1 time update.

To reflect the changes in your data you should restart the client or use dbM.refreshFoundset for each foundset you updated.

7

mboegem:
Have a look at the fs-updater sample code.
This is quite self-explaining

I had a look, but it is not so clear for me. If I use JSFoundsetUpdater.next( ), it does not change the selection in the JSFoundset, so the only way to use it, is by a relation? So if I use JSFoundsetUpdater.next( ), I can’t get a column value, I only can set a column value?

mboegem:
However, I’d recommend plain sql for this.
It’s way faster to do this 1 time update.

Yes, it was really fast.

mboegem:
To reflect the changes in your data you should restart the client or use dbM.refreshFoundset for each foundset you updated.

Ok.