[Qgis-user] QGIS and Oracle native connection

Tue May 14 09:04:55 PDT 2013

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> 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
> 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/20130514/129d5209/attachment.html>