Terrible performance when dropping into raw SQL Oracle

[Shawn H]

I've an app that has to drop into raw sql to run a query in a different Oracle database.  The ORM isn't involved as I'm trying to get a count of rows that meet specific criteria to return that count via an AJAX call.  Running the identical query in SqlPlus takes ~4 seconds.  Django takes ~16 seconds.  What might be the problem?  My view function is below.  The 16 seconds is between cursor.execute and the if number_count[0][0] lines.  Thanks in advance for the help.

def get_number_of_notices(request, case_number):

from django.db import connections

try:

cursor = connections['landmgm'].cursor()

cursor.execute('SELECT count(1) from (SELECT DISTINCT RECORDNUMB FROM NOTICED_PARCELS WHERE CASE_NUMBER = %s AND RECORDNUMB > 0 UNION \

SELECT DISTINCT RECORDNUMB FROM CONDONOTICE WHERE CASE_NUMBER = %s AND RECORDNUMB > 0)', [case_number, case_number])

number_count = cursor.fetchone()

if number_count[0][0] <= 0:

msg = json.dumps('{"msg":"Notification has not been run for this case", "number_notified":"0", "result":"Fail"}')

return HttpResponse(msg, content_type='application/json')

else:

msg = json.dumps('{"msg":"Notification has been run for this case", "number_notified":"' + str(number_count[0][0]) + '", "result":"Success"}')

return HttpResponse(msg, content_type='application/json')

except:

msg = json.dumps('{"msg":"An error was encountered while checking this case number. Please contact GIS Staff.", "result":"Error"}')

return HttpResponse(msg, content_type='application/json')

[Nikolas Stevenson-Molnar]

Is the case number the same when you run the query in SqlPlus as when you run it in Django? In other words, are you certain the query is exactly the same?

_Nik

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9aeb979c-4cce-4525-b813-2a35558cc62b%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Shawn H]

Yes.  I've tested with several case numbers, and I'm using a similar parameterized approach in my gui Oracle client as well, with the same results.  It's always about 3 to 4 times slower running via django.  I've tried it both on my local development web server as well as my production apache linux box, and it always takes much longer running via django.

[Tom Evans]

On Wed, Feb 26, 2014 at 6:16 PM, Shawn H <shawn....@gmail.com> wrote:
> Yes. I've tested with several case numbers, and I'm using a similar
> parameterized approach in my gui Oracle client as well, with the same
> results. It's always about 3 to 4 times slower running via django. I've
> tried it both on my local development web server as well as my production
> apache linux box, and it always takes much longer running via django.
>
>

If you write a standard python program, ie not using django, but still
using whatever oracle DB adapter Django uses, that connects to your
oracle server and executes the query, is it still slow?

IE is the problem something django does, or how the adapter works.

Cheers

Tom

[Shawn H]

Good idea.  I'll try that and report back

[Shawn H]

3.8 seconds.  It seems to be django, not cx_Oracle.

[Shawn H]

Because this worked so well, I've gone directly to cx_Oracle in my django view and used that to get the result in the 4 seconds.  There is definitely a problem with the Django implementation - I wonder if perhaps the indexes on the tables aren't being used properly.

[Shawn H]

I said that before testing it.  The exact same code using cx_Oracle takes ~4 seconds outside of the django environment, but still takes ~16 seconds when running in the django view.  What the heck is going on?  Updated portion of code below

cnxn = cx_Oracle.connect('notification/notifydev@landmgm')

cursor = cx_Oracle.Cursor(cnxn) #connections['landtest_11'].cursor()

print datetime.datetime.now()

cursor.execute('SELECT count(1) from (SELECT DISTINCT RECORDNUMB FROM DEVGIS.NOTICED_PARCELS WHERE CASE_NUMBER = &s AND RECORDNUMB > 0 UNION \

SELECT DISTINCT RECORDNUMB FROM DEVGIS.CONDONOTICE WHERE CASE_NUMBER = &s AND RECORDNUMB > 0)', [case_number, case_number])

print datetime.datetime.now()

number_count = cursor.fetchone()

[Thomas Lockhart]

On 2/26/14 2:41 PM, Shawn H wrote:

Because this worked so well, I've gone directly to cx_Oracle in my django view and used that to get the result in the 4 seconds.  There is definitely a problem with the Django implementation - I wonder if perhaps the indexes on the tables aren't being used properly.

That would seem unlikely since you are specifying the query directly.

