Why are my two SQL iterations different?

I have the following SQLAlchemy iteration in python:

for business in session.query(Business).filter(Business.name.like("%" + term + "%")):
    print business.name  

I have about 7000 businesses in my list, and this runs in under 10ms. Great!

However, I want to support a more specific search algorithm than like - for example, I want to match & with and, and so forth. So, I tried t开发者_开发问答he following:

for business in session.query(Business):
    if term in business.name:
        print business.name

The latter takes about 600ms to run. What is SQLAlchemy doing in its filter call that makes the iteration so much faster? How can I make mine faster?

Thanks!

SA does nothing to optimize your query.
The difference you have is between:

1. filter data on RDBMS directly on one hand and
2. load all rows from the database into memory, then filter out those not needed.

Obviously, the second version is going to be much slower, especially if the number of rows is large. In fact, the SA might even contribute to your second version being slower, as it creates Business objects for every row in the database, which depending on your model might be relatively expensive operation. Furthermore, if you have eager-load relationships, it will load those too. Just run two queries below against database directly to see how many rows each returns:

SELECT * FROM Business WHERE Name LIKE '%term%';
SELECT * FROM Business;

I would suggest using SQL engine filtering capabilities for large number of objects, as you can do most of the filtering directly there much more efficiently