[Qgis-developer] Oracle layers - improving the speed

[Jürgen E. Fischer]

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