Re: [cx-oracle-users] cx-oracle performance with django and NCLOB data type

[Shai B. <sh...@pl...>]

On Thursday 26 March 2015 02:54:36 Daniel Porter wrote:
> On Mar 25, 2015 3:08 PM, "Shai Berger" <sh...@pl...> wrote:
> 
> > Queries which return 6000 records and use LOB fields from each of the
> > records are quite rare for Web servers; depending on the details of your 
> > use case, you may be able to improve things a lot by using Django's
> > defer(), which makes the relevant fields lazy -- retrieved from the DB only
> > when the specific field is accessed.
> > 
> > Actually, queries which return 6000 records -- with or without LOBs --
> > aren't very common for Django's use cases;
> 
> We're doing a model choice field where users can choose from a list of
> people. It just happens we have a boatload of people. We're using
> model.objects.only(...) to get only the fields that represent it. It still
> takes like a second to pull those 6k records, as compared to it occurring
> "instantly" (i.e. faster than i can perceive) with a postgres database.
> 
I would question your Oracle setup. Talk to your DBA, see where the time is 
spent. It could be that your Oracle is short on memory or there's a vicious 
firewall between your web server and database server or something of that sort.

Also, surely you're not presenting your users with a 6000-entry select-box... 
so there's a lot you can do. But discussion of that should be on django-users.

> > Django retrieves records from Oracle in
> > batches of 100. You can probably improve the 1.5s number by going into
> > the Django source (django/db/backends/oracle/base.py), finding the line
> > that says "self.cursor.arraysize = 100", and changing it to, say, 1000.
> > But there are probably other things you can do, which would help you more.
> 
> Will investigate that. Do you know what the tradeoffs there are? Does it
> just up memory consumption, or would it somehow slow down smaller queries?
> 

Assuming there's no shortage of memory, smaller queries shouldn't be affected.

> > I'd guess you stand to gain most by using the "regular" performance-
> > improvement techniques -- those related to the database, like indexing,
> > those unrelated, like caching, and those web-related, like breaking the
> > original service request into pieces (e.g. load initial chunk of data
> > with the main request and get the rest in separate AJAX requests). Of
> > course, I know almost nothing about your use-case, so I may be completely
> > off, but that would be the common case.
> 
> Memcached might be a real boon. The other bits, while important, don't
> really address making SELECT * FROM TABLE faster, though, right? Badly set
> up indexes wouldn't interfere with that operation?
> 

You imply that there is no where-clause; in that case, no, bad indexes 
wouldn't matter much. But also, again, check Oracle setup. With a sane setup, 
if you keep querying a 6k-row-table for the whole table, it will be cached in 
memory (on the database server).

> > > Are there known issues here? Does anyone have any advice for running
> > > 
> > > this/them down? Are there workarounds? I don't know what next steps to
> > > take.
> > 
> > Django's Oracle backend has some serious inefficiencies in its treatment
> > of numbers; with cx_Oracle 5.1.3 running on Linux or similar systems, they
> > can be patched away if you don't need high-precision decimals, but details
> > of that are probably out of scope for this mailing list. This is also 
> > unrelated to LOBs.
> 
> This sounds pretty promising. If it's out of scope, can we take it off
> list? Are there resources about it? I'm not sure what to google.
> 

Discussion from 2.5 years ago (I wanted a fix to be in Django, but was 
convinced it is inappropriate; I was not a Django team member back then)
https://groups.google.com/d/topic/django-developers/4BNkJyGez9A/discussion

In there, you'll find a link to a patch: 
https://github.com/django/django/pull/393

It is a bit of a mess: the PR includes unrelated things, there's some dead-
wrong bits in it (w.r.t Decimals, mostly), and at the time it didn't really do 
the trick (cx_Oracle 5.1.3 fixed that if you're on Linux/64bit).

HTH,
	Shai.