136 reputation
1
bio website
location France
age
visits member for 2 years, 2 months
seen Feb 20 '13 at 16:19

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