Hi,

I have a view which contains the following 9 rows....

UGC Cinema Batman Begins 405 UGC Cinema Madagascar 401 UGC Cinema Wedding Crashers 402 UGC Cinema War of the Worlds 404 UGC Cinema The Descent 403 UGC Cinema Dark Water 452 UGC Cinema Fantastic Four, The 451 UGC Cinema Charlie and the Chocolate Factory 453 UGC Cinema Skeleton Key, The 505

Paging is turned on and I'm sorting by cinema name so all of the above rows are consecutive in the results set.

Now, the first 5 records for this cinema currently appear at the bottom of page 2 like so....

UGC Cinema Batman Begins UGC Cinema Madagascar UGC Cinema Wedding Crashers UGC Cinema War of the Worlds UGC Cinema The Descent

but then at the start of page 3 is..

UGC Cinema War of the Worlds UGC Cinema Wedding Crashers UGC Cinema Madagascar UGC Cinema Batman Begins

So it would appear that 9 rows are being shown but on the 3rd page the rows of this group are ordered in the opposite direction to how they are on page 2 and thus some of the rows appear on more than one page and some rows don't get shown at all.

Is this to be expected?

Cheers,

Ian.

You have an order by in your view (and a top n) ?

Neither. Here's the view definition..

SELECT
tbl_CompanyAddress.Company,
tbl_film.Title,
tbl_film_cinema.FilmCinemaID

FROM
tbl_cinema

INNER JOIN
tbl_film_cinema

ON
tbl_cinema.CompanyAddressID = tbl_film_cinema.cinemaID 

INNER JOIN
tbl_film

ON
tbl_film_cinema.filmID = tbl_film.filmID

INNER JOIN
tbl_CompanyAddress

ON
tbl_cinema.CompanyAddressID = tbl_CompanyAddress.CompanyAddressID

Ok here's the thing. By default, the rows in a select are unorded, and can be returned in any order by the database system. If you want a given order (for example: Cinema, Moviename), you have to specify that.

The paging query does this: - it creates a temptable - it inserts the resultset of the query executed (up till the page requested) into this temptable, with a new PK, which is an identity - it then selects from the temptable the actual page to return, using the identity PK field.

