[Qgis-developer] Oracle provider slow for large datasets - fetches all features every time

[Jürgen E. Fischer]

Hi Steve,

On Tue, 06. Oct 2015 at 17:02:19 +0100, Steve Pritchard wrote:
> When I open a QGIS project that has a layer using the Oracle provider, the
> provider queries for all of the features even though the display area only
> contains a few features:
 
> SELECT
> "GEOM","LOC_ID","LOC_LABEL","NAME","GRIDREF","LATITUDE","LONGITUDE","COUNTRY_CODE","IN_USE","REGION","PROTOCOL_ID","GEOG_REGION","COASTAL","PRIORITY","EASTING","NORTHING","EXTANT","ONLINE_REGION","BOUNDARY","ZOOMLEV","SITE_TYPE","PRIMARY_INDEX_REG","CONTEXT_INDEX_REG","AREA_INTERTIDAL","AREA_SUBTIDAL","AREA_NONTIDAL","ANCESTOR_LOC_ID","ACTIVE","COMMENTS","GEOM_EDITED_DT"
> FROM "BTOWATCH"."LOC_AUX_GEO_WEBS_VW" "featureRequest" WHERE
> mod("featureRequest"."GEOM".sdo_gtype,100) IN (3,7) AND (protocol_id =
> 'WEBSC')

What messages do you get in the message log?

I suppose it's a view and sdo_filter fails on it.  Because sdo_filter requires
a spatial index and qgis probably doesn't find it on that view and tries one
query with sdo_filter that fails and therefore doesn't use it later.  Another
problem might be the SRID.

And without sdo_filter QGIS only resort is to retrieve all and filter them on
the client side.


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 release manager (PSC)  Germany                    IRC: jef on FreeNode                         
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 827 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20151007/6a9e6e29/attachment.pgp>