If you suspect that Django *is* modifying your query, can you look at the query as it arrives to the Oracle backend and compare the two test cases?

hth

                        - Tom

On Wednesday, February 26, 2014 3:49:47 PM UTC-6, Shawn H wrote:

3.8 seconds.  It seems to be django, not cx_Oracle.

On Wednesday, February 26, 2014 2:50:58 PM UTC-6, Shawn H wrote:

Good idea.  I'll try that and report back

On Wednesday, February 26, 2014 1:22:52 PM UTC-6, Tom Evans wrote:

On Wed, Feb 26, 2014 at 6:16 PM, Shawn H <shawn....@gmail.com> wrote:
> Yes.  I've tested with several case numbers, and I'm using a similar
> parameterized approach in my gui Oracle client as well, with the same
> results.  It's always about 3 to 4 times slower running via django.  I've
> tried it both on my local development web server as well as my production
> apache linux box, and it always takes much longer running via django.
>
>

If you write a standard python program, ie not using django, but still
using whatever oracle DB adapter Django uses, that connects to your
oracle server and executes the query, is it still slow?

IE is the problem something django does, or how the adapter works.

Cheers

Tom

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/c4ff4ee0-43b8-4d1f-9ef0-6ef93546dcad%40googlegroups.com.

[Scott Anderson]

Are you timing the query in the view specifically, or the entire view? That is, do you know for sure that it is *only* the query that is taking 16 seconds and not the rest of the view?

-scott

[Shawn H]

The cursor.execute specifically.  I'm printing a timestamp immediately before and immediately after that line.  I'm positive it is ONLY the query that takes 16 seconds, whether using django.cursor or a cx_Oracle cursor.

[junctionapps]

Hey Shawn, would you do me a favour and try something a query with a implicit distinct like the following substituting YOURRECORDTABLE for whatever table holds your recordnumb as a unique value (I'm making a large assumption you have a table where each recordnum occurs once). Could you let me know if you see improvements in speed from the SQLPlus, and subsequently Django?

SELECT count(1) from (

SELECT RECORDNUMB FROM YOURRECORDTABLE

where RECORDNUMB IN 

(

SELECT RECORDNUM

FROM NOTICED_PARCELS 

WHERE CASE_NUMBER = %s AND RECORDNUMB > 0

 UNION 

SELECT RECORDNUMB 

FROM CONDONOTICE 

WHERE CASE_NUMBER = %s AND RECORDNUMB > 0

)', [case_number, case_number]

[Shawn H]

I can't do that, as your assumption is incorrect. I don't have a table that holds recordnumb as a unique value.  Each recordnumb occurs many times, and occurs many times per case number, by design. The number is used to identify unique ownership among many properties for a particular case - each unique owner is assigned a recordnumb and that number is assigned to each property that the unique owner owns. That's why we're doing a distinct, so we can identify the number of unique recordnumb values for a particular case.  BTW, I tried using max() instead of distinct, with no change, which I expected since the database solution would be nearly identical in that case.  Hopefully I didn't misunderstand what you were requesting I try.

On Monday, March 3, 2014 1:43:35 PM UTC-6, junctionapps wrote:

Hey Shawn, would you do me a favour and try something a query with a implicit distinct like the following substituting YOURRECORDTABLE for whatever table holds your recordnumb as a unique value (I'm making a large assumption you have a table where each recordnum occurs once). Could you let me know if you see improvements in speed from the SQLPlus, and subsequently Django?

SELECT count(1) from (

SELECT RECORDNUMB FROM YOURRECORDTABLE

where RECORDNUMB IN 

(

SELECT RECORDNUM

FROM NOTICED_PARCELS 

WHERE CASE_NUMBER = %s AND RECORDNUMB > 0

 UNION count(1)

[Kurtis Mullins]

Sort of off-topic as I don't have anything constructive to add to help fix your problem but I recommend not running complex (long-running) queries directly in Django views. Perhaps use Celery. 

I know this doesn't solve your problem (although it might help a bit if Django's environment is causing issues).

Good luck!

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7b3fd61c-f2de-4e29-87b8-1637ce1f5169%40googlegroups.com.

[junctionapps]

You understood it exactly. I've been cleaning up some old SQL statements (outside of use in Django) and have seen some real benefits of restructuring to reduce execution times. Unnecessary overuse of 'distinct' was a favourite of someone in my organization a few years back. Good luck!