Re: [Qgis-user] QGIS and Oracle native connection
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.pie...@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.pie...@cg67.fr ** ** ** ** *De :* qgis-user-boun...@lists.osgeo.org [mailto: qgis-user-boun...@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.pie...@cg67.fr ** ** ** ** *De :* qgis-user-boun...@lists.osgeo.org [mailto: qgis-user-boun...@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.neum...@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
Re: [Qgis-user] QGIS and Oracle native connection
Done http://hub.qgis.org/issues/8190 -Message d'origine- De : HAUBOURG [mailto:regis.haubo...@eau-adour-garonne.fr] Envoyé : jeudi 27 juin 2013 13:25 À : PIERRE Sylvain; 'qgis-user@lists.osgeo.org' Objet : RE: [Qgis-user] QGIS and Oracle native connection Hi, Just create a osgeo account and you then have a login for all osgeo tools : http://www.osgeo.org/osgeo_userid you will have to log then in hu.qgis.org to be able to : 1- search for existing feature request or bugs, just to check if no one already submitted something related 2- create your request 3- just put a reminder with the id of the feature/bug request in the mail list discussion (or it's nabble version ) Cheers, Régis Cordialement, Régis Haubourg Régis Haubourg Administrateur de données Géographiques Département des Systèmes d'Information (DCSI) Agence de l'eau Adour Garonne 90 rue du Férétra, 31078 Toulouse Cedex4 Tél: 05 61 36 82 58 Mail: regis.haubo...@eau-adour-garonne.fr http://www.eau-adour-garonne.fr Accédez aux données sur l'eau : http://adour-garonne.eaufrance.fr/ -Message d'origine- De : PIERRE Sylvain [mailto:sylvain.pie...@cg67.fr] Envoyé : jeudi 27 juin 2013 12:51 À : HAUBOURG; qgis-user@lists.osgeo.org Objet : RE: [Qgis-user] QGIS and Oracle native connection Hello Régis, I never put a request for bugtracking, what's the way? -Message d'origine- De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user- boun...@lists.osgeo.org] De la part de Régis Haubourg Envoyé : jeudi 27 juin 2013 10:23 À : qgis-user@lists.osgeo.org Objet : Re: [Qgis-user] QGIS and Oracle native connection Hi Sylvain, +1 with your feature request, could you create it in the bugtracker. +Like Andreas, I wish we expand that feature request to all db dealing with schemas. As a workaround waiting for 2.1, users here never have to explore db structure with QGIS, since with provide access to data with a dropdown menu (using layers menu from project plugin). Only admins are bothered by that issue. Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS- and-Oracle-native-connection-tp5050656p5062489.html Sent from the Quantum GIS - User mailing list archive at Nabble.com. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Hi Sylvain, +1 with your feature request, could you create it in the bugtracker. Like Andreas, I wish we expand that feature request to all db dealing with schemas. As a workaround waiting for 2.1, users here never have to explore db structure with QGIS, since with provide access to data with a dropdown menu (using layers menu from project plugin). Only admins are bothered by that issue. Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-native-connection-tp5050656p5062489.html Sent from the Quantum GIS - User mailing list archive at Nabble.com. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
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.neum...@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.pie...@cg67.fr [2] DE : qgis-user-bounces@lists.osgeo.**orgqgis-user-boun...@lists.osgeo.org [mailto:qgis-user-bounces@**lists.osgeo.orgqgis-user-boun...@lists.osgeo.org] DE LA PART DE Jonathan Moules ENVOYÉ : mardi 14 mai 2013 18:05 À : Jürgen E.; qgis-user@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**97http://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
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 [cid:image001.jpg@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.pie...@cg67.frmailto:sylvain.pie...@cg67.fr De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@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.neum...@carto.netmailto:a.neum...@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 88tel:%2B33%203%2088%2076%2068%2088 – mobile : Fax : 03 88 76 68 71 Email : sylvain.pie...@cg67.frmailto:sylvain.pie...@cg67.fr [2] DE : qgis-user-boun...@lists.osgeo.orgmailto:qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@lists.osgeo.orgmailto:qgis-user-boun...@lists.osgeo.org] DE LA PART DE Jonathan Moules ENVOYÉ : mardi 14 mai 2013 18:05 À : Jürgen E.; qgis-user@lists.osgeo.orgmailto:qgis-user@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
Re: [Qgis-user] QGIS and Oracle native connection
Hello Régis, I never put a request for bugtracking, what's the way? -Message d'origine- De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@lists.osgeo.org] De la part de Régis Haubourg Envoyé : jeudi 27 juin 2013 10:23 À : qgis-user@lists.osgeo.org Objet : Re: [Qgis-user] QGIS and Oracle native connection Hi Sylvain, +1 with your feature request, could you create it in the bugtracker. +Like Andreas, I wish we expand that feature request to all db dealing with schemas. As a workaround waiting for 2.1, users here never have to explore db structure with QGIS, since with provide access to data with a dropdown menu (using layers menu from project plugin). Only admins are bothered by that issue. Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-native-connection-tp5050656p5062489.html Sent from the Quantum GIS - User mailing list archive at Nabble.com. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Hi Pierre, Everything is written here: http://hub.qgis.org/wiki/quantum-gis/Bugreports You need an OSGeo login (useful for all OpenSource GIS projects as well). Andreas On Thu, 27 Jun 2013 12:50:41 +0200, PIERRE Sylvain wrote: Hello Régis, I never put a request for bugtracking, what's the way? -Message d'origine- De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@lists.osgeo.org] De la part de Régis Haubourg Envoyé : jeudi 27 juin 2013 10:23 À : qgis-user@lists.osgeo.org Objet : Re: [Qgis-user] QGIS and Oracle native connection Hi Sylvain, +1 with your feature request, could you create it in the bugtracker. +Like Andreas, I wish we expand that feature request to all db dealing with schemas. As a workaround waiting for 2.1, users here never have to explore db structure with QGIS, since with provide access to data with a dropdown menu (using layers menu from project plugin). Only admins are bothered by that issue. Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-native-connection-tp5050656p5062489.html Sent from the Quantum GIS - User mailing list archive at Nabble.com. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org 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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Hi, Just create a osgeo account and you then have a login for all osgeo tools : http://www.osgeo.org/osgeo_userid you will have to log then in hu.qgis.org to be able to : 1- search for existing feature request or bugs, just to check if no one already submitted something related 2- create your request 3- just put a reminder with the id of the feature/bug request in the mail list discussion (or it's nabble version ) Cheers, Régis Cordialement, Régis Haubourg Régis Haubourg Administrateur de données Géographiques Département des Systèmes d'Information (DCSI) Agence de l'eau Adour Garonne 90 rue du Férétra, 31078 Toulouse Cedex4 Tél: 05 61 36 82 58 Mail: regis.haubo...@eau-adour-garonne.fr http://www.eau-adour-garonne.fr Accédez aux données sur l'eau : http://adour-garonne.eaufrance.fr/ -Message d'origine- De : PIERRE Sylvain [mailto:sylvain.pie...@cg67.fr] Envoyé : jeudi 27 juin 2013 12:51 À : HAUBOURG; qgis-user@lists.osgeo.org Objet : RE: [Qgis-user] QGIS and Oracle native connection Hello Régis, I never put a request for bugtracking, what's the way? -Message d'origine- De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user- boun...@lists.osgeo.org] De la part de Régis Haubourg Envoyé : jeudi 27 juin 2013 10:23 À : qgis-user@lists.osgeo.org Objet : Re: [Qgis-user] QGIS and Oracle native connection Hi Sylvain, +1 with your feature request, could you create it in the bugtracker. +Like Andreas, I wish we expand that feature request to all db dealing with schemas. As a workaround waiting for 2.1, users here never have to explore db structure with QGIS, since with provide access to data with a dropdown menu (using layers menu from project plugin). Only admins are bothered by that issue. Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS- and-Oracle-native-connection-tp5050656p5062489.html Sent from the Quantum GIS - User mailing list archive at Nabble.com. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
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 [cid:image003.jpg@01CE7250.EFEE8720]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.pie...@cg67.frmailto:sylvain.pie...@cg67.fr De : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@lists.osgeo.org] De la part de Jonathan Moules Envoyé : mardi 14 mai 2013 18:05 À : Jürgen E.; qgis-user@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 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. j...@norbit.demailto:j...@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
Re: [Qgis-user] QGIS and Oracle native connection
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.pie...@cg67.fr [2] DE : qgis-user-boun...@lists.osgeo.org [mailto:qgis-user-boun...@lists.osgeo.org] DE LA PART DE Jonathan Moules ENVOYÉ : mardi 14 mai 2013 18:05 À : Jürgen E.; qgis-user@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
Re: [Qgis-user] QGIS and Oracle native connection
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 13Fax. +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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
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 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. j...@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 13Fax. +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@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
Re: [Qgis-user] QGIS and Oracle native connection
Hi Jürgen, Thanks again for this. I've now had the chance to do a little playing with it. And have some feedback below (along with the already-filed tickets). My primary observation is that its slow I'm afraid. - 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!). Does QGIS need to do all of the checks it does when actually listing the tables? Could it not do them when they are selected for adding instead? We have half a dozen other GIS program's that connect to Oracle and none of them are even close to being this slow to produce the table list. 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. Though it does seem to get a little faster on subsequent requests, even restarting QGIS, but that's probably Oracle caching. Note: I'm aware ArcGIS uses its ArcSDE tables and MapInfo uses its own metadata table so its not an /entirely/ fair comparison. - If I click add for a polygon dataset with ~7 million features (fully indexed), it takes a full 90 seconds before it is even added to QGIS (during this time QGIS is unresponsive)! This is with rendering turned off. The 20 million polyline dataset takes 140 seconds to add, again, with rendering off! This is less noticeable with smaller datasets. - Panning. Again, fine with smaller datasets, but the larger ones cause issues. The larger dataset (20 million features) is MasterMap Topographic Line, the large scale mapping for the UK. If I view it at 1:5,000 (the furthest out we use it), there are in a detailed urban scene about 40,000 features on-screen. In order of speed: QGIS: ~4-16 seconds (no styling)) GeoServer WMS single tile : ~15 seconds (via Openlayers, categorised styling) MapInfo: ~9 seconds (includes categorised styling) ArcGIS: ~4 seconds (includes categorised styling) GeoServer WMS 256^2 tiled (no GWC cache): ~3 seconds (via Openlayers, categorised styled) The panning did get faster, I guess because Oracle started picking up on my pans. --- The short version is - is there anything I can do to speed this up? Are these the expected speeds for these sorts of datasets/numbers? I'm happy to do further testing if desired. Jonathan On 9 May 2013 16:13, Jürgen E. j...@norbit.de wrote: Hi Jonathan, On Wed, 08. May 2013 at 11:24:08 +0100, Jonathan Moules wrote: Anyone? I'd love to try this and test it, but can't if I have no idea how to even connect. Is there any documentation for it anywhere? https://github.com/qgis/Quantum-GIS/blob/master/resources/context_help/QgsOracleNewConnection-en_US Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13Fax. +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@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. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Hi Jonathan, On Wed, 08. May 2013 at 11:24:08 +0100, Jonathan Moules wrote: Anyone? I'd love to try this and test it, but can't if I have no idea how to even connect. Is there any documentation for it anywhere? https://github.com/qgis/Quantum-GIS/blob/master/resources/context_help/QgsOracleNewConnection-en_US Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13Fax. +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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Thanks Jurgen. Google failed to find that, although given it only has a timestamp of two hours ago.. ;-) I'll see how I get on tomorrow. Thanks again, Jonathan On 9 May 2013 16:13, Jürgen E. j...@norbit.de wrote: Hi Jonathan, On Wed, 08. May 2013 at 11:24:08 +0100, Jonathan Moules wrote: Anyone? I'd love to try this and test it, but can't if I have no idea how to even connect. Is there any documentation for it anywhere? https://github.com/qgis/Quantum-GIS/blob/master/resources/context_help/QgsOracleNewConnection-en_US Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13Fax. +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@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. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] QGIS and Oracle native connection
Bump. Anyone? I'd love to try this and test it, but can't if I have no idea how to even connect. Is there any documentation for it anywhere? Thanks, Jonathan On 1 May 2013 15:35, Jonathan Moules jonathanmou...@warwickshire.gov.ukwrote: Hi List, I've got QGIS 1.9.0 based on a weekly nightly and it includes the new Oracle Spatial layer connection. Unfortunately the help is empty and I can't figure out what I should populate my fields with. There are 6 fields, Four are self explanatory, but I can't figure out the other two. Name - User-friendly string for me. Database - ??? Host -??? Port - 1521 Username - I know this. Password - I know this. My issue is with the Database and Host settings. In Oracle SQL Developer (from the same machine) I have the following that work: SID: gistest Hostname: 10.1.45.34 But when I try this combination in QGIS, I get an ORA-12514 error (TNS:listener does not currently know of service requested in connect descriptor). Any suggestions? Also, what do the bottom four checkboxes do? (The top two are self explanatory). Thanks, Jonathan -- 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. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] QGIS and Oracle native connection
Hi List, I've got QGIS 1.9.0 based on a weekly nightly and it includes the new Oracle Spatial layer connection. Unfortunately the help is empty and I can't figure out what I should populate my fields with. There are 6 fields, Four are self explanatory, but I can't figure out the other two. Name - User-friendly string for me. Database - ??? Host -??? Port - 1521 Username - I know this. Password - I know this. My issue is with the Database and Host settings. In Oracle SQL Developer (from the same machine) I have the following that work: SID: gistest Hostname: 10.1.45.34 But when I try this combination in QGIS, I get an ORA-12514 error (TNS:listener does not currently know of service requested in connect descriptor). Any suggestions? Also, what do the bottom four checkboxes do? (The top two are self explanatory). Thanks, Jonathan 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. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
