cx-oracle-users

[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

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
>

Re: [cx-oracle-users] Unicode issue

[Uwe H. <uw...@fa...>]

Hi Anthony,

  see below.

Regards
     Uwe

Am 13.01.2012 23:37, schrieb Anthony Tuininga:
> 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.

Yup , I had expected this. But that's not a very practical solution. 
 From my view of a *client* developer that means: Using unicode
with cx_Oracle is tough (For example you can use custom Cursor classes 
making a parameter conversion from unicode to str on the fly).
You have to touch every application when moving from 5.0.4 to 5.1.x.

As a side note: What does this mean (performance wise) for python3.x
and varchar2 attributes. I haven't tested this.


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.
>
If you have large applications with many queries it's also impractical 
to touch every query.


> 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
>>
>
> ------------------------------------------------------------------------------
> RSA(R) Conference 2012
> Mar 27 - Feb 2
> Save $400 by Jan. 27
> Register now!
> http://p.sf.net/sfu/rsa-sfdev2dev2

Re: [cx-oracle-users] Unicode issue

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

See below.

On Sat, Jan 14, 2012 at 2:17 AM, Uwe Hoffmann <uw...@fa...> wrote:
> Hi Anthony,
>
>  see below.
>
> Regards
>     Uwe
>
> Am 13.01.2012 23:37, schrieb Anthony Tuininga:
>> 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.
>
> Yup , I had expected this. But that's not a very practical solution.
>  From my view of a *client* developer that means: Using unicode
> with cx_Oracle is tough (For example you can use custom Cursor classes
> making a parameter conversion from unicode to str on the fly).
> You have to touch every application when moving from 5.0.4 to 5.1.x.

Well, there are other options that can "solve" this problem which I'll
get to in a minute. But your argument loses a lot of force when you
realize that you are essentially saying "I want to use Unicode in my
client throughout but *NOT* use Unicode in my database". If you used
Unicode in your database, your client would be perfectly happy. So
using Unicode is "tough" only when you want to only go halfway. :-)

Fortunately, cx_Oracle does have a method for handling this situation
so you don't have to write a custom cursor class and manipulate all of
the parameters on the fly. You can use connection wide or cursor
specific input and output type handlers. These allow you to manipulate
the variables passed between Oracle and Python quite conveniently. I
use this, for example, to turn all of my strings in the database into
Unicode. The same can be done for converting all unicode bind
variables to varchar2 type bind variables instead. Oracle does the
conversion itself on the fly.

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
        return cursor.var(unicode, size, cursor.arraysize)

def InputTypeHandler(cursor, value, numElements):
    if isinstance(value, unicode):
        return cursor.var(str, arraysize = numElements)

Then you can do the following

connection.outputtypehandler = OutputTypeHandler
connection.inputtypehandler = InputTypeHandler

That should cover everything for you quite nicely and make Unicode
"easy" again. :-)

> As a side note: What does this mean (performance wise) for python3.x
> and varchar2 attributes. I haven't tested this.

Well, everything is converted to string, as it turns out, so there is
no performance problem as Oracle is perfectly fine with accepting
string and converting it to Unicode but the reverse is not true. That
suggests, of course, that I should be doing the same thing in both
versions of Python but to be perfectly honest it never really crossed
my mind. :-) I'll give it some thought.

> 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.
>>
> If you have large applications with many queries it's also impractical
> to touch every query.
>
>
>> 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
>>>
>>
>> ------------------------------------------------------------------------------
>> RSA(R) Conference 2012
>> Mar 27 - Feb 2
>> Save $400 by Jan. 27
>> Register now!
>> http://p.sf.net/sfu/rsa-sfdev2dev2
>
>
>
> ------------------------------------------------------------------------------
> RSA(R) Conference 2012
> Mar 27 - Feb 2
> Save $400 by Jan. 27
> Register now!
> http://p.sf.net/sfu/rsa-sfdev2dev2
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Unicode issue

[Uwe H. <uw...@fa...>]

