I'm using web.py to create a simple report page from Oracle. When I take the best practice approach of using vars= to pass parameters, the delay is 11-12 seconds. When I do the same query using string substitution, the query runs in less than a second. Here's how I'm checking:
sql = """
SELECT a, b, c
FROM my_table
WHERE what = $what
ORDER BY a, b"""
print('before', datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S'))
result = db.query(sql, vars={'what': '1234'})
print('after', datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S'))
The "before" and "after" show clearly than I'm getting a massive delay on the query. I've tried using select() with the same vars= and I get the same delay. So, my initial suggestion is that it's web.db's SQL escape functions that are creating the delay. I don't want to pass unescaped input, and it doesn't seem like there should be so much overhead.
Is there anything else that could be creating this delay? If it is the escape logic, are there any gotchas of which I need to be aware?
Thanks in advance!
EDIT:
On further investigation, I've proven (to myself, at least) that the delay is not specific to web.py, but happens in cx_Oracle. I reached this conclusion modifying my sql for syntax and doing:
cursor = db._db_cursor()
lines = cursor.execute(sql.format(my_table, {'what': '1234'})
...this produces a similar ten-to-twelve second delay compared to hard-coding the variable. Any cx_Oracle advice?
Alrighty. On further, further investigation, I figured out that the problem is a mismatch in encoding between Python and Oracle for the string parameter I pass. Fixed it with a simple what.encode('iso-8859-1'). If that doesn't work for you, check the Oracle encoding using the PL/SQL dump() function. If the Python encoding doesn't work, try decoding first.