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]] 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]. > 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. > > -- > 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. > > -- > 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. -- 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.
