Query help

[Brian]

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

[Michael Bayer]

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(...)

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Cunningham, Brian J]

It is slow on the sqlalchemy.select([],).execute()  part.

[Michael Bayer]

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 .

[Cunningham, Brian J]

Thank you.