It is slow on the sqlalchemy.select([],).execute() part.
From: [email protected] [mailto:[email protected]] On Behalf Of Michael Bayer Sent: Wednesday, September 19, 2012 1:45 PM To: [email protected] Subject: Re: [sqlalchemy] Query help query is taking too long to: a. begin returning results or b. once it begins returning results, takes too long to iterate rows ? that is, where is this slow: # slow here ? (SQL Server has a slow query plan) result = session.execute("select DISTINCT <rest of your query>") # ... or slow here ? (the query returns many many rows) rows = result.fetchall() # or slow when you do this ? (ORM overhead) objects = session.query(MyObject).filter(...) On Sep 19, 2012, at 2:24 PM, Brian wrote: Here is the initial information. SQLAlchemy Version : 0.7.4 Database backend: SQL Server 2005 Library interface : pyodbc 3.0.4 On a side note I tried updating to the latest SQLAlchemy version and the problem I am running into is still present. The problem I am running into is I build up a fairly large select query and when I run sqlalchemy.sql.expression.Select.execute() it takes much long than it should. I have tried generating the raw compiled sql statement in various methods to try to debug it and all the other methods return in under 5-6 seconds. I tried using a sqlalchemy connection.execute('select . . . . . '), a pyodbc connection.execute('select . . . .') and using MS SQL Server Management Studio. The generated query without the variables substituted in is SELECT DISTINCT directory.path, [RasterFile].[FileName] FROM directory, [RasterFile], [GroupRaster], usergroup, users, [RasterGeodeticBounds] WHERE [RasterFile].[DirectoryID] = directory.id AND [GroupRaster].[GroupID] = usergroup.[GroupID] AND [GroupRaster].[FileID] = [RasterFile].id AND users.id = usergroup.[UserID] AND users.id = ? AND [RasterFile].[FileName] LIKE ? ESCAPE '<' AND [RasterFile].status = ? AND [RasterFile].[DateCreatedNum] >= ? AND [RasterFile].[DateCreatedNum] <= ? AND [RasterFile].[DateModifiedNum] >= ? AND [RasterFile].[DateModifiedNum] <= ? AND [RasterFile].id = [RasterGeodeticBounds].[FileID] AND [RasterGeodeticBounds].[WestLong] <= ? AND [RasterGeodeticBounds].[EastLong] >= ? AND [RasterGeodeticBounds].[SouthLat] <= ? AND [RasterGeodeticBounds].[NorthLat] >= ? AND [RasterFile].id IN ( SELECT [RasterTag].[FileID] FROM [RasterTag], [UserTagTable] WHERE [RasterTag].[TagID] = [UserTagTable].id AND [UserTagTable].[aTag] LIKE ? ESCAPE '<' INTERSECT SELECT [RasterTag].[FileID] FROM [RasterTag], [UserTagTable] WHERE [RasterTag].[TagID] = [UserTagTable].id AND [UserTagTable].[aTag] LIKE ? ESCAPE '<' INTERSECT SELECT [RasterTag].[FileID] FROM [RasterTag], [UserTagTable] WHERE [RasterTag].[TagID] = [UserTagTable].id AND [UserTagTable].[aTag] LIKE ? ESCAPE '<' ) Any help is appreciated. Thank you -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/tPpgGviVqhEJ. To post to this group, send email to [email protected]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:[email protected]>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:[email protected]>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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.
