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.
