1

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?

  • Why can't you use some SQL database and issue CREATE TABLE requests at runtime? Of course you should probably avoid having a lot (e.g. hundreds) of tables... – Basile Starynkevitch Sep 4 '14 at 7:25
  • 2
    @gnat aren't you overreacting a bit? I'm also asking for a general solution to my problem. If that would be a particular product, so be it. – Wouter Sep 4 '14 at 8:23
  • 1
    "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." - what you've done is basically implement a DBMS yourself, and now you've found that that is hard, which is why we get other people to do it. Use a DBMS. There's nothing wrong with having hundreds of tables! – AakashM Sep 4 '14 at 10:32
  • 2
    @Wouter: I changed your question a little bit, otherwise I would expect our community to close it soon (they are very strict about tool-recommendations beeing off-topic). – Doc Brown Sep 4 '14 at 11:15
  • 1
    I don't understand why the denormalizing is required? if the power user creates a schema, have them specify the required indexes, ie article.supplierId – Ewan Aug 30 '18 at 9:08
1

You're asking for an SQL operation on a NOSQL database which seems rather illogical to me. That's approaching NOSQL with an SQL mindset, which you should let loose.

For the problem you state I would say look into a NOSQL solution that provides full-text indexing like Apache Solr or Sphinx

  • 1
    I do not agree. The users are free to changes schema's at will and potentiolly we have hundreds of "tables". If you read around a bit, changing schema's and create such an amount of tables, the general consensus is: do not use an sql db for this. – Wouter Sep 4 '14 at 8:01
  • 1
    That's why I suggested: index everything. – Pieter B Sep 4 '14 at 8:14
  • 1
    I'm already doing that. The point of the question is that that is really cumbersome and I am looking for some sort of automated way of doing that. – Wouter Sep 4 '14 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.