SQL Alchemy performance

I'm working on a project that will need a huge database. Currently we are using SQLAlchemy but I'm concerned a little about performance issues. My question is , having a query like:

session.query(DataStorage).filter(DataStorage.storage_path.startswith(path)).all()

How does SQLAlchemy does the actual translation and filter. Does it get all the entries from DataStorage with a SELECT clause and then check everyone of them ?开发者_运维百科 Or does it know how to translate the "filter(DataStorage.storage_path.startswith(path))" into SQL ? How much is lost in terms of performance to using native SQL queries ?

regards, Bogdan

SqlAlchemy uses your code to generate an SQL statement. In your case, you up with something like:

SELECT * FROM DataStorage WHERE DataStorage.storage_path LIKE 'path%';

The query is run against the database once you use the .all(). So in this case, it would get all of the rows in a resultset iterator and return them to you.

I'm not familiar with the particular SQLAlchemy constructs you're using, but the best way to find out is to try it. Turn on query logging in MySQL and check out the queries SQLAlchemy is generating. You can try writing the query by hand and comparing the performance of the two. (You'll need a bunch of test data in your database for this.)

Generally, ORMs do fine with simple SELECTs, WHERE clauses, ORDER BY, etc. When you start to do many joins or lots of processing on the data, the constructed queries tend be less optimal. This is specific to your application. The approach I typically take is to write things out using the ORM and optimize and replace with SQL where necessary.