[ORM Foreign Keys][Performance] How to use Charfield Foreign Keys efficiently?

[Dominik Szmaj]

Hey,

I have a very big performance problem with Django and Oracle db.

This legacy db has lots of primary keys as strings from the time when those id's were alphanumerical so it can't be simply converted to number.

So when I set FK on such varchar column django seems to not crash but it converts to int and search whole table of strings with a number which takes ages to complete.

Is there maybe some sane solution to the problem? Why it forces me to use numbers on varchar FK?

Best regards.

Dominik

[Avraham Serour]

can you post your model?

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e4e51608-740c-410c-bc47-21d189c0edf6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jani Tiainen]

Please, post your models in question. It's probably something really simple to resolve in general.

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 https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFWa6tKd-c9bUn5qSv9hFQvPSvVVyqOi1GpOWYQWz2uzL52h%2Bg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

-- 
Jani Tiainen

[Dominik Szmaj]

Hey,

'case_id' is a monkeypatch number(*) field so it works quick for now, not a pk, but unique:

class OmEvent(Model):
    case = ForeignKey('CaseInfo2', to_field='case_id', related_name='events', blank=True, null=True)
    
    class Meta:
        db_table = 'om_event'

old form:

class OmEvent(Model):
    case = ForeignKey('CaseInfo2', related_name='events', blank=True, null=True)
    
    class Meta:
        db_table = 'om_event'

which points to 'case_number' pk which is a varchar.

Everything is managed by db router which prevents migrations on remote db.

Regards,

Dominik

W dniu wtorek, 21 listopada 2017 15:00:49 UTC+1 użytkownik Avraham Serour napisał:

can you post your model?

On Tue, Nov 21, 2017 at 3:31 PM, Dominik Szmaj <domini...@snello.pl> wrote:

Hey,

I have a very big performance problem with Django and Oracle db.

This legacy db has lots of primary keys as strings from the time when those id's were alphanumerical so it can't be simply converted to number.

So when I set FK on such varchar column django seems to not crash but it converts to int and search whole table of strings with a number which takes ages to complete.

Is there maybe some sane solution to the problem? Why it forces me to use numbers on varchar FK?

Best regards.

Dominik

--
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 unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

[Peter of the Norse]

This should be a bug.  Most databases don’t even allow foreign keys to be a different type.  I didn’t see anything in the docs about this, but it should happen automatically.  There are several examples of using non-int fields, e.g. UUID.  Can you provide your migration file for OmEvent and model definition for CaseInfo2?

- Peter of the Norse

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a8512002-4fac-4ae0-b6ee-35a239d2670f%40googlegroups.com.