Re: Python 2: unicode vs str param causing slowness

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 <[email protected]<mailto:[email protected]>>
Organization: Oracle Corporation
Date: Sunday, January 25, 2015 10:40 PM
To: "cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org<mailto:cx-oracle-users-5NWGOfrQmneHXe+LvDLADg@public.gmane.orgrceforge.net>" <cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org<mailto:[email protected]>>
Cc: Dayton Gomez <dayton_gomez-okLH5SSHHyRWk0Htik3J/[email protected]<mailto:dayton_gomez-okLH5SSHHyTQFizaE/[email protected]>>
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

------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users