Opened 11 years ago

Closed 7 years ago

#20414 closed Bug (fixed)

Handling of numbers under oracle is slow

Reported by: Shai Berger Owned by: Shai Berger
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords: oracle performance
Cc: Anssi Kääriäinen, Erin Kelly, Mariusz Felisiak Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

With current code, for all number columns, the Oracle backend asks cx_Oracle to return the numbers as strings, and then transforms them back to numbers on the way out. All this is done because Oracle supports higher precision (and a wider range of integers) than the Python float type; some values, if they are to be reported accurately, must be returned as decimal.Decimals, and cx_Oracle doesn't apparently do that on its own.

With cx_Oracle 5, though, it is possible to use a much better approach, on a per-column (rather than per-row) basis, selecting the right output type when there is enough information to do so, and making most of the decisions once-per-query instead of once-per-row.

An original patch for this was written by Ian Kelly; I improved it and made a pull-request in the stretch towards 1.5, but there was no ticket for it and so it was never merged. I hope we can finally merge it into 1.6.

There was a little saga of discussions around this (mostly Anssi, Ian and myself, https://groups.google.com/d/topic/django-developers/4BNkJyGez9A/discussion). Some other issues are also mentioned there. But this is the main one.

So now there is a ticket. Things changed, and the pull-request needed updating. The new one is at https://github.com/django/django/pull/1071.

Change History (6)

comment:1 by Russell Keith-Magee, 11 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Tim Graham, 11 years ago

Patch needs improvement: set

Newest pull request appears to be here. It at least needs to be updated to reflect the fact that it won't be added in 1.6 as currently documented.

comment:3 by Shai Berger, 11 years ago

Well, I seemed to have botched that branch -- I need to fix it. Until then, I'll close the PR.

Anyway, later tests I ran showed this to be a lot less promising than I had thought. The reason is that cx_Oracle, internally, does this return-number-as-string-and-transform-to-Python-int dance whenever the database column can hold more than 9 digits; Django's Oracle backend uses number(11) for ints. I'll revisit this when I finally get to meddling with cx_Oracle internals (this is needed mostly for Python 3 support, which is currently quite completely broken).

comment:5 by Mariusz Felisiak, 7 years ago

Cc: Mariusz Felisiak added

in reply to:  4 comment:6 by Shai Berger, 7 years ago

Resolution: fixed
Status: newclosed
Version: master2.0

Replying to felixxm:

I think it's already fixed in https://github.com/django/django/commit/e06cab260049bb58eafdc4f60ac50a5f3759c38c.

Well, it needed https://github.com/oracle/python-cx_Oracle/commit/b1d1a93a8749eb40e9a8f76d94b6b2434c84399b first, but yes, since both are in, I believe the issue should be closed. If there's any more issues with integers performance on Oracle, they're unrelated to this.

Note: See TracTickets for help on using tickets.
Back to Top