[Qgis-user] QGIS and Oracle native connection
PIERRE Sylvain
sylvain.pierre at cg67.fr
Thu Jun 27 02:23:31 PDT 2013
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
[cid:image001.jpg at 01CE7328.C1300980]<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 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<mailto: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<tel:%2B33%203%2088%2076%2068%2088> – mobile :
Fax : 03 88 76 68 71
Email : sylvain.pierre at cg67.fr<mailto:sylvain.pierre at cg67.fr> [2]
DE : qgis-user-bounces at lists.osgeo.org<mailto:qgis-user-bounces at lists.osgeo.org>
[mailto: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<mailto: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<mailto: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<mailto:sylvain.pierre at cg67.fr>
[3]
http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597
[4] mailto:jef at norbit.de<mailto:jef at norbit.de>
[5] http://www.norbit.de
[6] mailto:Qgis-user at lists.osgeo.org<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<mailto:Qgis-user at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130627/9d8b5a13/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 3523 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130627/9d8b5a13/attachment.jpg>
More information about the Qgis-user
mailing list