Thanks you for your response
Unfortunately, yes, the 1.5b records is not something I have control over.
I agree the SYSGUID field needs working. However, if I change this field to
any other field type, the query still takes over 40 seconds.
class Marker(models.Model):
marker_id = models.BinaryField(primary_key=True) # This field type is
a guess.
markid = models.CharField(unique=True, max_length=20, blank=True)
crop = models.ForeignKey("variantdb_admin.Crop", blank=True, null=True)
insid = models.CharField(max_length=5, blank=True)
insdate = models.DateTimeField(blank=True, null=True)
class Meta:
managed = False
db_table = "prod_schema"."marker"
DEBUG (58.566) QUERY = u'SELECT * FROM (SELECT "_SUB".*, ROWNUM AS "_RN"
FROM (SELECT "PROD_SCHEMA"."MARKER"."MARKER_ID",
PROD_SCHEMA"."MARKER"."MARKID", "PROD_SCHEMA"."MARKER"."CROP_ID",
"PROD_SCHEMA"."MARKER"."INSID", "PROD_SCHEMA"."MARKER"."INSDATE" FROM
"PROD_SCHEMA"."MARKER" WHERE "PROD_SCHEMA"."MARKER"."MARKID" = :arg0)
"_SUB" WHERE ROWNUM <= 1) WHERE "_RN" > 0' - PARAMS = (u'TO1',);
args=('TO1',)
thanks again for your help
joris
On Thu, Jan 22, 2015 at 4:51 PM, SK <korotki...@gmail.com> wrote:
> To many calculations in the from_db_value function. Need optimizations. And
> what about query limitations: 1.5b is really needed?
>
>
>
>
> class SYSGUID16Field(models.Field):
> default_error_messages = {
> 'invalid': "'%(value)s' is not a valid SYS_GUID."
> }
>
> description = "A connector to the SYS_GUID() fields for Oracle
> Backends"
>
> def __init__(self, *args, **kwargs):
> kwargs['max_length'] = 16
> super(SYSGUID16Field, self).__init__(*args,**kwargs)
>
> def from_db_value(self, value, connection):
> #print 'call from_db_value %s' % value
> if value is None:
> return value
> return str(b2a_hex(value)).upper()
>
>
>
>
>
>
> четверг, 22 января 2015 г., 18:32:36 UTC+3 пользователь Joris Benschop
> написал:
>
>> Dear List,
>>
>> I'm trying to run a simple select on a very large Oracle table (1500
>> million records).
>>
>> I create a standard django model:
>> ------------------
>> class Marker(models.Model):
>> marker_id = SYSGUID16Field(primary_key=True) # This field type is a
>> guess.
>> markid = models.CharField(unique=True, max_length=20, blank=True)
>> crop = models.ForeignKey("variantdb_admin.Crop", blank=True,
>> null=True)
>> insid = models.CharField(max_length=5, blank=True)
>> insdate = models.DateTimeField(blank=True, null=True)
>>
>> class Meta:
>> managed = False
>> db_table = "prod_schema"."marker"
>> ------------------
>>
>> then simply run:
>> >>> x = Marker.objects.filter(markid = 'TO11')
>> >>> print x
>>
>> in debugger, this basically creates the following simple query:
>> DEBUG (44.120) QUERY = u'SELECT "PROD_SCHEMA"."MARKER"."MARKER_ID",
>> "PROD_SCHEMA"."MARKER"."MARKID", "PROD_SCHEMA"."MARKER"."CROP_ID",
>> "PROD_SCHEMA"."MARKER"."INSID", "PROD_SCHEMA"."MARKER"."INSDATE" FROM
>> "PROD_SCHEMA"."MARKER" WHERE "PROD_SCHEMA"."MARKER"."MARKID" = :arg0' -
>> PARAMS = (u'TO11',); args=('TO11',)
>>
>> As you see, in Django this takes 44 seconds to run.
>> If I run this exact same query with cx_oracle directly the response time
>> = < 0.1sec.
>>
>> For small tables the performance of django is quite good, so it almost
>> seems like django tries to do a count on the table before runnign the query
>> or something. Any suggestions on how I can make this lookup faster?
>>
>> thanks
>> joris
>>
>>
>>
>> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Django users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/django-users/CYIxM8nZl1A/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> django-users+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@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/29682a24-fdb6-4883-882e-58fb53d0a98c%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/29682a24-fdb6-4883-882e-58fb53d0a98c%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/CAFwPP0oa%2BMP0rQQ%2Boot1HAjV6DbEqqGe5AzptjWhm%3DweCx%3Dn9w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
