We have a use case where we store table-like data, but we know about the schema of the data only at runtime. In our application, a power user defines a schema and normal user can create records and make relations between those records. The problem that we are having is that when doing complex queries over the related "tables" (one could say, joins) with filtering and sorting, stuff becomes slow, which is logical.
To give an example. Lets say we have an article table with 1M rows and supplier table with 500 rows. Some suppliers have 50K articles. Now lets say I want to get all the articles from that supplier, but sorted by the article name. This query is slow. Which is not strange as it basically creates some sort of temporary table/result, which it needs to sort, but it cannot do it fast as there is no index on a temporary table/result.
Our current solution uses Neo4j, but I also tried this on orientdb and in both cases queries are slow, because of the same behavior.
Now we have solved this problem for now by creating indexes at runtime based on the schema the user provided, denormalizing articles and suppliers in one index. The problem is that keeping this index up-to-date is very cumbersome. Lets say we want to filter on the articles table on its supplier name. Now the supplier name is in the index, but it has been replicated 50K times in the above example. So if the user changes the name of the supplier, 50K index records need to be updated. Giving us all sorts of performance headaches and timing issues such as what happends if the user in a couple of seconds changes the supplier name twice, we need to queue the operations. Etc etc.
So my actual question, is there a clean, non-cumbersome way to automatically denormalize or index out of the box, for a system where the DB schema can change at run-time?
CREATE TABLErequests at runtime? Of course you should probably avoid having a lot (e.g. hundreds) of tables...