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.
