<div dir="ltr"><div class="gmail_quote"><div dir="ltr">Hi Devs,<div><br></div><div>I did some investigation regarding to the current GetFeature implementation (in master), but I doubt if that is working properly as it stands now. I've executed the query (with pagination) against a postgis database in the debugger and it doesn't seem to be working.</div><div><br></div><div>Currently the following approach is used in mapserver:</div><div><br></div><div>1. An initial query is issued by msWFSRetrieveFeatures (in mapwfs.c) where the subset of the features are added to the result cache (from startindex limiting to maxfeatures)</div><div>2. The total count of the features are computed by msWFSComputeMatchingFeatures (in mapwfs.c) in which the result set is saved and the original layer is closed and reopened and a count(..) aggregate query is initiated afterwards (in msPostGISLayerGetShapeCount).</div><div>3. The result set is then restored and GetShape is called for each item using the same layer.</div><div><br></div><div>The main problem with this approach that in #2 the original layer is closed and reopened therefore the resultset stored by the driver is invalidated. The driver specific GetShapeCount will create a new resultset either with 1 element (the count), or the entire set of shapes if it falls back to the stock LayerDefaultGetShapeCount. This resultset will then be used in #3 where the resultindex references refer to incorrect features.</div><div><br></div><div>Would that be an option to implement msWFSComputeMatchingFeatures in that way so that the original layer is not getting closed and reopened?</div><div><br></div><div>Best regards,</div><div><br></div><div>Tamas</div><div><br></div><div><br></div><div><br></div><div> </div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Seth G <<a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a>> ezt írta (időpont: 2019. aug. 23., P, 20:07):<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><u></u><div><div>Hi Tamas,<br></div><div><br></div><pre class="m_3771970011634523779gmail-m_-8847986967277667599u-article" style="margin:0px;padding:0px;color:rgb(31,31,31);font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:inherit;font-variant-east-asian:inherit;font-weight:400;font-stretch:inherit;font-size:14px;line-height:inherit;font-family:inherit;letter-spacing:normal;text-align:start;white-space:pre-wrap;word-break:break-word;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration-style:initial;text-decoration-color:initial"><div>The MapServer FOSS4G presentation is 90% done - I have a few slides to finish off, and then run through it to check the timings.
The latest version is at: <a href="https://geographika.s3.eu-west-2.amazonaws.com/mapserver-foss4g/index.html" rel="noopener noreferrer" style="color:rgb(22,108,197)" target="_blank">https://geographika.s3.eu-west-2.amazonaws.com/mapserver-foss4g/index.html</a><br></div></pre><div>I'll send it round to the dev list this weekend, let me know if you want any changes to the GISInternals slide (or if you see any other errors/omissions in the presentation). <br></div><div><br></div><div>With regards to the MSSQL Driver - do you have an idea when you may get to look at this? <br></div><div>I've noticed any GetFeature request calls the SQL query for the layer twice even without paging causing several performance issues. <br></div><div><br></div><div>Regards,<br></div><div><br></div><div>Seth<br></div><div><br></div><div id="m_3771970011634523779gmail-m_-8847986967277667599sig57814497"><div class="m_3771970011634523779gmail-m_-8847986967277667599signature">--<br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599signature"> <a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a><br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599signature"><br></div></div><div><br></div><div><br></div><div>On Sat, Aug 3, 2019, at 10:44 PM, Tamas Szekeres wrote:<br></div><blockquote type="cite" id="m_3771970011634523779gmail-m_-8847986967277667599qt"><div dir="ltr"><div dir="ltr"><div>Hi Seth,<br></div><div><br></div><div>It is hard to provide accurate estimate, because I don't know which solution will provide better performance.<br></div><div>As a first attempt, I'd probably try the SQLFetchScroll API for both the pagination and the 2phase query access. As you can see the 2phase query executes the query twice, which would not be required if the stay within the same result set. I think this implementation would require 2-3 days only, then we'd need to do some evaluation testing. This implementation would somewhat be ODBC 3.0 specific, but I think we should encourage the using the Native Client driver or the recent MSSQL ODBC 17 driver or any other 3.0 compatible driver at some point. <br></div><div><br></div><div>Regarding to FOSS4G, you can mention my name certainly. It is great you could take the responsibility to provide the status report. I've already met with most of the mapserver devs / PSC members in peson in some earlier conferences/codesprints (starting from 2006 in Lausanne), but unfortunately, I couldn't attend to the latest ones due to family reasons. Please convey my greetings to the mapserver community, anyway.<br></div><div><br></div><div>I'll also take a look at the mapcache addition shortly.<br></div><div><br></div><div>Best regards,<br></div><div><br></div><div>Tamas<br></div><div><br></div><div><br></div></div><div><br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail_quote"><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail_attr" dir="ltr">Seth G <<a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a>> ezt írta (időpont: 2019. aug. 3., Szo, 10:12):<br></div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail_quote"><div><u></u><br></div><div><div>Excellent, thanks Tamas. If you have a rough estimate of effort it would be useful. <br></div><div><br></div><div>A couple of other things - I'm presenting the "State of MapServer" at FOSS4G in August. Is it ok to include a slide mentioning <a href="https://www.gisinternals.com/" target="_blank">https://www.gisinternals.com/</a> and include your name?<br></div><div><br></div><div>Also the offer of funding to add the MapCache CGI exe to the GISInternals builds is still there. There various dependencies for Windows can be seen in <a href="https://github.com/mapserver/mapcache/blob/master/appveyor.yml" target="_blank">https://github.com/mapserver/mapcache/blob/master/appveyor.yml</a><br></div><div><br></div><div>Regards,<br></div><div><br></div><div>Seth<br></div><div><br></div><div id="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928sig57814497"><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928signature">--<br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928signature"> <a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a><br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928signature"><br></div></div><div><br></div><div><br></div><div>On Fri, Aug 2, 2019, at 11:32 AM, Tamas Szekeres wrote:<br></div><blockquote id="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928qt" type="cite"><div dir="ltr"><div>Hi Seth,<br></div><div><br></div><div>Yes, I'm interested in. <br></div><div>I'll check how much work does it involve.<br></div><div><br></div><div>Best regards,<br></div><div><br></div><div>Tamas<br></div><div><br></div></div><div><br></div><div class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928qt-gmail_quote"><div dir="ltr" class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928qt-gmail_attr">Seth G <<a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a>> ezt írta (időpont: 2019. aug. 2., P, 10:44):<br></div><blockquote class="m_3771970011634523779gmail-m_-8847986967277667599qt-gmail-m_-2012595088900243928qt-gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>Hi Tamas,<br></div><div><br></div><div>I've run into some performance problems with the MSSQL driver as outlined at <a href="https://github.com/mapserver/mapserver/issues/5842" rel="noreferrer" target="_blank">https://github.com/mapserver/mapserver/issues/5842</a><br></div><div><br></div><div>For a WFS GetFeature request the full SQL query for a layer is run 3 times to return a result. For layers with many fields/complex views this makes paging through records very slow. I think implementing paging would at least speed up 2 of the queries, and changing the 3rd to get a count rather than run the full query would make performance acceptable. <br></div><div><br></div><div>As you've been maintaining the MSSQL driver would you be interested in implementing paging/investigating performance? I have a project that would benefit greatly from this, so would be able to pay for any time spent. <br></div><div><br></div><div>Regards,<br></div><div><br></div><div>Seth<br></div><div><br></div><div>-- <br></div><div> <a href="mailto:sgirvin@geographika.co.uk" target="_blank">sgirvin@geographika.co.uk</a><br></div></blockquote></div></blockquote><div><br></div></div></blockquote></div></div></blockquote><div><br></div></div></blockquote></div>
</div></div>