Am 15.01.2012 03:57, schrieb Anthony Tuininga:
> See below.
>
> On Sat, Jan 14, 2012 at 2:17 AM, Uwe Hoffmann<uw...@fa...>  wrote:
>> Hi Anthony,
>>
>>   see below.
>>
>> Regards
>>      Uwe
>>
>> Am 13.01.2012 23:37, schrieb Anthony Tuininga:
>>> 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.
>>
>> Yup , I had expected this. But that's not a very practical solution.
>>   From my view of a *client* developer that means: Using unicode
>> with cx_Oracle is tough (For example you can use custom Cursor classes
>> making a parameter conversion from unicode to str on the fly).
>> You have to touch every application when moving from 5.0.4 to 5.1.x.
>
> Well, there are other options that can "solve" this problem which I'll
> get to in a minute. But your argument loses a lot of force when you
> realize that you are essentially saying "I want to use Unicode in my
> client throughout but *NOT* use Unicode in my database". If you used
> Unicode in your database, your client would be perfectly happy. So
> using Unicode is "tough" only when you want to only go halfway. :-)
>

You are right, but unfortunately the world is not ideal. E.g. in my 
environment I have n databases and m clients. I can not enforce a
"only use nvchar2 attributes". So that means I'm stuck with "normal"
strings. But I think that's a normal environment for many people.
(OT: if you choose the wrong database characterset  (e.g.utf16) 
nvarchar2 will double your database size in the "nearly all
  characters are ascii" scenario. I only want to say that there are
many dependencies).


> Fortunately, cx_Oracle does have a method for handling this situation
> so you don't have to write a custom cursor class and manipulate all of
> the parameters on the fly. You can use connection wide or cursor
> specific input and output type handlers. These allow you to manipulate
> the variables passed between Oracle and Python quite conveniently. I
> use this, for example, to turn all of my strings in the database into
> Unicode. The same can be done for converting all unicode bind
> variables to varchar2 type bind variables instead. Oracle does the
> conversion itself on the fly.
>
> def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
>      if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
>          return cursor.var(unicode, size, cursor.arraysize)
>
> def InputTypeHandler(cursor, value, numElements):
>      if isinstance(value, unicode):
>          return cursor.var(str, arraysize = numElements)
>
> Then you can do the following
>
> connection.outputtypehandler = OutputTypeHandler
> connection.inputtypehandler = InputTypeHandler
>
> That should cover everything for you quite nicely and make Unicode
> "easy" again. :-)

yes. (maybe with the exception of the casual user here)

>
>> As a side note: What does this mean (performance wise) for python3.x
>> and varchar2 attributes. I haven't tested this.
>
> Well, everything is converted to string, as it turns out, so there is
> no performance problem as Oracle is perfectly fine with accepting
> string and converting it to Unicode but the reverse is not true. That
> suggests, of course, that I should be doing the same thing in both
> versions of Python but to be perfectly honest it never really crossed
> my mind. :-) I'll give it some thought.
>

that will also solve the "problems" of the casual user. The question is:
Are there any overfluous (character) conversions on the client (or the 
server) side.


regards
   Uwe

>> 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.
>>>
>> If you have large applications with many queries it's also impractical
>> to touch every query.
>>
>>
>>> 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

Re: [cx-oracle-users] Unicode issue

[Waldemar O. <wal...@gm...>]

On Sat, Jan 14, 2012 at 19:57, Anthony Tuininga
<ant...@gm...> wrote:
> See below.
>
> On Sat, Jan 14, 2012 at 2:17 AM, Uwe Hoffmann <uw...@fa...> wrote:
>> Hi Anthony,
>>
>>  see below.

> Well, everything is converted to string, as it turns out, so there is
> no performance problem as Oracle is perfectly fine with accepting
> string and converting it to Unicode but the reverse is not true. That
> suggests, of course, that I should be doing the same thing in both
> versions of Python but to be perfectly honest it never really crossed
> my mind. :-) I'll give it some thought.
>

Here is an observation that may matter in the discussion.
Passing "number like" unicode parameters worked in 5.04 but stopped
working in 5.1
It works again with 5.1 and Python3

C:\>python
Python 2.7.2 (default, Jun 12 2011, 15:08:59) [MSC v.1500 32 bit
(Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle as dbi
>>> dbi.version
'5.0.4'
>>> conn = dbi.connect('user/password@dbname')
>>> cur = conn.cursor()
>>> cur.execute('select * from stkhldr where stkhldr_id = :sid', sid=u'48')
<__builtin__.OracleCursor on <cx_Oracle.Connection to skhd@milenet_d>>


C:\>python
Python 2.7.2 (default, Jun 12 2011, 15:08:59) [MSC v.1500 32 bit
(Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle as dbi
>>> dbi.version
'5.1.1'
>>> conn = dbi.connect(u'user/passwd@dbname')
>>> cur = conn.cursor()
>>> cur.execute('select * from stkhldr where stkhldr_id = :sid', sid=u'48')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-01460: unimplemented or unreasonable
conversion requested