yiisoft/yii — Issue #430: MS SQL Server Schema loading is slow
Author: tsdogs
Created: 2012-03-02 09:34:35+00:00
State: open

Using mssql as a backend is slow in yii as every time it checks for a model foreign key it takes 4 seconds and if you have more than 1 model to be used time for each request sums up.

I know that ms documentation talks about using INFORMATION_SCHEMA, but using the sys.* views seems to be much more efficent.

This could be a solution:
$st = 'sys.tables';
$sc = 'sys.columns';
$sfkc = 'sys.foreign_key_columns';
if (isset($table->catalogName))
{
$st = $table->catalogName.'.'.$st;
$sc = $table->catalogName.'.'.$sc;
$sfkc = $table->catalogName.'.'.$sfkc;
}
$sql = <<quoteTableName($sfkc)} AS fkc
INNER JOIN {$this->quoteTableName($st)} AS child
ON fkc.parent_object_id = child.[object_id]
INNER JOIN {$this->quoteTableName($st)} AS referenced
ON fkc.referenced_object_id = referenced.[object_id]
INNER JOIN {$this->quoteTableName($sc)} AS referenced_cols
ON fkc.parent_column_id = referenced_cols.column_id
AND referenced_cols.[object_id] = referenced.[object_id]
INNER JOIN {$this->quoteTableName($sc)} AS child_cols
ON fkc.referenced_column_id = child_cols.column_id
AND child_cols.[object_id] = child.[object_id]
WHERE child.name = :table
ORDER BY fkc.parent_column_id;
EOD;