Re: [cx-oracle-users] Unicode issue

[Anthony T. <ant...@gm...>]

Hi,

I just had a chance to test this out. This is "normal" and "expected"
behavior -- not what you want but that's a different thing! I tested
this with nvarchar2 instead of varchar2 and in that situation *both*
string and unicode parameters work equally well. You can also add this
line

cursor.setinputsizes(P1 = str)

That will force Oracle to treat it as a string (instead of Unicode)
and then all is well as well.

Hope that helps.

Anthony

2011/12/6 Brian Watson <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
>
> ------------------------------------------------------------------------------
> Cloud Services Checklist: Pricing and Packaging Optimization
> This white paper is intended to serve as a reference, checklist and point of
> discussion for anyone considering optimizing the pricing and packaging model
> of a cloud services business. Read Now!
> http://www.accelacomm.com/jaw/sfnl/114/51491232/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>