[Qgis-user] QGIS and Oracle native connection

Wed Jun 26 00:38:38 PDT 2013

Hi everybody,

I’ve installed Qgis-dev yesterday in order to test Oracle connection.
I work in a french local government. 95% of our data  are stored into Oracle.
So this new connection looks very interesting for us.
The main things is definitively slowness at scanning DB.
We have more than 20 schema and users can’t wait that Qgis scans all schemas until they can load their data.
I know you can stop scan BUT if unfortunatly you are interest in the last one schema, you have to wait…
Is it possible to only list all schema in the UI and only scan data on demand when user select a schema ?

Sylvain

→  Sylvain PIERRE
         Ingénieur Géographe
         Adjoint au chef du service
         Direction de l’Agriculture, de l’Espace Rural et de l’Environnement
         Service Administration Générale
       Conseil Général du Bas-Rhin

[cid:image003.jpg at 01CE7250.EFEE8720]<http://www.bas-rhin.fr/>

         Passerelle 67
         20 rue Livio / 67000 Strasbourg
         Tél : +33 3 88 76 68 88 – mobile :
         Fax : 03 88 76 68 71
         Email : sylvain.pierre at cg67.fr<mailto:sylvain.pierre at cg67.fr>

De : qgis-user-bounces at lists.osgeo.org [mailto:qgis-user-bounces at lists.osgeo.org] De la part de Jonathan Moules
Envoyé : mardi 14 mai 2013 18:05
À : Jürgen E.; qgis-user at lists.osgeo.org
Objet : Re: [Qgis-user] QGIS and Oracle native connection

Hi Jürgen,
I've updated to the newest nightly build (1.9.0-19 - yesterday) and note it has a couple of fixes. Its also faster, in part because its not listing the recycling tables now.
We have 551 rows in our all_sdo_geom_metadata table - seems we've had a clean-up.

Can you work out which queries take particularly long (I added some progress messages recently)?

I'm now using "only look in meta data table", "use estimated table metadata" and "only existing geometry types" as my defaults.

In the bottom left there's something that says "Scanning column ... " and then shows the column. The speed this cycles through tables seems to vary - it goes blur-fast if I've just done it a minute ago (despite restarting QGIS), so I guess in those cases Oracle caches - it only takes < 5 seconds.
But if I do it for the first time, some of them take a significant time, though it doesn't seem to be entirely related to their size (the vast majority of the tables are only in the thousands of rows or smaller - the millions are the exception (probably 5)).

You can "stop" the detection and then pick what's already there.
I completely missed the fact that "connect" turns into stop. Even took me a minute after reading your email to find it.

Do you have numeric primary keys?  Otherwise QGIS must build a map that assigns
numeric keys to the primary keys (or ROWID if there isn't any primary key) on
the client side.

Yes and no. Each table has a column with a unique number that is set by ArcSDE to be unique and not-nullable, but its not set as a primary key in Oracle. MapInfo and ArcSDE use this column as their index (MapInfo because its called MI_PRINX, and ArcSDE because we tell it to when we register the table).
I don't know how normal this setup is, but the only other thing that's ever hinted at wanting an explicit primary key is GeoServer, and then only as a "WARN" event in the logs.

A thought - if it can't find a primary key, how about testing to see if there's a column called MI_PRINX? Anywhere with MapInfo will have it. http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597

Jonathan

On 14 May 2013 10:31, Jürgen E. <jef at norbit.de<mailto:jef at norbit.de>> wrote:
Hi Jonathan,

On Mon, 13. May 2013 at 13:06:49 +0100, Jonathan Moules wrote:
> The first and most obvious thing is that it's incredibly slow to list the
> tables. I don't know how many tables were used in the test setup, but we
> have over a thousand spatial tables ranging from one row to 20 million on
> an Oracle Locator 10g setup that has about 50 concurrent users.
> In the best case scenario ("Only look in meta data table" and "Use
> estimated table metadata" both checked), it still takes a full two minutes
> to list all of the tables. If I don't have those checkboxes checked it
> takes much longer (scanning the table that has ~20million features alone
> takes about a minute!).
Can you work out which queries take particularly long (I added some progress
messages recently)?

> Does QGIS need to do all of the checks it does when actually listing the
> tables?
Well, QGIS needs to know which geometry types are present.  And that might take
long to determine.   It might be possible to do that lazy - ie. on demand
(introduce another level to the tree where the types in the geometry column
are.

> Its also impossible to "add" a table while the list is being generated so
> the user has to wait until its finished before being able to continue.
You can "stop" the detection and then pick what's already there.

> Panning. Again, fine with smaller datasets, but the larger ones cause
> issues.
Do you have numeric primary keys?  Otherwise QGIS must build a map that assigns
numeric keys to the primary keys (or ROWID if there isn't any primary key) 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
committ(ed|ing) to Quantum GIS                         IRC: jef on FreeNode

--
norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502

_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org<mailto:Qgis-user at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/qgis-user

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-user/attachments/20130626/8be744b7/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 3518 bytes
Desc: image003.jpg
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130626/8be744b7/attachment.jpg>