The query to autoload foreign keys from the information schema views on MS SQL Server performs very poorly for large databases. I extracted the query from the log generate with "metadata.engine.echo = True". I tested used both windows and cygwin versions of python 2.5, pyodbc 2.0.36, SQLAlchemy 0.3.8 on Win XP Pro64 against an SQL Server 2005 database on a Win2k3 server.
The autoload time for each table was about 11 seconds. Autoloading all tables from my database would require over 5 hours. A quick test using 4 tables was almost too painful to test. In the management studio, I tried several rearrangements of the query. Adding missing catalog and schema where clauses improved time for 11 seconds to 8 seconds, but nothing seemed to make a truly large difference. An equivalent query, written using the SQL Server 2005 sys.foreign_keys and sys.foreign_key_columns takes under 100 milliseconds. That is still around 3 minutes to autoload my entire database, but is fast enough for use with small applications that require table subsets with 20 to 50 tables. 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] The BOL was unclear on the value for the match option column, so I hard coded it. Is it reasonable to make an option, similar to use_scope_identity, to optionally use my query in place of the information schema query? Should I maintain a local version of SQLAlchemy with this query, or would it be generally useful? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
