What is the fastest method for performing more than 64 table joins in SQlite?

Question

I'm planning on generating queries for SQLite that will involve many joins on 12 tables that will surpass the 64 table join limit in SQLite. (~250 table joins or possibly more) This will be running on android eventually. The purpose behind this is to have X amount of user defined fields in the result set depending on the report that is being generated.

Unfortunately I'm not a DBA and I do not know of an optimal way to achieve this.

So far I think the options are:

• Use 2 temp tables to juggle the result set while joining the max amount possible. (My previous solution in SQLServer, fairly slow)
• Produce result sets of a few columns and a key to join on and store them in n temp tables. (Where n is less than 64) Then join all the temp tables on their common key.
• Create a single temp table and fill it up one insert or update at a time.
• Don't do a big join, perform many selects instead and fill up some sort of data container.

Is there something else I should consider?

Answer 1

Per your comment on Mike's response, "the query to generate the report needs to join and rejoin many many times".

Frequently, when dealing with reports, you'll want to split your query into bite-size chunks, and store intermediary results in temporary tables where applicable.

Also, your question makes it sound like you've an entity/attribute/value store and trying to pivot the whole thing. If so, you may want to revisit using this design anti-pattern, since it probably is at the source of your problem.

Answer 2

I don't think you can get "fast" on any relational database platform when you're trying to join that many tables - any kind of built-in optimisation is going to give up the ghost. I would be likely to review my design when I saw as many as ten tables in a query.

I think your schema design needs to be revisited. 250+ tables in a schema (on a phone!) doesn't make sense to me - I run several enterprise apps in a single DB with 200+GB of data and there are still only 84 tables. And I never join all of them. Do all your tables have different columns? Really different? Could you post a few entries from sqlite_master?

Answer 3

Since your app is running on an Android device, I would guess it syncs with an enterprise-class database on a server somewhere. The real solution is to generate a de-normalized representation of the server data on the device database, so it can be more readily accessed.