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]
On Thursday, 27 February 2014 11:48:40 UTC-4, Shawn H wrote:
>
> 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.
>
> On Thursday, February 27, 2014 9:27:48 AM UTC-6, Scott Anderson wrote:
>>
>> 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
>>
>> On Wednesday, February 26, 2014 5:53:15 PM UTC-5, Shawn H wrote:
>>>
>>> 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()
>>>
>>>
>>> On Wednesday, February 26, 2014 4:41:06 PM UTC-6, 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.
>>>>
>>>> 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 <[email protected]>
>>>>>>> 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 [email protected].
To post to this group, send email to [email protected].
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/b3a44d11-6d45-4700-8a23-424956535ae0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
