Thanks Umberto,
I have an index set up on the TimeStamp field, but looking at the query
plans it won't affect performance as SQL Server still has to get all the
records in the table to order them, before applying WHERE clauses in the
outer query.
What would be ideal is a LAYER property such as ORDER which would allow
a field to be set to order records for rendering (similar to FILTER
which is added to the outer query).
I'll have to look at how Postgres handles subqueries to see if it is
also affected by the same issues.
Note SQL Server simply (and silently) ignores the ORDER BY clause when
using SELECT TOP 100 PERCENT.
This introduced hard to find logical errors in my application which were
only spotted by someone who knew the data well, so I'd suggest anyone
who relies on features being rendered in a specific order to
double-check their apps!
On 28/04/2014 11:53, Umberto Nicoletti wrote:
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
<http://technet.microsoft.com/en-us/library/ms181154%28v=sql.105%29.aspx>
HTH,
Umberto
On Mon, Apr 28, 2014 at 10:50 AM, geographika <[email protected]
<mailto:[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]
<mailto:[email protected]>
http://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
