[cx-oracle-users] Unicode issue

[Brian W. <bw...@ma...>]

Hello everyone,

 I'm referencing bug 3442096:

 https://sourceforge.net/tracker/index.php?func=detail&aid=3442096&group_id=84168&atid=571972

 We just updated a webapp from SQLAlchemy 0.4 to 0.7.3. Once we went live, the server slowed to a crawl. We put them back on their old server while we diagnosed. I hadn't noticed before, but the new orm emits unicode parameters. The unicode version is much slower:

 2011-12-06 14:12:24,439 INFO sqlalchemy.engine.base.Engine SELECT [......CUT......]
 FROM demp_inventory
 WHERE demp_inventory.inventory_snsn = :inventory_snsn_1 AND demp_inventory.inventory_slocation = :inventory_slocation_1 AND demp_inventory.inventory_sentityid = :inventory_sentityid_1 AND demp_inventory.inventory_scc = :inventory_scc_1 AND (demp_inventory.inventory_sshelflifeid IS NULL OR demp_inventory.inventory_sshelflifeid = :inventory_sshelflifeid_1) AND (demp_inventory.inventory_sserialno IS NULL OR demp_inventory.inventory_sserialno = :inventory_sserialno_1))
 WHERE ROWNUM <= :ROWNUM_1
 2011-12-06 14:12:24,439 INFO sqlalchemy.engine.base.Engine {'inventory_snsn_1': u'0000LLCQA7546', 'inventory_slocation_1': u'JNK1234114', 'inventory_sserialno_1': '', 'ROWNUM_1': 1, 'inventory_sentityid_1': u'bae9b856-d874-11df-89f4-0022191e6cbe', 'inventory_sshelflifeid_1': '', 'inventory_scc_1': u'A'}

 0.419656 seconds to execute

 2011-12-06 13:59:15,882 INFO sqlalchemy.engine.base.Engine SELECT [......CUT......]
 FROM demp_inventory
 WHERE demp_inventory.inventory_snsn = :inventory_snsn_1 AND demp_inventory.inventory_slocation = :inventory_slocation_1 AND demp_inventory.inventory_sentityid = :inventory_sentityid_1 AND demp_inventory.inventory_scc = :inventory_scc_1 AND (demp_inventory.inventory_sshelflifeid IS NULL OR demp_inventory.inventory_sshelflifeid = :inventory_sshelflifeid_1) AND (demp_inventory.inventory_sserialno IS NULL OR demp_inventory.inventory_sserialno = :inventory_sserialno_1))
 WHERE ROWNUM <= :ROWNUM_1
 2011-12-06 13:59:15,882 INFO sqlalchemy.engine.base.Engine {'inventory_snsn_1': '0000LLCQA7546', 'inventory_slocation_1': 'JNK1234114', 'inventory_sserialno_1': '', 'ROWNUM_1': 1, 'inventory_sentityid_1': 'bae9b856-d874-11df-89f4-0022191e6cbe', 'inventory_sshelflifeid_1': '', 'inventory_scc_1': 'A'}

 0.014337 seconds to execute

 I've tested on cx_Oracle 5.0.3, 5.0.4, and 5.1.1 with identical results. I've learned my lesson on deployment, and I'll never do another rollout without performance testing first. I've tried various fixes so far with no luck. I've downloaded the source to try and diagnose my own problem, but thats easier said than done. I'm trying to get sqla to nix unicode params. If anyone has any suggestions or things to try, PLEASE let me know. We use cx in production and would buy support or donate to solve our issue.

 Many thanks,
 Brian Watson