Re: LinqToSql Paging problem (bug?) with Skip and Take

d-42 wrote:

On Apr 18, 3:47 pm, Marc Gravell <marc.grav...@xxxxxxxxx> wrote:
Should it be considered a bug that skip / take do not generate
stable sort orders on their own?

Well, without a stated order, any standard SQL query is of
indeterminate order, and thus in theory unstable. In reality, most
databases will (as an implementation detail) honour a clustered
index as a default sort [assuming it can pick one from the numerous
tables involved], but even this isn't typically guaranteed. In
reality, you should always specify an order condition if you are
paging.

Marc

Thanks Marc for your reply and I don't disagree with what you are
saying, but its more subtle an issue than that.

Specifying a mere 'order condition' isn't sufficient. If I were to
sort by last name and there were multiple people with the same last
name I don't want the rows with the same last name trading places
between queries. Even if I were to sort by the primary key of a parent
in a join query, I'll have to contend with unstable row order.

Requiring that I define my queries so that row order is 100%
determinate in order to use paging is a pretty serious burden, and if
skip and take really require that effort in order satisfy being
reliable then they should really generate row-order preserving sql
themselves.

By definition 'SELECT' returns an unordered set, as SQL is set based,
and sets have no order. If you want an order in the returned set, you
have to define the ordering, with ORDER BY. If that results in multiple
rows with the same value, again, the order is undetermined.

So you can jump up and down that it is a burden and all, fact is that
you didn't provide a proper ordering of the set you wanted to return,
and therefore the RDBMS has to pick an ordering ;). Mind you, ANY
ordering it chooses IS an ordering, so therefore you have to provide it
yourself.

=> And, if you take a look at the sql its generating, they DO do this!!
Take a look at the second sql example I provided. Linq is clearly
generating row-number stable ordering on the fly without my help, and
that's a good thing.

It can't page otherwise. The rownumber is required to determine which
rows are in which page to return. though, the order in which the rows
are in that set with rownumber is determined based on the ordering in
the query! The rownumbers are added to the final, ordered set. So if in
THAT set there are some rows with undeterminable ordering (same value
in the order column(s)) it can be the order between queries flips.

Normally you won't see this, but you WILL if sqlserver can optimize
something internally with memory pages. Then the order will be
different in each query because it already has some rows in memory.

The problem, is that, for page 1, skip is 0, and the query is
processed as if the skip wasn't applied at all, all the row numbering,
and order stabilizing stuff is optimized out.

Skip(0) should generate the same row numbering sql that skip(n) does
so that paging systems can rely on the first page of a paged view
operates on the same query, with the same row order that will be used
for the other pages.

Skip(n) imposes a consistent row order. Skip(0) should use the same
row order.

The more I think about it, the more I think optimizing skip(0) out of
the query completely is a bug.

No, you think that the ordering you see in the rownumber column is a
valid order, but that's applied TO the ordered set which happens to be
NOT properly ordered! :).

Take northwind. Sort customers on country, then page over customers
with 10 rows a page. You'll see that rows aren't in the proper order
sometimes. This is because there are more customers per country. So you
have to tell the RDBMS in which order the customers have to be placed
which have the same country value, e.g. customerid, or city.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: OO vs. RDB challenge
    ... Lets say you have a list of customers ... Layer 1. ... SQL might help with Layer 1 ... SQL might help with Layer 2 but only if the query is simple enough. ...
    (comp.object)
  • Re: Modifying Data Directly through Studio Manager
    ... 'tool' to allow your customers to manage their data. ... I've seen people comment on turned on the query pane, ... Many of my customers are starting to upgrade to SQL 2005, ... Isaac Shloss, MCSE ...
    (microsoft.public.sqlserver.tools)
  • Re: Last 30 days in Query
    ... In order to do the last 30 entries, you would have to first decide on an order to sort your data. ... If you are asking "Where do I enter these SQL statements at?", you need to enter them in SQL view of the query. ... Design view, Datasheet view, and SQL view. ...
    (microsoft.public.access.queries)
  • Re: LinqToSql Paging problem (bug?) with Skip and Take
    ... as a default sort [assuming it can pick one from the numerous tables ... in a join query, I'll have to contend with unstable row order. ... reliable then they should really generate row-order preserving sql ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Modifying Data Directly through Studio Manager
    ... Because business decision choose to allow this doesn't mean that it isn't an ... 'tool' to allow your customers to manage their data. ... I've seen people comment on turned on the query pane, ... Many of my customers are starting to upgrade to SQL 2005, ...
    (microsoft.public.sqlserver.tools)
Loading