Execute a prepared statement in sqlalchemy

Question

I have to run 40K requests against a username:

SELECT * from user WHERE login = :login

It's slow, so I figured I would just use a prepared statement.

So I do

e = sqlalchemy.create_engine(...)
c = e.connect()
c.execute("PREPARE userinfo(text) AS SELECT * from user WHERE login = $1")
r = c.execute("EXECUTE userinfo('bob')")
for x in r:
    do_foo()

But I have a:

InterfaceError: (InterfaceError) cursor already closed None None

I don't understand why I get an exception

Answer 1

From this discussion, it might be a good idea to check your paster debug logs in case there is a better error message there.

Answer 2

Not sure how to solve your cursor related error message, but I dont think a prepared staement will solve your performance issue - as long as your using SQL server 2005 or later the execution plan for SELECT * from user WHERE login = $login will already be re-used and there will be no performance gain from the prepared statement. I dont know about MySql or other SQL database servers, but I suspect they too have similar optimisations for Ad-Hoc queries that make the prepared statement redundant.

It sounds like the cause of the performance hit is more down to the fact that you are making 40,000 round trips to the database - you should try and rewrite the query so that you are only executing one SQL statement with a list of the login names. Am I right in thinking that MySql supports an aray data type? If it doesnt (or you are using Microsoft SQL) you should look into passing in some sort of delimited list of usernames.