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...
