If you don't have an index yet on TimeStamp, you could try creating one with an ORDER clause and see if this improves performance (with a properly created index you can avoid running separate order operation for every query):
http://technet.microsoft.com/en-us/library/ms181154(v=sql.105).aspx HTH, Umberto On Mon, Apr 28, 2014 at 10:50 AM, geographika <[email protected]> wrote: > Hi list, > > I recently came across a problem with the order features are rendered by > Mapserver. I am displaying road surveys done at various times, and have a > WMS layer that displays the latest survey results. Rather than trying to do > complicated segmentation, I simply display features ordered by survey date, > with the most recent records on top. > > Initially my layer DATA clause was similar to the following: > > SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from mydata) as tbl > USING UNIQUE ID USING SRID=29902 ORDER BY [TimeStamp] DESC > > I hadn't realised that anything outside after the USING... statement was > ignored, so the records were displayed unordered. > > I then tried using: > > SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from mydata ORDER > BY [TimeStamp] DESC) as tbl USING UNIQUE ID USING SRID=29902 > > However when the data for the WMS is prepared this SQL becomes a subquery, > and a WHERE clause is added to get the data for the requested extent. > SQL Server doesn't allow ORDER BY in a subquery, so an error is thrown: > > "The ORDER BY clause is invalid in views, inline functions, derived > tables, subqueries, and common table expressions, unless TOP or FOR XML is > also specified." > > My current workaround is to change the SQL to: > > SELECT TOP 1000000 ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from > mydata ORDER BY [TimeStamp] DESC) as tbl USING UNIQUE ID USING SRID=29902 > > However this causes slow queries as all data needs to be fetched in order > before filtering. > > So my question is is there a way to order records using another LAYER > property (similar to the FILTER property), so ordering is done only on the > records returned for rendering? > > Thanks for any advice, > > Seth > > -- > web: http://geographika.co.uk > twitter: @geographika > > > > _______________________________________________ > mapserver-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/mapserver-users >
_______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
