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

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

Hi Daniel,

On Wednesday 25 March 2015 19:35:52 Daniel Porter wrote:
> 
> The queries don't seem to be the problem. The queries complete pretty
> quickly. The problem seems to be taking the query results and instantiating
> objects (through django's ORM). All of the results are slow, but the
> egregious ones are ones that return NCLOB. It takes ~21 seconds to return
> 6k records as django models (just grabbing everything: essentially SELECT *
> FROM ...). If you exclude the NCLOB columns, it reduces to a (still kind of
> unreasonable) 1.5 seconds.
> 

The problem is that retrieving a single LOB takes a database roundtrip; as far 
as I know, this is an Oracle issue, there's nothing Django or cx_oracle can do 
about it.

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; 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.

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.

> 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.

HTH,
	Shai