<font face="Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size="2">or use a subquery in the DATA statement instead of FILTER or PROCESSING<br><br><font color="#990099">-----"mapserver-users" <mapserver-users-bounces@lists.osgeo.org> a écrit : -----</font><div class="iNotesHistory" style="padding-left:5px;"><div style="padding-right:0px;padding-left:5px;border-left:solid black 2px;">A : "'mapserver-users@lists.osgeo.org'" <mapserver-users@lists.osgeo.org><br>De : "Lane, Michael" <michael.lane@fadq.qc.ca><br>Envoyé par : "mapserver-users" <mapserver-users-bounces@lists.osgeo.org><br>Date : 25/01/2017 16:13<br>Objet : [mapserver-users] Mapfile - filter by parameter - full scan<br><br> <!--Notes ACF <meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">--> <!--[if !mso]><style></style><![endif]--><!--[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]--> <div class="WordSection1"> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Hi,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Experiencing something not expected with the filter condition in mapfile.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">I add this code in my mapFile to have the possibility to void the filter if no parameter is given:<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom:12.0pt"><i><span style="font-family: "Arial","sans-serif";"><font color="#004080">VALIDATION</font></span></i> <br> <i><span style="font-family: "Arial","sans-serif";"><font color="#004080">       no_diag '^[0-9]{1,2}$' </font></span></i><br> <i><span style="font-family: "Arial","sans-serif";"><font color="#004080">       default_no_diag '0'</font></span></i> <br> <i><span style="font-family: "Arial","sans-serif";"><font color="#004080"> END</font></span></i> <br> <i><span style="font-family: "Arial","sans-serif";"><font color="#004080"> DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"</font></span></i> <br> <i><span style="font-family: "Arial","sans-serif";"><font color="#004080">  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))   </font></span></i><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><i><font color="#1f497d">WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …</font></i><o:p></o:p></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">I was expecting something more like :<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><i><font color="#1f497d">WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …</font></i><o:p></o:p></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">So I had to add more parenthesis to the filter to get it correct<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><i><font color="#1f497d">((('%no_diag%' = '0') or ([no_diag]='%no_diag%'))) </font></i><o:p></o:p></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Thanks<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">Michael Lane </span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><i><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">Technicien en informatique</span></i><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> <br> </span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">Direction des solutions d'affaires</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><b><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">La Financière agricole du Québec</span></b><b><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span></b><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">1400, boul. Guillaume-Couture</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">Lévis (Québec) G6W 8K7</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA">Tél. : 418 838-5614, poste 6034</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:FR-CA"><a href="mailto:Michael.Lane@fadq.qc.ca"><span style="color:blue">michael.lane@fadq.qc.ca</span></a></span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> <br> </span><span style="font-size:12.0pt;font-family:"Trebuchet MS","sans-serif";color:gray;mso-fareast-language:FR-CA"><img id="_x0000_i1025" src="cid:image001.jpg@01D27723.BAB8F550"  width="106" height="33" border="0"></span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><span style="font-size:12.0pt;font-family:"Trebuchet MS","sans-serif";color:gray;mso-fareast-language:FR-CA"><img id="_x0000_i1026" src="cid:image002.jpg@01D27723.BAB8F550"  width="269" height="44" border="0"></span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-language:FR-CA"><br> </span><span style="font-size:12.0pt;font-family:"Trebuchet MS","sans-serif";color:gray;mso-fareast-language:FR-CA"><img id="_x0000_i1027" src="cid:image003.jpg@01D27723.BAB8F550"  width="188" height="26" border="0"></span><o:p></o:p></p> </div> <div><font size="2" face="Courier New,Courier,monospace">_______________________________________________<br>mapserver-users mailing list<br>mapserver-users@lists.osgeo.org<br><a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a></font></div></mapserver-users-bounces@lists.osgeo.org></michael.lane@fadq.qc.ca></div></div><div><br>Si vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur.  Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.<br><br>L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.<br></div></font>