[Qgis-user] QGIS and Oracle native connection

Thu Jun 27 01:23:01 PDT 2013

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<qgis-user-bounces at lists.osgeo.org>
>> [mailto:qgis-user-bounces@**lists.osgeo.org<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/**
>> 5c41496d5951a49c852562b5004f3a**44/**fcb3edc86ce9460b80256ae7004ee5**97<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<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/**
>> 5c41496d5951a49c852562b5004f3a**44/**fcb3edc86ce9460b80256ae7004ee5**97<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 <Qgis-user at lists.osgeo.org>
>> [7] http://lists.osgeo.org/**mailman/listinfo/qgis-user<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<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/b569087d/attachment.html>