Eleven seconds? Perhaps something is wrong with the server. My experience has been queries to the INFORMATION_SCHEMA views take on the order of 100ms or so. This is through pymssql and interactive queries with the MS tools: I have not tried them through pyodbc or other modules.
At any rate, using the views is not that big of a deal, getting accurate table information is. If you want to take a swag at a patch, make sure that you capture IDENTITY columns, foreign keys and column defaults correctly as well as the normal column information. The keyword option is a great idea at least in the early stages. Also, up to now we've been at least informally supporting MSSQL 2000 as well as 2005, so watch for any incompatibilities there. Rick On 6/22/07, d_henderson <[EMAIL PROTECTED]> wroe: > > > 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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
