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)
> 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/7b3fd61c-f2de-4e29-87b8-1637ce1f5169%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
