[Qgis-user] QGIS and Oracle native connection
Jonathan Moules
jonathanmoules at warwickshire.gov.uk
Thu Jul 4 07:07:44 PDT 2013
Hi Pierre,
Oracle stores its own metadata into mdsys.all_sdo_geom_metadata table.****
>
> I think Oracle provider uses this table, but I would like to know if it’s
> true ?
>
Yes it does if you tick the checkbox for "Only Look in meta data table",
otherwise QGIS will scan the entire database.
Advantage - It's infinitely faster using the Oracle metadata table.
Disadvantage - you need to keep it up to date; Oracle doesn't do it
natively.
Jonathan
On 2 July 2013 08:32, PIERRE Sylvain <sylvain.pierre at cg67.fr> wrote:
> Hi all,****
>
> I’m now going deeper into testing :****
>
> I load some big table (200 000 or 300 000 rows with polygon geomtry) from
> our coprorate DB.****
>
> I compare time loading with same operation in Mapinfo.****
>
> Loading takes little more time than in Mapinfo, but panning zooming are
> slightly slower with Mapinfo than QGis.****
>
> For the first point Mapinfo uses a metadata table (MAPCATALOG) which
> stores layer’s bounding box.****
>
> Oracle stores its own metadata into mdsys.all_sdo_geom_metadata table.****
>
> I think Oracle provider uses this table, but I would like to know if it’s
> true ?****
>
> Or is there any other action from Qgis api before loading that makes this
> difference ?****
>
> ** **
>
> Thanks****
>
> ** **
>
> ** **
>
> ** **
>
> *→* *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***
>
> [image: Description : Description :
> \\dsi7085103\c$\Users\samuel.guigon\Pictures\CG67\logo_CG67+www_coul.jpg]<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****
>
> ** **
>
> ** **
>
> *De :* qgis-user-bounces at lists.osgeo.org [mailto:
> qgis-user-bounces at lists.osgeo.org] *De la part de* PIERRE Sylvain
> *Envoyé :* jeudi 27 juin 2013 11:24
> *À :* kimaidou; Andreas Neumann
> *Cc :* qgis-user
> *Objet :* Re: [Qgis-user] QGIS and Oracle native connection****
>
> ** **
>
> Hi,****
>
> ** **
>
> Yes !****
>
> ** **
>
> Unlike Oracle DB manager which, each time you run it, re-scan all schemas,
> the browse panel scan only one time.****
>
> ** **
>
> Thanks a lot !****
>
> ** **
>
> ** **
>
> ** **
>
> *→* *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***
>
> [image: Description : Description :
> \\dsi7085103\c$\Users\samuel.guigon\Pictures\CG67\logo_CG67+www_coul.jpg]<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****
>
> ** **
>
> ** **
>
> *De :* qgis-user-bounces at lists.osgeo.org [mailto:
> qgis-user-bounces at lists.osgeo.org] *De la part de* kimaidou
>
> *Envoyé :* jeudi 27 juin 2013 10:23
> *À :* Andreas Neumann
> *Cc :* qgis-user
> *Objet :* Re: [Qgis-user] QGIS and Oracle native connection****
>
> ** **
>
> Hi
>
> A suggestion : have you tried to use the Browser pannel ( in french : Menu
> Vue > Panneaux > Parcourir) or the DbManager to access the data ? With
> these 2 tools, you can very quickly navigate through databases, schemas and
> tables, without the need to wait for QGIS to scan them all.****
>
> Regards****
>
> Michael****
>
> ** **
>
> 2013/6/26 Andreas Neumann <a.neumann at carto.net>****
>
> Hi,
>
> I think this would be a very useful addition also for other database
> providers (Postgis, SQL server, etc.)
>
> The problem is that we are in feature freeze now. Only bugfixes allowed at
> this time. New features (like this two-step scanning will have to wait for
> QGIS 2.0x or 2.1.
>
> Andreas****
>
>
>
> On Wed, 26 Jun 2013 09:38:38 +0200, PIERRE Sylvain wrote:****
>
> 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
>
> [1]****
>
>
>
> 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 [2]
>
> 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****
>
> re isn't any primary key) on
> the client side.
>
> Yes and no. Each table has a column with a unique number that is set****
>
> 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
> ****
>
> [3]
>
> Jonathan
>
> On 14 May 2013 10:31, Jürgen E. 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 [5]****
>
>
> 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 [6]
> http://lists.osgeo.org/mailman/listinfo/qgis-user [7]****
>
>
>
> 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.****
>
> Links:
> ------
> [1] http://www.bas-rhin.fr/
> [2] mailto:sylvain.pierre at cg67.fr
> [3]
>
>
> http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597
> [4] mailto:jef at norbit.de
> [5] http://www.norbit.de
> [6] mailto:Qgis-user at lists.osgeo.org
> [7] http://lists.osgeo.org/mailman/listinfo/qgis-user****
>
>
> --
> --
> Andreas Neumann
> Böschacherstrasse 10A
> 8624 Grüt (Gossau ZH)
> Switzerland****
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user****
>
> ** **
>
> _______________________________________________
> Qgis-user mailing list
> 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/20130704/ee1f9531/attachment.html>
More information about the Qgis-user
mailing list