Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

[Dayton G. <Day...@sy...>]

I'll provide as much as I can, but some of it is private (full schema or actual data.

 1.  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
    *   client:
       *   oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
       *   oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
 2.  cx-oracle version: 5.1.3
 3.  NLS_CHARACTERSET       WE8MSWIN1252
 4.  NLS_NCHAR_CHARACTERSET       AL16UTF16  - is this what you requested?
 5.  NLS_LANG = '.UTF8' (set by Django), the database has… NLS_LANGUAGE       AMERICAN

For the schema, there's actually two tables. Again, I can't share you the whole schema, but here's some of it.

"device":
 ID       NOT NULL NUMBER(11) - pk
 PLATFORM NVARCHAR2(20) - indexed
USER_ID NUMBER(11) - FK to "user"
 IS_ACTIVE        NOT NULL NUMBER(1) - indexed

"user":
 ID       NOT NULL NUMBER(11) - pk
 USERNAME NVARCHAR2(75)
 IS_ACTIVE       NOT NULL NUMBER(1) - indexed


The query does something like…

SLOW:
q = cxcursor.execute('''SELECT COUNT(*) FROM "DEVICE" INNER JOIN "USER" ON ( "DEVICE"."USER_ID" = "USER"."ID" ) WHERE ("DEVICE"."IS_ACTIVE" = 1  AND "USER"."IS_ACTIVE" = 1  AND "DEVICE"."USER_ID" IS NOT NULL AND  "DEVICE"."PLATFORM" = :platform )''', {'platform': u'ios'})
FAST:
q = cxcursor.execute('''SELECT COUNT(*) FROM "DEVICE" INNER JOIN "USER" ON ( "DEVICE"."USER_ID" = "USER"."ID" ) WHERE ("DEVICE"."IS_ACTIVE" = 1  AND "USER"."IS_ACTIVE" = 1  AND "DEVICE"."USER_ID" IS NOT NULL AND  "DEVICE"."PLATFORM" = :platform )''', {'platform': 'ios'})

As for data, the DEVICE table has about 25k entries, with the user table about the same size.

I just tried using one table, no join, and it was extremely fast. So it seems to be the INNER JOIN causing the issues, when coupled with the param.
--
From: avinash nandakumar <avi...@or...<mailto:avi...@or...>>
Organization: Oracle Corporation
Date: Sunday, January 25, 2015 10:40 PM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Cc: Dayton Gomez <day...@sy...<mailto:day...@sy...>>
Subject: Re: [cx-oracle-users] Python 2: unicode vs str param causing slowness

Hello Dayton,

I have tried to reproduce the issue on cx-oracle with Oracle 11 with DB charset W8DEC and NCHAR charset AL16UTF16.
However I was not able to reproduce the problem.

Although some of these you have provided before in order to consolidate can you please give details for the following:-
1. Oracle version number
2. cx-oracle version
3. DB characterset
4. DB Ncharset
5. NLS_LANG
6. Table schema
7. Table data

Best Regards,
Avinash