Poor performance of Django ORM with Oracle

Question

I am building a Django website with an Oracle backend, and I observe very slow performance even when doing simple lookups on the primary key. The same code works very fast when the same data are loaded in MySQL.

What could be the reason for the poor performance? I have a suspicion that the problem is related to the use of Oracle bind parameters, but this may not be the case.

Django model (a test table with ~6,200,000 rows)

from django.db import models

class Mytable(models.Model):
    upi = models.CharField(primary_key=True, max_length=13)

    class Meta:
        db_table = 'mytable'

Django ORM (takes ~ 1s)

from myapp.models import *
r = Mytable.objects.get(upi='xxxxxxxxxxxxx')

Raw query with bind parameters (takes ~ 1s)

cursor.execute("SELECT * FROM mytable WHERE upi = %s", ['xxxxxxxxxxxxx'])
row = cursor.fetchone()
print row

Raw query with no bind parameters (instantaneous)

cursor.execute("SELECT * FROM mytable WHERE upi = 'xxxxxxxxxxxxx'")
row = cursor.fetchone()
print row

My environment

• Python 2.6.6
• Django 1.5.4
• cx-Oracle 5.1.2
• Oracle 11g

When connecting to the Oracle database I specify:

'OPTIONS': {
    'threaded': True,
}

Any help will be greatly appreciated.

[Update] I did some further testing using the debugsqlshell tool from the Django debug toolbar.

# takes ~1s
>>>Mytable.objects.get(upi='xxxxxxxxxxxxx')
SELECT "Mytable"."UPI"
FROM "Mytable"
WHERE "Mytable"."UPI" = :arg0  [2.70ms]

This suggests that Django uses the Oracle bind parameters, and the query itself is very fast, but creating the corresponding Python object takes a very long time.

Just to confirm, I ran the same query using cx_Oracle (note that the cursor in my original question is the Django cursor).

import cx_Oracle
db= cx_Oracle.connect('connection_string')
cursor = db.cursor()

# instantaneous
cursor.execute('SELECT * from mytable where upi = :upi', {'upi':'xxxxxxxxxxxxx'})
cursor.fetchall()

What could be slowing down Django ORM?

[Update 2] We looked at the database performance from the Oracle side, and it turns out that the index is not used when the query comes from Django. Any ideas why this might be the case?

Solo

Did you check the index for the lookup field exist in db? – Dugaid

When I inspect the table in SQL Developer I see that there is a valid normal index on that column. – Solo

What happens if you run the 2 versions in SQL Developer, and are the query plans different (use Explain Plan or Autotrace buttons)? For bind variables use SELECT * FROM mytable WHERE upi = :s and SQL Developer will prompt you for the value. – Solidus

@TonyAndrews Both queries use the same execution plan, which scans the unique index. – Solo

And both are instantaneous in SQL Dev? – Solidus

@TonyAndrews Yes, both queries are super fast in SQL Dev (All rows fetched: 1 in 0.002 seconds). – Solo

Possible duplicate??? #16905065 – Dogoodism

Although, also, possibly not. Your key appears to be a string already. – Dogoodism

@Dogoodism Thank you for linking this SO question. It has some interesting leads, but, as you pointed out, the accepted answer does not seem to be applicable here because the bind parameter is a string. – Solo

I edited my question to include additional tests that suggest that Django ORM may be slow when working with Oracle. – Solo

What happens if you add db_index=True? – Collision

@BurhanKhalid Thanks for the suggestion, but still no luck. I think db_index only matters when the model is created: docs.djangoproject.com/en/1.5/ref/models/fields/#db-index – Solo

Answer 1

After working with our DBAs, it turned out that for some reason the Django get(upi='xxxxxxxxxxxx') queries didn't use the database index.

When the same query was rewritten using filter(upi='xxxxxxxxxxxx')[:1].get(), the query was fast.

The get query was fast only with integer primary keys (it was string in the original question).

FINAL SOLUTION

create index index_name on Mytable(SYS_OP_C2C(upi));

There seems to be some mismatch between the character sets used by cx_Oracle and Oracle. Adding the C2C index fixes the problem.

UPDATE: Also, switching to NVARCHAR2 from VARCHAR2 in Oracle has the same effect and can be used instead of the functional index.

Here are some useful discussion threads that helped me: http://comments.gmane.org/gmane.comp.python.db.cx-oracle/3049 http://comments.gmane.org/gmane.comp.python.db.cx-oracle/2940

Solo

Answer 2

Using TO_CHAR(character) should solve the performance issue:

cursor.execute("SELECT * FROM mytable WHERE upi = TO_CHAR(%s)", ['xxxxxxxxxxxxx'])

Rudd