[Qgis-developer] Oracle layers - improving the speed

[Jonathan Moules]

Hi Jürgen,
  Thinking about it, the simplest solution is to just trust
MDSYS.USER_SDO_GEOM_METADATA and get everything from that where possible -
SRID and BBOX. The geometry-type it doesn't have, but is that strictly
necessary for the list?

This seems like the optimal solution for us; I'd suggest adding the
functionality behind the current "Use estimated table metadata" and/or
"only look in meta data table" checkboxes. In fact I expected "only look in
meta data table" to already do this.

I realise that this can be dangerous as Oracle doesn't keep it up to date,
however we do a good job of keeping it up to date ourselves (because if we
don't, ArcMap starts doing silly things). I'm sure other organisations are
similarly good at keeping their metadata up to date and could benefit too.

Does that seem like a plausible solution? A local-cache could build on that
I guess (store geometry types from pre-accessed tables?)
Cheers,
Jonathan


On 8 February 2014 17:45, Jürgen E. <jef at norbit.de> wrote:

> Hi Jonathan,
>
> On Tue, 04. Feb 2014 at 17:54:10 +0000, Jonathan Moules wrote:
> >    The native Oracle provider has been in QGIS for quite a while (a
> year?)
> >    and is generally quite good.
> >    However there are still two fundamental issues that we have with it,
> both
> >    as a result of the fact that Oracle doesn't store accurate spatial
> >    metadata:
>
> >    a) Whenever I "Connect" to the Oracle database, QGIS performs a scan
> of
> >    every table to get the metadata which can take almost 2 minutes (the
> >    results are cached by Oracle and subsequent "Connects" are faster, but
> >    only for an hour or two). This is using the "Only look in metadata
> table",
> >    "use estimated table metadata" and "only existing geomtetry types"
> >    checkboxes.
> >    See also - [1]http://hub.qgis.org/issues/8689
>
> That's to determine the geometry types and srids used in the tables.
>
> That the available metadata tables in Oracle are somewhat optional and you
> can't safely assume much about them is really unfortunate.
>
> We could do another metadata table that stores the outcome of those
> queries in
> the database.  That would require the user to have INSERT/UPDATE on that
> table
> and/or CREATE TABLE rights.
>
> We could also do a local user cache.  That would have the advantaage that
> it
> could be used for any database.
>
> >    b) When I add a specific Oracle table, QGIS then performs some checks
> >    against it (I believe trying to find the actual BBOX). This is fine
> for
> >    small tables, but for large tables it can take several minutes for the
> >    layer to get added - locking up QGIS in the interim - even if
> "render" is
> >    disabled!
>
> >    The issue becomes even more problematical when re-opening a project -
> QGIS
> >    then rescans all of the tables included in the project which can mean
> a
> >    several minute delay after opening the project.
>
> The extent could also be added to that cache.  But for this the extent
> could
> also be added as cachedExtent to the data source uri.  Parameters for srid
> and
> (geometry)type are already there and I just changed the postgres provider
> to
> depend on those without verifying them. Extent would be the next natural
> candidate and possibly speed up the loading of projects even more.
>
> But those are features which will have to wait until after the release.
>
>
> Jürgen
>
> --
> Jürgen E. Fischer         norBIT GmbH               Tel. +49-4931-918175-31
> Dipl.-Inf. (FH)           Rheinstraße 13            Fax. +49-4931-918175-50
> Software Engineer         D-26506 Norden
> http://www.norbit.de
> QGIS PSC member (RM)      Germany                      IRC: jef on FreeNode
>
> --
> norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
> Rheinstrasse 13, 26506 Norden
> GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502
>
> _______________________________________________
> Qgis-developer mailing list
> Qgis-developer at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain sensitive or protectively marked material up to RESTRICTED and 
should be handled accordingly. Unless you are the named addressee (or 
authorised to receive it for the addressee) you may not copy or use it, or 
disclose it to anyone else. If you have received this transmission in error 
please notify the sender immediately. All email traffic sent to or from us, 
including without limitation all GCSX traffic, may be subject to recording 
and/or monitoring in accordance with relevant legislation.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20140210/079e4eaf/attachment.html>