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

[Daniel P. <dan...@gm...>]

On Mar 25, 2015 3:08 PM, "Shai Berger" <sh...@pl...> wrote:
>
> 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.

Harumph.

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


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


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


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


>
> 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/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users