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.Decimal
s, 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 , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 11 years ago
Patch needs improvement: | set |
---|
comment:3 by , 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).
follow-up: 6 comment:4 by , 7 years ago
I think it's already fixed in https://github.com/django/django/commit/e06cab260049bb58eafdc4f60ac50a5f3759c38c.
comment:5 by , 7 years ago
Cc: | added |
---|
comment:6 by , 7 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Version: | master → 2.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.
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.