As the view uses joins, and because a view is executed in realtime (the results aren't read from disk ) the resultset of the view may differ for each execution of the view, and thus the resultset inserted into the temptable may have a different order each time (within the sorting you specified), unless you apply proper sorting on these fields which give you the order you want.

In this case, the resultset is sorted properly on Cinema, but as more rows have the same cinema value, these rows are left in the order they appeared in the resultset (by the database system).

So, to get the proper order, you should apply a second sortcolumn, moviename, which then will give the proper order and won't give the weird paging results.

Thanks for your reply.

the resultset of the view may differ for each execution of the view

Yes but typically it doesn't. If I refresh page 2 of the results its always the same set in the same order and its the same for page 3.

Its the page index which appears to be effecting the order.

you should apply a second sortcolumn

If I start doing this then won't every paged set of results have to have every column listed in the sort expression otherwise the most insignificant of row attributes potentially will cause these weired paging results?

I would rather turn paging off than goto this extreme to accommodate this issue.

Surely there's something in the paging SQL which is pulling the rows from the temp table in a different order upon a different page index?

Cheers,

Ian.

Ian wrote:

the resultset of the view may differ for each execution of the view

Yes but typically it doesn't. If I refresh page 2 of the results its always the same set in the same order and its the same for page 3.

Hmm. Well the query is executed each time again, though it stays weird.

Its the page index which appears to be effecting the order.

you should apply a second sortcolumn

If I start doing this then won't every paged set of results have to have every column listed in the sort expression otherwise the most insignificant of row attributes potentially will cause these weired paging results?

Only within their significant values. Though it still can cause a row to appear twice

I would rather turn paging off than goto this extreme to accommodate this issue. Surely there's something in the paging SQL which is pulling the rows from the temp table in a different order upon a different page index?

It just executes the query with all the results, then does a select on the identity column, where it selects the page to return using the value of the identity column. So the query is just executed for each page request, and in theory should resolve in the same row order.

When you turn on tracing of the DQE, you'll get the SQL being produced. When you run that a couple of times in Query analyzer (close connection, reconnect) does it give the same results or different?

The sad thing is that it's hard to reproduce, as it's random (Well, not in your database, but I've never seen it happen here with our unittests)

We appear to have just experienced the same problem as Ian describes at the beginning of this thread.

We are in the final stages of testing the next release of our application. One of our test scenarios is to validate the sort order of our paged grids (yeah, someone has to do it manually at the moment poor sucker). We have not had a problem previously but in this round of testing the sort order failed in one particular case.

We're using SQLServer 2000 and llblgen 1.2004.2 about box: July 13th 2005.

This particular query has a large set of result fields and > 150 result rows.

We captured the llblgen generated query in query analyser and did some disecting and analysis on that query.

The paging query does this: - it creates a temptable - it inserts the resultset of the query executed (up till the page requested) into this temptable, with a new PK, which is an identity - it then selects from the temptable the actual page to return, using the identity PK field.

We independently ran the query that produces the resultset that is inserted into the temp table. That sorted correctly.

We then selected the values manually out of the temp table. The order was wrong.

We also selected out the __rowcnt PK field from the temp table along with our results and got the following unexpected and interesting results (this is a partial extract - original query had order by STREETNAME):

 __rowcnt,STREETNAME

146,Auckland St
147,Auckland St
148,Butler Dr
149,Butler Dr
150,Butler Dr
141,Adelaide St
142,Adelaide St
143,Adelaide St
144,Adelaide St
145,Auckland St

So the values coming out of the temp table are NOT in the same order as they were inserted !!! So we did this to the paging query:

 FROM #TempTable
WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd
ORDER BY [__rowcnt]

And of course this time got the right results. But then as you say in this thread:

Ok here's the thing. By default, the rows in a select are unorded, and can be returned in any order by the database system.

Unfortunately it looks like that goes for temp tables as well which has a remote chance of screwing up sort order in paging.

I'll email you our query and results so you can take a look if you'd like.

Is your query based on a view with an orderby? (very important, as that will mess up the results)

No View. Straight adapter based predicates. Core query does the right thing.

Order in temp table changes if we: - add a clause which filters out NULL values from the column being sorted - reduce the number of columns being selected in the core query

But I think this is just playing with the query execution plan.

See the actual query in the file I emailed you at support.

Ok, I see what you mean. THe main thing for me was that I could never reproduce it here, so I thank you for your time investigating this and come up with the fix: ORDER BY __rowcnt works. My unittests work the same when I add that to the query, so I'll upload a hotfix for 1.0.2004.2 in which I've added ORDER BY __rowcnt ASC to the query. Please let me know if that solves your problem.

Ok, I've uploaded a hotfix for the 1.0.2004.2 runtime libraries with a fix for this. Could you please test these to see if they work for you? thanks.

Will do. Although I won't be able to test this until tomorrow our time (its 9pm here now). So I'll get the fix into our environment and get our testers to try it out tomorrow morning.

Its certainly intermittent behaviour and hard to reproduce. I'm just surprised we haven't come across it before this.

Just one of those things.

No problem. It's been reported before but I never could find a fix. I hope this solves it. Thinking about it, it should

Well, that did it perfectly.

Our testers did some pretty extensive paging and sorting testing today (poor buggers) and the new version worked in all cases. Testing was performed on the same database that exhibited the original (and repeatable) sorting problem but worked fine with the new version.

Thanks as always for the prompt support. Just in the nick of time for us. We're about to release our next version to production tomorrow afternoon after 4 months of development so talk about an 11th hour fix! At least we are good to go now.

Thanks again. (My Project Manager was pretty impressed with your response time to this and the rest of our team were commenting how we need to aspire to that sort of support response for our own stuff ).

takb wrote:

Well, that did it perfectly.

Our testers did some pretty extensive paging and sorting testing today (poor buggers) and the new version worked in all cases. Testing was performed on the same database that exhibited the original (and repeatable) sorting problem but worked fine with the new version.

Glad it's solved! Indeed, manual testing stuff is a real pain... (I have some of those databinding unittests as well... I always have to stop myself skipping these )

Thanks as always for the prompt support. Just in the nick of time for us. We're about to release our next version to production tomorrow afternoon after 4 months of development so talk about an 11th hour fix! At least we are good to go now.

no problem.

Thanks again. (My Project Manager was pretty impressed with your response time to this and the rest of our team were commenting how we need to aspire to that sort of support response for our own stuff ).

Thanks! Customers are a company's most valuable asset, so keeping your customers happy is a top priority . After all, it's for a company much cheaper to keep customers than to get a customer back after he/she left you for a competitor.

Will the hotfix be in effect if I install from the main LLBLGen Pro installer?

An update of the LLBLGen Pro installer includes all the hotfixes released before the release of the installer.

And you can also check the change-log of the installer.

Ian wrote:

Will the hotfix be in effect if I install from the main LLBLGen Pro installer?

This is a hotfix for the Runtime libraries. So the archive contains just the runtime libraries. You copy / unpack the archive to the official llblgen pro installation folder and recompile your code, which is mainly the procedure to take with hotfixes for runtime libraries.

If you just use installers, Walaa's answer is the one to go with.