<div dir="ltr"><div>Hi <br><br>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.<br>
<br></div><div>Regards<br></div><div>Michael<br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/6/26 Andreas Neumann <span dir="ltr"><<a href="mailto:a.neumann@carto.net" target="_blank">a.neumann@carto.net</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
I think this would be a very useful addition also for other database providers (Postgis, SQL server, etc.)<br>
<br>
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.<br>
<br>
Andreas<div class="im"><br>
<br>
On Wed, 26 Jun 2013 09:38:38 +0200, PIERRE Sylvain wrote:<br>
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">
Hi everybody,<br>
<br>
I’ve installed Qgis-dev yesterday in order to test Oracle<br>
connection.<br>
<br>
I work in a french local government. 95% of our data are stored into<br>
Oracle.<br>
<br>
So this new connection looks very interesting for us.<br>
<br>
The main things is definitively slowness at scanning DB.<br>
<br>
We have more than 20 schema and users can’t wait that Qgis scans all<br>
schemas until they can load their data.<br>
<br>
I know you can stop scan BUT if unfortunatly you are interest in the<br>
last one schema, you have to wait…<br>
<br>
Is it possible to only list all schema in the UI and only scan data on<br>
demand when user select a schema ?<br>
<br>
Sylvain<br>
<br></div>
→ SYLVAIN PIERRE<div class="im"><br>
<br>
Ingénieur Géographe<br>
<br>
Adjoint au chef du service<br>
<br>
Direction de l’Agriculture, de l’Espace Rural et<br>
de l’Environnement<br>
<br>
Service Administration Générale<br>
<br></div>
CONSEIL GÉNÉRAL DU BAS-RHIN<br>
<br>
[1]<div class="im"><br>
<br>
Passerelle 67<br>
20 rue Livio / 67000 Strasbourg<br>
Tél : <a href="tel:%2B33%203%2088%2076%2068%2088" value="+33388766888" target="_blank">+33 3 88 76 68 88</a> – mobile :<br>
Fax : 03 88 76 68 71<br></div>
Email : <a href="mailto:sylvain.pierre@cg67.fr" target="_blank">sylvain.pierre@cg67.fr</a> [2]<br>
<br>
DE : <a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@lists.osgeo.<u></u>org</a><br>
[mailto:<a href="mailto:qgis-user-bounces@lists.osgeo.org" target="_blank">qgis-user-bounces@<u></u>lists.osgeo.org</a>] DE LA PART DE Jonathan<br>
Moules<br>
ENVOYÉ : mardi 14 mai 2013 18:05<br>
À : Jürgen E.; <a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a><br>
OBJET : Re: [Qgis-user] QGIS and Oracle native connection<div class="im"><br>
<br>
Hi Jürgen,<br>
<br>
I've updated to the newest nightly build (1.9.0-19 - yesterday) and<br>
note it has a couple of fixes. Its also faster, in part because its<br>
not listing the recycling tables now.<br>
<br>
We have 551 rows in our all_sdo_geom_metadata table - seems we've had<br>
a clean-up.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Can you work out which queries take particularly long (I added some<br>
progress messages recently)?<br>
</blockquote>
<br>
I'm now using "only look in meta data table", "use estimated table<br>
metadata" and "only existing geometry types" as my defaults.<br>
<br>
In the bottom left there's something that says "Scanning column ... "<br>
and then shows the column. The speed this cycles through tables seems<br>
to vary - it goes blur-fast if I've just done it a minute ago (despite<br>
restarting QGIS), so I guess in those cases Oracle caches - it only<br>
takes < 5 seconds.<br>
<br>
But if I do it for the first time, some of them take a significant<br>
time, though it doesn't seem to be entirely related to their size (the<br>
vast majority of the tables are only in the thousands of rows or<br>
smaller - the millions are the exception (probably 5)).<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
You can "stop" the detection and then pick what's already there.<br>
</blockquote>
<br>
I completely missed the fact that "connect" turns into stop. Even took<br>
me a minute after reading your email to find it.<br>
<br>
Do you have<br>
<br>
</div><div class="im"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
re isn't any primary key) on<br>
the client side.<br>
<br>
Yes and no. Each table has a column with a unique number that is set<br>
</blockquote></div>
be unique and not-nullable, but its not set as a primary key in<div class="im"><br>
Oracle. MapInfo and ArcSDE use this column as their index (MapInfo<br>
because its called MI_PRINX, and ArcSDE because we tell it to when we<br>
register the table).<br>
<br>
I don't know how normal this setup is, but the only other thing that's<br>
ever hinted at wanting an explicit primary key is GeoServer, and then<br>
only as a "WARN" event in the logs.<br>
<br>
A thought - if it can't find a primary key, how about testing to see<br>
if there's a column called MI_PRINX? Anywhere with MapInfo will have<br>
it.<br>
<br>
<a href="http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597" target="_blank">http://testdrive.mapinfo.com/<u></u>TECHSUPP/MIPROD.NSF/<u></u>5c41496d5951a49c852562b5004f3a<u></u>44/<u></u>fcb3edc86ce9460b80256ae7004ee5<u></u>97</a><br>
</div>
[3]<br>
<br>
Jonathan<br>
<br>
On 14 May 2013 10:31, Jürgen E. wrote:<div><div class="h5"><br>
<br>
Hi Jonathan,<br>
<br>
On Mon, 13. May 2013 at 13:06:49 +0100, Jonathan Moules wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
The first and most obvious thing is that it's incredibly slow to<br>
</blockquote>
list the<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
tables. I don't know how many tables were used in the test setup,<br>
</blockquote>
but we<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
have over a thousand spatial tables ranging from one row to 20<br>
</blockquote>
million on<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
an Oracle Locator 10g setup that has about 50 concurrent users.<br>
In the best case scenario ("Only look in meta data table" and "Use<br>
estimated table metadata" both checked), it still takes a full two<br>
</blockquote>
minutes<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
to list all of the tables. If I don't have those checkboxes checked<br>
</blockquote>
it<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
takes much longer (scanning the table that has ~20million features<br>
</blockquote>
alone<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
takes about a minute!).<br>
</blockquote>
<br>
Can you work out which queries take particularly long (I added some<br>
progress<br>
messages recently)?<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Does QGIS need to do all of the checks it does when actually listing<br>
</blockquote>
the<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
tables?<br>
</blockquote>
<br>
Well, QGIS needs to know which geometry types are present. And that<br>
might take<br>
long to determine. It might be possible to do that lazy - ie. on<br>
demand<br>
(introduce another level to the tree where the types in the geometry<br>
column<br>
are.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Its also impossible to "add" a table while the list is being<br>
</blockquote>
generated so<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
the user has to wait until its finished before being able to<br>
</blockquote>
continue.<br>
<br>
You can "stop" the detection and then pick what's already there.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Panning. Again, fine with smaller datasets, but the larger ones<br>
</blockquote>
cause<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
issues.<br>
</blockquote>
<br>
Do you have numeric primary keys? Otherwise QGIS must build a map that<br>
assigns<br>
numeric keys to the primary keys (or ROWID if there isn't any primary<br>
key) on<br>
the client side.<br>
<br>
Jürgen<br>
<br>
--<br>
Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31<br>
Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50<br></div></div>
Software Engineer D-26506 Norden <a href="http://www.norbit.de" target="_blank">http://www.norbit.de</a> [5]<div class="im"><br>
committ(ed|ing) to Quantum GIS IRC: jef on FreeNode<br>
<br>
--<br>
norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme<br>
mbH<br>
Rheinstrasse 13, 26506 Norden<br>
GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502<br>
<br>
______________________________<u></u>_________________<br>
Qgis-user mailing list<br>
</div><a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a> [6]<br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/qgis-user</a> [7]<div class="im"><br>
<br>
This transmission is intended for the named addressee(s) only and may<br>
contain sensitive or protectively marked material up to RESTRICTED and<br>
should be handled accordingly. Unless you are the named addressee (or<br>
authorised to receive it for the addressee) you may not copy or use<br>
it, or disclose it to anyone else. If you have received this<br>
transmission in error please notify the sender immediately. All email<br>
traffic sent to or from us, including without limitation all GCSX<br>
traffic, may be subject to recording and/or monitoring in accordance<br>
with relevant legislation.<br>
<br></div>
Links:<br>
------<br>
[1] <a href="http://www.bas-rhin.fr/" target="_blank">http://www.bas-rhin.fr/</a><br>
[2] mailto:<a href="mailto:sylvain.pierre@cg67.fr" target="_blank">sylvain.pierre@cg67.fr</a><br>
[3]<br>
<br>
<a href="http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597" target="_blank">http://testdrive.mapinfo.com/<u></u>TECHSUPP/MIPROD.NSF/<u></u>5c41496d5951a49c852562b5004f3a<u></u>44/<u></u>fcb3edc86ce9460b80256ae7004ee5<u></u>97</a><br>
[4] mailto:<a href="mailto:jef@norbit.de" target="_blank">jef@norbit.de</a><br>
[5] <a href="http://www.norbit.de" target="_blank">http://www.norbit.de</a><br>
[6] mailto:<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.<u></u>org</a><br>
[7] <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/qgis-user</a><span class="HOEnZb"><font color="#888888"><br>
</font></span></blockquote><span class="HOEnZb"><font color="#888888">
<br>
-- <br>
--<br>
Andreas Neumann<br>
Böschacherstrasse 10A<br>
8624 Grüt (Gossau ZH)<br>
Switzerland</font></span><div class="HOEnZb"><div class="h5"><br>
______________________________<u></u>_________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/qgis-user</a><br>
</div></div></blockquote></div><br></div>