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

On Mar 25, 2015 3:08 PM, "Shai Berger" <shai-9mtU2oJkgntWk0Htik3J/[email protected]> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

------------------------------------------------------------------------------
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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users