TIP: determine which columns to be indexed (and do it)

Classic Servoy
1

With the launch of 4.1.0 almost everything is possible.
I had to know which columns I should put an index on, so I created this function:

function createIndex()
{
	var $tmpObject = new Object();
	var $server = 'yourServerName';
	
	var $tables = databaseManager.getTableNames($server);
	$tables[$tables.length] = null; //need a null value to get global relations
	
	var $smRelations;
	var $smRelItems;
	var $foreignTable;
	var $foreignColumn;
	var $jsTable;
	
	for (var i = 0; i < $tables.length; i++)
	{
		// get all relations for a table
		$smRelations = solutionModel.getRelations( $server,$tables[i])
		
		for (var j = 0; j < $smRelations.length; j++)
		{
			// get all relationitems for a relation
			$smRelItems = $smRelations[j].getRelationItems();
			
			for (var k = 0; k < $smRelItems.length; k++)
			{
				// get foreign table- and columnname
				$foreignTable	= $smRelations[j].foreignTableName;
				$foreignColumn	= $smRelItems[k].foreignColumnName;
				
				// process entry when column is not pk-column
				$jsTable	= databaseManager.getTable($server, $foreignTable);
				if($foreignColumn != $jsTable.getRowIdentifierColumnNames()[0])
				{
					// add entry to tmpObject to avoid duplicate table/column combinations
					$tmpObject[$foreignTable + '|'+$foreignColumn] = '';
				}
			}
		}
	}
	
	var $idxName;
	var $table;
	var $column;
	var $sql;
	
	for ( i in $tmpObject )
	{
		// iterate object and create index
		$table	= i.split('|')[0];
		$column	= i.split('|')[1];
		$idxName	= 'IDX_' + $table + '_' + $column; 
		
		// SQL statement maybe different for sql-db's other than sybase
		$sql = 'CREATE INDEX ' + $idxName + ' on ' + $table + ' (' + $column + ')';
		plugins.rawSQL.executeSQL($server, 'dummy', $sql);
	}
}

In our solution I created a table which holds my index ‘metadata’. I marked each record with ‘REL’ for relationship metadata and ‘SQL’ for columns that I used in sql queries within the methods. (unfortunately I had to scan those manually :( )

I’m aware that the code can be more advanced, and take also the servername into account, but this does the job for our solution.
Hope this can help others…

2

Nice tip Marc!

Paul

3

Just saw this from a cross-post. Very nice tip!