<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:BatangChe;
panose-1:2 3 6 9 0 1 1 1 1 1;}
@font-face
{font-family:"\@BatangChe";
panose-1:2 3 6 9 0 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Texte de bulles Car";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.TextedebullesCar
{mso-style-name:"Texte de bulles Car";
mso-style-priority:99;
mso-style-link:"Texte de bulles";
font-family:"Tahoma","sans-serif";
mso-fareast-language:FR;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=FR link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Hi everybody,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I’ve installed Qgis-dev yesterday in order to test Oracle connection.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I work in a french local government. 95% of our data are stored into Oracle.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>So this new connection looks very interesting for us. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>The main things is definitively slowness at scanning DB.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>We have more than 20 schema and users can’t wait that Qgis scans all schemas until they can load their data.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I know you can stop scan BUT if unfortunatly you are interest in the last one schema, you have to wait…<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Is it possible to only list all schema in the UI and only scan data on demand when user select a schema ?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Sylvain<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'><o:p> </o:p></span></p><table class=MsoNormalTable border=1 cellpadding=0 style='border:none;border-top:solid gray 1.0pt'><tr style='height:54.95pt'><td width=294 style='width:220.5pt;border:none;padding:1.5pt 1.5pt 1.5pt 1.5pt;height:54.95pt'><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt'><b><span lang=EN-US style='font-size:14.0pt;font-family:"Arial","sans-serif";color:#CC0066'>→</span></b><span lang=EN-US style='font-size:11.0pt;font-family:"Arial","sans-serif";color:#1F497D'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'>Sylvain PIERRE</span></b><b><span lang=EN-US style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#CC0066'><o:p></o:p></span></b></p><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt'><span lang=EN-US style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#CC0066'> </span><span lang=EN-US style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#1F497D'>Ingénieur Géographe<o:p></o:p></span></p><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt'><span lang=EN-US style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#1F497D'> Adjoint au chef du service<o:p></o:p></span></p><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt'><span lang=EN-US style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#1F497D'> Direction de l’Agriculture, de l’Espace Rural et de l’Environnement<o:p></o:p></span></p><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt'><span lang=EN-US style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#1F497D'> </span><span style='font-size:8.0pt;font-family:"Arial","sans-serif";color:#1F497D'>Service Administration Générale<o:p></o:p></span></p><p class=MsoNormal style='margin-left:21.3pt;text-indent:-21.3pt;line-height:115%'><span style='font-family:"Arial","sans-serif";color:navy'> </span><b><span style='font-size:8.0pt;line-height:115%;font-family:"Arial","sans-serif";color:#1F497D'>Conseil Général du Bas-Rhin</span></b><b><span style='font-size:8.0pt;line-height:115%;font-family:"Arial","sans-serif";color:navy'><o:p></o:p></span></b></p></td><td width=167 style='width:125.35pt;border:none;padding:1.4pt 1.4pt 1.4pt 1.4pt;height:54.95pt'><p class=MsoNormal align=center style='text-align:center;line-height:115%'><a href="http://www.bas-rhin.fr/"><span style='color:navy;text-decoration:none'><img border=0 width=152 height=72 id="Image_x0020_6" src="cid:image003.jpg@01CE7250.EFEE8720" alt="Description : Description : \\dsi7085103\c$\Users\samuel.guigon\Pictures\CG67\logo_CG67+www_coul.jpg"></span></a><span lang=EN-US style='font-size:8.0pt;line-height:115%;font-family:"Calibri","sans-serif";color:#1F497D'><o:p></o:p></span></p></td></tr><tr style='page-break-inside:avoid;height:54.95pt'><td width=463 colspan=2 style='width:347.35pt;border-top:solid gray 1.0pt;border-left:none;border-bottom:solid gray 1.0pt;border-right:none;padding:1.5pt 1.5pt 1.5pt 1.5pt;height:54.95pt'><p class=MsoNormal style='line-height:115%'><span style='font-size:8.0pt;line-height:115%;font-family:"Arial","sans-serif";color:gray'> </span><span lang=EN-US style='font-size:8.0pt;line-height:115%;font-family:"Arial","sans-serif";color:gray'>Passerelle 67 <br> 20 rue Livio / 67000 Strasbourg<br> Tél : +33 3 88 76 68 88 – mobile :<br> Fax : 03 88 76 68 71<br> Email : <a href="mailto:sylvain.pierre@cg67.fr">sylvain.pierre@cg67.fr</a><o:p></o:p></span></p></td></tr></table><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>De :</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> qgis-user-bounces@lists.osgeo.org [mailto:qgis-user-bounces@lists.osgeo.org] <b>De la part de</b> Jonathan Moules<br><b>Envoyé :</b> mardi 14 mai 2013 18:05<br><b>À :</b> Jürgen E.; qgis-user@lists.osgeo.org<br><b>Objet :</b> Re: [Qgis-user] QGIS and Oracle native connection<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hi Jürgen,<o:p></o:p></p><div><p class=MsoNormal>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.<o:p></o:p></p></div><div><p class=MsoNormal>We have 551 rows in our all_sdo_geom_metadata table - seems we've had a clean-up.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><p class=MsoNormal>Can you work out which queries take particularly long (I added some progress messages recently)?<o:p></o:p></p></blockquote><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I'm now using "only look in meta data table", "use estimated table metadata" and "only existing geometry types" as my defaults.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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.<o:p></o:p></p></div><div><p class=MsoNormal>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)).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><p class=MsoNormal>You can "stop" the detection and then pick what's already there.<o:p></o:p></p></blockquote><div><p class=MsoNormal>I completely missed the fact that "connect" turns into stop. Even took me a minute after reading your email to find it.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><p class=MsoNormal>Do you have numeric primary keys? Otherwise QGIS must build a map that assigns<br>numeric keys to the primary keys (or ROWID if there isn't any primary key) on<br>the client side.<o:p></o:p></p></blockquote><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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).<o:p></o:p></p></div><div><p class=MsoNormal>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.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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. <a href="http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597">http://testdrive.mapinfo.com/TECHSUPP/MIPROD.NSF/5c41496d5951a49c852562b5004f3a44/fcb3edc86ce9460b80256ae7004ee597</a><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Jonathan<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>On 14 May 2013 10:31, Jürgen E. <<a href="mailto:jef@norbit.de" target="_blank">jef@norbit.de</a>> wrote:<o:p></o:p></p><p class=MsoNormal>Hi Jonathan,<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>On Mon, 13. May 2013 at 13:06:49 +0100, Jonathan Moules wrote:<br>> The first and most obvious thing is that it's incredibly slow to list the<br>> tables. I don't know how many tables were used in the test setup, but we<br>> have over a thousand spatial tables ranging from one row to 20 million on<br>> 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 minutes<br>> to list all of the tables. If I don't have those checkboxes checked it<br>> takes much longer (scanning the table that has ~20million features alone<br>> takes about a minute!).<o:p></o:p></p></div><p class=MsoNormal>Can you work out which queries take particularly long (I added some progress<br>messages recently)?<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>> Does QGIS need to do all of the checks it does when actually listing the<br>> tables?<o:p></o:p></p></div><p class=MsoNormal>Well, QGIS needs to know which geometry types are present. And that might take<br>long to determine. It might be possible to do that lazy - ie. on demand<br>(introduce another level to the tree where the types in the geometry column<br>are.<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>> Its also impossible to "add" a table while the list is being generated so<br>> the user has to wait until its finished before being able to continue.<o:p></o:p></p></div><p class=MsoNormal>You can "stop" the detection and then pick what's already there.<o:p></o:p></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>> Panning. Again, fine with smaller datasets, but the larger ones cause<br>> issues.<o:p></o:p></p></div><p class=MsoNormal>Do you have numeric primary keys? Otherwise QGIS must build a map that assigns<br>numeric keys to the primary keys (or ROWID if there isn't any primary key) on<br>the client side.<o:p></o:p></p><div><div><p class=MsoNormal><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>Software Engineer D-26506 Norden <a href="http://www.norbit.de" target="_blank">http://www.norbit.de</a><br>committ(ed|ing) to Quantum GIS IRC: jef on FreeNode<br><br>--<br>norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH<br>Rheinstrasse 13, 26506 Norden<br>GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502<br><br>_______________________________________________<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/mailman/listinfo/qgis-user</a><o:p></o:p></p></div></div></div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><br><span style='font-family:"Arial","sans-serif";color:#222222;background:white'>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.</span><o:p></o:p></p></div></body></html>