about Collection.offset() performance

[xueyou2000]

If there are 6000 data,
when N is big, query is slower

const N = 0;
collection
  .filter((data) => {  return /** */ })
  .offset(N)  // The larger the N value, the slower the query
  .limit(10)
  .toArray()

[dfahlander]

Yes, that is true and also documented here.

Paging can generally be done more efficiently by adapting the query to the last result instead of using offset/limit.

const PAGE_SIZE = 10;
const page1 = await db.friends
  .where('age').above(25) // keyrange query (affects result order)
  .filter(friend => /nice/.test(friend.notes)) // Some custom filter...
  .limit(PAGE_SIZE)
  .toArray();

// Now, prepare next page by picking last entry
const lastEntry = page1[page1.length-1];


const page2 = await db.friends
  .where('age').above(lastEntry.age)
  .filter(friend => /nice/.test(friend.notes)) // Some custom filter...
  .limit(PAGE_SIZE);
  .toArray();
...

[dfahlander]

I updated the docs for offset() with hints for paging. Some more samples there...

http://dexie.org/docs/Collection/Collection.offset()#a-better-paging-approach

[njvrzm]

I'm seeing some behavior with Collection.offset() that I think contradicts the documentation. In the section on performance the docs say:

Examples where offset() will be fast
...
db.[table].where(index).between(value).offset(N)

I'm using a simple between query like this and the query time increases more or less linearly with the offset, as I'd expect it to do for what the documentation describes as "advanced queries".

I've put a demo on codepen to illustrate this. It creates a table with 100k entries, just two numbers in each, both indexed. Run pageWithOffset() in the console to see the behavior. With small offsets (<1k) retrieving a page of 100 results takes about 10ms, but this increases to around 80ms for offsets around 10k, and continues increasing. (Run stop() in the console if you get bored watching the numbers go up.)

Is it possible that there's some criterion for a fast offset() call that's missing from the documentation? Or perhaps it's simply not working correctly? Or, if this is the expected behavior, perhaps a note could be added to the documentation to the effect that "fast" doesn't mean "more or less constant time", which is what I would expect.

[njvrzm]

Well, I dug in a bit more and it turns out this is actually a limitation of IndexedDB itself - at least on Chrome and Firefox on macOS. IDBCursor.advance(offset) takes longer as offset increases, approximately linearly. And testing the slow-offset cases the docs describe I see they are in fact a lot slower than the fast cases.

Personally I think it would still be worth noting in the documentation that offset has non-constant performance. I'd be happy to make a PR if you agree.

Either way, thanks for Dexie! I'm really enjoying using it.

[dfahlander]

Thanks for pointing this out. I think describing offset as "fast" is misleading, even though it is faster than the other offset cases where dexie needs to go some non-native work to fulfill the offset. You are more than welcome to update the docs the way you find more accurate 👍