[sqlalchemy] slow autoload with SqlServer Information Schema
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 -~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy.engine.engine_descriptors() is broken
A call to sqlalchemy.engine.engine_descriptors() fails with this traceback: $ ./python test_sa1.py engine_descriptors(): Traceback (most recent call last): File test_sa1.py, line 62, in module print 'engine_descriptors():',sqlalchemy.engine.engine_descriptors() File build\bdist.win32\egg\sqlalchemy\engine\__init__.py, line 75, in engine_descriptors NameError: global name 'sqlalchemy' is not defined --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: help with IronPython
On Jun 20, 2:09 pm, Rick Morrison [EMAIL PROTECTED] wrote:
The SA MSSQL module currently supports the three DBAPI modules mentioned
above (pyodbc, pymssql, adodbapi).
You'll need to either get one of those three to import under IronPython, or
add support for the IPCE adaptor.
pyodbc, pymssql, and adodbapi each use binary modules, so they would
require some (unknown amount of) conversion to work with IronPython.
I wrapped a module around the IPCE adaptor module to meet (more of)
the DBAPI 2.0 module level requirements, and duplicated the pyodbc
code in databases/mssql.py to use my module.
That got me an error on the Table('users', metadata, ... line from the
tutorial:
Traceback (most recent call last):
File D:\Software\IronPython\dbTest\test_sa.py, line 83, in
Initialize
File D:\Software\IronPython\dbTest\test_sa.py, line 80, in main
File D:\Software\IronPython\dbTest\test_sa.py, line 52, in
test_users_table
File schema, line unknown, in __call__
File D:\Downloads\Python\SQLAlchemy-0.3.8\Lib\sqlalchemy\schema.py,
line 156, in __call__
File , line 0, in Make##135
TypeError: Cannot convert _TableSingleton(class
'sqlalchemy.schema.Table') to String
This error looks like it might be due to a difference in the type
systems between C-Python and IronPython, previously reported as
http://www.codeplex.com/IronPython/WorkItem/View.aspx?WorkItemId=7594
That IronPython work item is currently scheduled for the 2.0 alpha 2
release. Note that the community can vote for problems to be fixed on
the Codeplex site.
Here is my wrapper module, which I called ipodbc.py:
import dbapi
import odbc as db
dbapi._load_type(db.assembly,db.typename)
def connect(connectStr):
return = db.connect(connectStr)
apilevel = '2.0'
threadsafety = 0
paramstyle = 'qmark'
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] help with IronPython
I recently tried SA again, and found that IronPython can now import it
without errors.
I seem to be missing some critical bit when connecting the driver to
SA
I have IPCE-r6
ipy -V
IronPython 1.1 (1.1) on .NET 2.0.50727.42
My test program starts like so:
import sys, os, os.path
import dbapi
import odbc as db
dbapi._load_type(db.assembly,db.typename)
sa_path = os.path.join(os.getenv('SA'), 'lib')
sys.path.append(sa_path)
from sqlalchemy import *
DatabaseName = 'Temp'
db = create_engine('mssql://DEVELOPERS-W2K3/'+DatabaseName)
ipy test_sa1.py
Traceback (most recent call last):
File D:\Software\IronPython\dbTest\test_sa1.py, line 15, in
Initialize
File __init__, line unknown, in create_engine
File D:\Downloads\Python\SQLAlchemy-0.3.8\lib\sqlalchemy\engine
\__init__.py, line 154, in create_engine
File strategies, line unknown, in create
File D:\Downloads\Python\SQLAlchemy-0.3.8\lib\sqlalchemy\engine
\strategies.py, line 58, in create
File D:\Downloads\Python\SQLAlchemy-0.3.8\lib\sqlalchemy\databases
\mssql.py, line 350, in dbapi
ImportError: No DBAPI module detected for MSSQL - please install
pyodbc, pymssql, or adodbapi
I am trying to use the DBapi adaptors that come with IPCE (see
https://svn.sourceforge.net/svnroot/fepy/trunk/dbapi/). Do these work
with SA?
Which of the listed drivers will work with IronPython? I thought they
were binary, or used win32 extensions.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
