slow autoload with SqlServer Information Schema

[paj]

D Henderson posted the following to the mailing list:

The query to autoload foreign keys from the information schema views on MS SQL Server performs very poorly for large databases.

An equivalent query, written using the SQL Server 2005 sys.foreign_keys and sys.foreign_key_columns takes under 100 milliseconds.

Here is my rewritten query:

SELECT COL_NAME(b.parent_object_id,b.parent_column_id) AS column_name , USER_NAME(a.schema_id) AS table_schema , OBJECT_NAME(a.referenced_object_id) AS table_name , a.name AS constraint_name , 'SIMPLE' AS match_option , a.delete_referential_action_desc AS delete_rule , a.update_referential_action_desc AS update_rule FROM sys.foreign_keys AS a INNER JOIN sys.foreign_key_columns AS b ON b.constraint_object_id = a.object_id WHERE a.parent_object_id = OBJECT_ID(?) AND a.schema_id = USER_ID(?) ORDER BY a.name, b.constraint_column_id

[Former user]

(original author: ram) Table reflection here on MSSQL2005 + pymssql is ~100-300 ms per table on databases with less than 100 tables, perhaps not record-breaking, but within reason. Table reflection is a convenience feature, it's not designed to scale up.

I'm reluctant to swap out queries -- there are MSSQL 2000 users to support, and concerns with case sensitive vs insensitive databases.

Will examine using ODBC catalog functions when we tackle pyodbc + Unix as a speedup, but I'm going to pass on this one.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)