I had to know which columns I should put an index on, so I created this function:
- Code: Select all
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...