network profile
| bio | website | |
|---|---|---|
| location | France | |
| age | ||
| visits | member for | 10 months |
| seen | Feb 20 at 16:19 | |
| stats | profile views | 0 |
|
Nov 2 |
comment |
Filtering data ordered by rowversion Thanks for tanking the time to think about it. |
|
Nov 2 |
comment |
Filtering data ordered by rowversion Do you mean a global or an Id local VersionNumber ? Either case, I'm unable to see how that will help with the question, could you elaborate further ? |
|
Nov 2 |
comment |
Filtering data ordered by rowversion Right, one of the design change solution is to have the client remember the MAX(RV) per Id (or a subscription system where the internal application remembers all Id/RV pairs) and I use this patern for an other client. An other solution was to force the client to always retrieve all the Ids (which make the indexing problem trivial). It still doesn't cover the question particular need : Incremental retrieval of a subset of Ids with only one global counter provided by the client. |
|
Oct 31 |
comment |
Filtering data ordered by rowversion I'll try to be clearer but all in one, the query must be asserted by : SELECT COUNT(*) FROM Data d WHERE d.RV BETWEEN @Cursor AND (SELECT MAX(RV) FROM (QUERY)) AND d.Id IN (SELECT Id FROM @Ids) must return exactly @MaxRows. |
|
Oct 31 |
comment |
Filtering data ordered by rowversion I've read some on CROSS APPLY (partly thanks to your blog posts) and I see that this query does improve performance when applying the TOP operator as it will only apply the sub-query until @MaxRows rows are fetched from it, using the Ids list as seed. The problem is that it doesn't account for the constraint of "incremental chunked data retrieval", as I can't use the MAX(RV) from the result-set of this query to perform subsequent query and have no row leftover. |
|
Aug 9 |
comment |
Filtering data ordered by rowversion If you sort the result by (Id, RV), even providing the last Id and last RV for the next call will make it miss many rows. The only way to make it work using a (Id, RV) sort is force the client to track RV at the Id level, and not at the global level (which might be one solution). |
|
Aug 8 |
comment |
Filtering data ordered by rowversion @8kb : the update statements that run on the table only modify the Value column.
@crokusek : Won't ordering by RV, ID instead of RV only increase sort workload without any benefice, I don't understand the reasoning behind your comment.
From what I've read, RV should be unique unless inserting data specifically into that column, which the application does not. |
|
Aug 7 |
awarded | Student |
|
Aug 7 |
comment |
Filtering data ordered by rowversion Crosspost with stackoverflow.com/questions/11586004/…;. I've removed Oracle version for the moment because I've discovered that ORA_ROWSCN isn't indexable (and hardly through indexed materialized views). |
|
Aug 7 |
asked | Filtering data ordered by rowversion |
