On Apr 4, 2012, at 10:21 AM, Borax wrote: > Hi, > > I'm using SQLAlchemy 0.7.5 in a web application accessing several tables > containing between 2.5 and 3.5 millions records; these tables are indexed > correctly. > Database is Oracle 10g (release 10.2.0.4). > > My problem is quite simple : when querying the database through SQLAlchemy > (via "session.query(MyClass).filter(...)"), the execution plan (when using > indexed criterias) doesn't use table indexes but does a full scan which takes > between 10 and 15 seconds; when querying the database through a simple SQL > query (in Tora for example), indexes are normaly used and the result is > immediate. > > I tried to play in SQLAlchemy with optimizer modes and hints, but it doesn't > change anything. > > Any idea ?
this all comes down to the nature of the SQL being emitted. what is the SQL being emitted by SQLAlchemy in this case, and what is the SQL that should be emitted ? Working with SQLAlchemy in a non-trivial way implies you're using echo=True or otherwise logging queries during development, and are watching what it does at every step - this is how you tune your app essentially. The ORM is pretty open ended as to how the ultimate SQL query can be structured. You'd want to identify exactly how the structure needs to be in raw SQL, then make sure the ORM query is structured in the same way. If there's truly some odd artifact about the indexable SQL that the ORM just won't do, we can look into using literal expressions for parts of the query, though typically the Oracle indexing quirks are over things like which table comes first in the FROM list or what kinds of comparisons are being used between values, these things can be controlled. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
