<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Thanks Umberto,<br>
<br>
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. <br>
<br>
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). <br>
I'll have to look at how Postgres handles subqueries to see if it
is also affected by the same issues. <br>
<br>
Note SQL Server simply (and silently) ignores the ORDER BY clause
when using SELECT TOP 100 PERCENT. <br>
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!<br>
<br>
<br>
On 28/04/2014 11:53, Umberto Nicoletti wrote:<br>
</div>
<blockquote
cite="mid:CAD34bBDRrDEr_cHX+wf3oCrU9nb1oKwrUvRHM7X7nJmOK3AkHQ@mail.gmail.com"
type="cite">
<div dir="ltr">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):
<div>
<br>
</div>
<div><a moz-do-not-send="true"
href="http://technet.microsoft.com/en-us/library/ms181154%28v=sql.105%29.aspx"
target="_blank">http://technet.microsoft.com/en-us/library/ms181154(v=sql.105).aspx</a><br>
</div>
<div><br>
</div>
<div>HTH,</div>
<div>Umberto</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Mon, Apr 28, 2014 at 10:50 AM,
geographika <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:geographika@gmail.com" target="_blank">geographika@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF"> Hi list,<br>
<br>
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. <br>
<br>
Initially my layer DATA clause was similar to the
following:<br>
<br>
SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR]
from mydata) as tbl USING UNIQUE ID USING SRID=29902 ORDER
BY [TimeStamp] DESC<br>
<br>
I hadn't realised that anything outside after the USING...
statement was ignored, so the records were displayed
unordered. <br>
<br>
I then tried using:<br>
<br>
SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR]
from mydata ORDER BY [TimeStamp] DESC) as tbl USING UNIQUE
ID USING SRID=29902<br>
<br>
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. <br>
SQL Server doesn't allow ORDER BY in a subquery, so an
error is thrown:<br>
<br>
"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."<br>
<br>
My current workaround is to change the SQL to:<br>
<br>
SELECT TOP 1000000 ID, GEOM, Rating, YEAR([TimeStamp])
AS [YEAR] from mydata ORDER BY [TimeStamp] DESC) as tbl
USING UNIQUE ID USING SRID=29902<br>
<br>
However this causes slow queries as all data needs to be
fetched in order before filtering. <br>
<br>
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?<br>
<br>
Thanks for any advice,<br>
<br>
Seth<br>
<br>
<div lang="x-western"> <font color="#888888">--<br>
web:</font><a moz-do-not-send="true"
href="http://geographika.co.uk" target="_blank"><font
color="#888888"> </font>http://geographika.co.uk</a><br>
<font color="#888888">twitter: @geographika<br>
</font><br>
</div>
<br>
</div>
<br>
_______________________________________________<br>
mapserver-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/mailman/listinfo/mapserver-users"
target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
</body>
</html>