Using sqlalchemy to execute sql DRASTICALLY slows down runtime

Question

I have a rather long query (there were 7 joins, now 7 subqueries, because in raw sql 7 the subselects were much faster - I don’t even know when 7 joins would end if I let it work, but more than 1 min against 0, 05 seconds. With subqueries)

When I run it on db, it, as I said, takes .05-.1 seconds to execute. Just using session.execute() slows it down to a minute!

Is there anything I can do?

Let me know if you need more information. I suspect this is a common sqlalchemy thing. Perhaps sqlalchemy is setting up a query plan, and not just allowing mysql to do this? Or...?

EDIT: run the explanation for both, and they seem identical, except that sqlalchemy adds "using temporary using filesort" to the extra column. Is that what slows it down? How can I stop this from doing this?

EDIT 2: DEFINITELY sqlalchemy. I tried using the MySQL cursor to execute instead of the SA session and got the same .05 second runtime.

EDIT 3:

Code for creating our engine:

 engine_ro = create_engine( config.ro_database_url, #string with username, password, db pool_size=config.database_pool_size, #int max_overflow=config.database_max_overflow, #int pool_timeout=config.database_timeout, # int echo=config.database_echo, #False echo_pool=config.database_echo, #same as echo #False listeners=[GoneAway()] if config.database_use_listeners else None) 

where GoneAway() is a method that executes SELECT 1 to test the connection.

To create a session object:

 SessionRO = scoped_session(sessionmaker(bind=engine_ro, autocommit=False)) 

where scoped_session and sessionmaker are sqlalchemy functions.

Then the code that executes the request:

 session = SessionRO() results = session.execute(sql, params) 

EDIT 4: In case someone wonders if I comment on the listeners bit, it is still slow. Also, if I just use sessionmaker without scoped_session.

Answer 1

sqlalchemy not setting up a query plan or anything else unusual. It simply generates SQL and sends it through a DB-API-2.0 connection. So, if you explicitly call execute with the same expression that sqlalchemy generated, it will work in exactly the same way. *

The easiest way to see what sqlalchemy generating queries is to pass echo=True as an additional parameter in the create_engine call.

In your case, the query generated by sqlalchemy was actually different from your manual query, because it tested an integer parameter with a string, and not with an int.

---

* It is not 100% guaranteed; you must make sure that any connection parameters in the DB-API-2.0 connect function match, and neither you nor sqlalchemy performed any PRAGMA . But you can test them the same way you can test the request itself.

Answer 2

Here is a real set of tests to compare the MySQL cursor with the SQLAlchemy engine and session. Please replace the connection and SQL information at the bottom, and then run it. Tell us what timings are.

 import time def time_thing(fn, description): print "Running %s" % description now = time.time() try: ret = fn() return ret finally: spent = time.time() - now print "Finished %s, took %d seconds" % (description, spent) def with_mysqldb(sql): import MySQLdb conn = MySQLdb.connect(db=DBNAME, user=USERNAME, passwd=PASSWORD, host=HOST) def go(): cursor = conn.cursor() cursor.execute(sql) # if result fetching is the issue: # cursor.fetchall() cursor.close() time_thing(go, "Executing SQL with MySQLdb cursor") def _sqla_engine_w_test_connection(): from sqlalchemy import create_engine eng = create_engine(SQLALCHEMY_URL) def test(): conn = eng.connect() result = conn.execute("select 1") assert result.fetchall()[0] == (1, ) time_thing(test, "Making a test connection...") return eng def with_sqlalchemy(sql): eng = _sqla_engine_w_test_connection() def go(): result = eng.execute(sql) # if result fetching is the issue: # result.fetchall() result.close() time_thing(go, "Executing SQL with SQLA engine") def with_sqlalchemy_session(sql): from sqlalchemy.orm import Session eng = _sqla_engine_w_test_connection() def go(): sess = Session(eng) result = sess.execute(sql) # if result fetching is the issue: # result.fetchall() result.close() time_thing(go, "Executing SQL SQLA session") SQLALCHEMY_URL = "mysql://scott: tiger@localhost /test" DBNAME = "test" HOST = "localhost" USERNAME = "scott" PASSWORD = "tiger" SQL = "SELECT 1" with_mysqldb(SQL) with_sqlalchemy(SQL) with_sqlalchemy_session(SQL) 

Answer 3

What DBAPI are you using? Maybe try changing it to something else. I am currently working with PostgreSQL, and I have a big performance difference between pypostgresql and psycopg2 (the latter is much faster).

See the SQLAchemy documentation for a list of available DBAPIs for MySQL: chapter 4.1.5.

Answer 4

The real problem here was idiotic. Actually really stupid of me. abarnert really guessed that it was indeed at an early stage, but it was so small that it took me 2 days to find.

In the console version, I inserted the right things. In the SqlAlchemy version, I tested AN INT PARAMETER WITH STRING.

Facepalm

Thanks for the help guys. Don't you like these 2 character errors?