Slow performance with Django when connected to Oracle

[dw314159]

Django gurus:

Hello, I am experiencing very slow performance with Django when
connected to an Oracle database. The exact same Django application
runs far faster with PostgreSQL and SQLite, with the same source data
loaded into each database. Looking at the information embedded in
“connection.queries” after making an Oracle query through Django, it
appears that the queries themselves run very quickly (runtimes are
comparable to those measured with PostgreSQL and SQLite), but the
whole turnaround time for a query in Oracle far exceeds the reported
query time.

I’ve tried the built-in Oracle backend engine and django-oraclepool
0.7; neither improve performance. (With django-oraclepool, I also gave
sufficient time for the connections to pool before taking
measurements). Using cx_Oracle outside of Django on the same
workstation to connect to the same Oracle database, the query
turnaround time is very quick.

Is this problem connected to the fact that Django is reported to drop
the database connection between queries, and therefore must reconnect
to Oracle every time a query is executed? Or is there a Django
configuration option that must be specified to make the best use of
Oracle?

We are using Python 2.7, Django 1.2.1, Oracle 11, and Apache HTTP
Server 2.2.17.

Any help would be greatly appreciated. Thanks!

DW

[Ian]

On Feb 7, 9:44 pm, dw314159 <dw314...@gmail.com> wrote:
> Django gurus:
>
> Hello, I am experiencing very slow performance with Django when
> connected to an Oracle database. The exact same Django application
> runs far faster with PostgreSQL and SQLite, with the same source data
> loaded into each database. Looking at the information embedded in
> “connection.queries” after making an Oracle query through Django, it
> appears that the queries themselves run very quickly (runtimes are
> comparable to those measured with PostgreSQL and SQLite), but the
> whole turnaround time for a query in Oracle far exceeds the reported
> query time.
>
> I’ve tried the built-in Oracle backend engine and django-oraclepool
> 0.7; neither improve performance. (With django-oraclepool, I also gave
> sufficient time for the connections to pool before taking
> measurements). Using cx_Oracle outside of Django on the same
> workstation to connect to the same Oracle database, the query
> turnaround time is very quick.
>
> Is this problem connected to the fact that Django is reported to drop
> the database connection between queries, and therefore must reconnect
> to Oracle every time a query is executed? Or is there a Django
> configuration option that must be specified to make the best use of
> Oracle?

Django actually closes the connection after each HTTP request, not
each individual query. This means that if you were running your
timing tests in a request-less environment (i.e. through the Django
shell), you would not expect to see any slowdown caused by this. It
also means that if you are running more than a few queries to serve
each request, the overall effect may be less noticeable.

Have you tried tracing the code during a request? It would be helpful
to have at least a general idea of where it's spending so much time.

Cheers,
Ian

[dw314159]

Ian,

Thank you for the prompt reply!

I am observing the same behavior in the Django shell. Here the actual
query runtime is about the same between Oracle and PostgreSQL back-
ends, but the total turnaround time is about 18 times longer with
Oracle. I believe the following code demonstrates this case:

from django.db import connection
import minilims.log.models as log
import time
time_list = []
for n in range(0, 20):
t1 = time.time()
entries = log.Param.objects.filter(log = 6).order_by('stuff', 'id')
entry = [x for x in entries]
t2 = time.time()
time_list.append(t2 - t1)
print len(connection.queries), 'queries ran.'
average_time = sum(time_list) / len(time_list)
# display minimum, average, and maximum turnaround time
print min(time_list), average_time, max(time_list)
# display average query time
print sum([float(x['time']) for x in connection.queries]) /
len(connection.queries)

The above code in the shell using a PostgreSQL backend reports:

>>> # display minimum, average, and maximum turnaround time
>>> print min(time_list), average_time, max(time_list)
0.203052997589 0.211852610111 0.234575033188
>>>
>>> # display average query time
>>> print sum([float(x['time']) for x in connection.queries]) / len(connection.queries)
0.0557

However, running the same code with an Oracle back-end, after
restarting the shell, results in:

>>> # display minimum, average, and maximum turnaround time
>>> print min(time_list), average_time, max(time_list)
3.59030008316 3.64263659716 4.33223199844
>>>
>>> # display average query time
>>> print sum([float(x['time']) for x in connection.queries]) / len(connection.queries)
0.05825

Any ideas?

Thanks!

DW

[Ian]

What does the model look like, and how many rows does your query
return? The Oracle backend has to do some extra processing over the
data to get its results into the expected format. This may be what
you're seeing, although I would be surprised if it is really
dominating the query time by that much. LOB columns can also slow the
backend down significantly, since we have to make extra round-trips to
the database to read their contents. Neither of these things would be
included in the recorded query time.

If your model includes LOB columns or has a large number of fields,
then your best bet is probably to use QuerySet.defer() or
QuerySet.only() to limit the fields returned to those that are
specifically of interest.