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

On Thursday 26 March 2015 02:54:36 Daniel Porter wrote:
> On Mar 25, 2015 3:08 PM, "Shai Berger" <shai-9mtU2oJkgntWk0Htik3J/[email protected]> 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.

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the 
conversation now. http://goparallel.sourceforge.net/