Thank you. From: [email protected] [mailto:[email protected]] On Behalf Of Michael Bayer Sent: Wednesday, September 19, 2012 1:54 PM To: [email protected] Subject: Re: [sqlalchemy] Query help
OK assuming the text of the query is what you want, you'd have to figure out where it's not using indexes appropriately or otherwise spending too much time scanning things. For SQL Server 2005, its probably easiest to use the management studio interface, some MS docs on that are here: http://msdn.microsoft.com/en-us/library/ms178071%28v=sql.90%29.aspx . On Sep 19, 2012, at 2:49 PM, Cunningham, Brian J wrote: It is slow on the sqlalchemy.select([],).execute() part. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Michael Bayer Sent: Wednesday, September 19, 2012 1:45 PM To: [email protected]<mailto:[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]<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.
