Efficiently retrieve many small changing elements

[tobiasBora]

I'm using Dexie.js (svelte backend) to build a kind of special kind of spreadsheet, and I have a table that contains items like:

export interface Cell {
  tab: TabID,
  column: columID, // This is a string ID, not just a small integer column number
  row: rowID,
  formula: string,
  evaluatedFormula: number,
  // More attributes
}

Now, I'm wondering what is the most (reasonably) efficient way to get these often-changing cells (I may have thousands of them, possibly more) to show them in my spreadsheet. I can think of 2 options:

• do many tiny queries for each cell: by creating a new component for every cell, I could do inside my component a query for each cell… That would certainly be the easiest to program, but then I'm a bit worried : how does Dexie.js perform with thousands of tiny queries like that?
• do one big query at the beginning of my spreadsheet that queries all the cells of the current tab, and then in my inner loop that displays the cell, I "somehow" get the items queried above. The issue here is that if I do let myCells = liveQuery(async () => await db.cells.where("tab").equals(tabID)).toArray();, I endup with a list of items, i.e. I can't efficiently search for them searching through the loop as myCells.filter(c => c.column == currentCol && c.row == currentRow) takes linear time for each cell, i.e. a total quadratic time, just to display the cells… and I'm even more scared to know what happens when a cell changes. I may be able to search this list with a custom binary search assuming I create an index [tab,column,row], but I don't know if it is optimal… Or is it maybe possible to ask Dexie to create a table and not a list, that I could search in O(1)? I don't want to create this list manually in the live query, as I guess any update of a cell would result in O(n) operations just to create the list. So access would be O(1), but I'd also to have O(1) updates.

Any advice?

[dfahlander]

I would suggest to do one query per cell component or possibly by row. If one cell is updated, only that query will rerun. But if you have one large query, it would rerun when any cell changes.

You could combine it with a query in parent component doing

db.cells.where("tab").equals(tabID)).primaryKeys()

to find which cells that exists (don't know if that makes sense in your case though)

[tobiasBora]

Great, thanks a lot I'll try that way! So do you mean that if a single element changes the liveQuery will run again in full complexity? In pouchdb they can do it a bit more cleverly where they only receive the diff (so if one element is added it's a tiny element), and then one can manually update, e.g., a map to add/remove the element (so again one step). So kind of O(1) instead of O(n). I'm also very curious to understand how queries work, how they are batched etc. For instance if I create one query per cell it will do a single subscription to the db or one subcription per cell?