2

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?

Biff MaGriff
  • 8,102
  • 9
  • 61
  • 98
  • 6
    Wow do you really need that many tables? How many columns does each one have? In this case I'd say no method will be fast on a phone, if possible do all the joining on a server and just send the final result as an XML or JSON file. – Joseph Earl May 05 '11 at 16:01
  • The tables I'm joining on are thin, 2-3 columns, but I'm joining on the same ones many many times. I will most likely have an option to do the processing via web service, however I would like the user to have the option to do it locally without a data connection. – Biff MaGriff May 05 '11 at 16:50
  • 3
    @Biff MaGriff: "Is there another option I may be missing?" -- I humbly suggest that SQLite perhaps is not the right data storage mechanism for whatever it is that you are doing. – CommonsWare May 05 '11 at 17:01
  • 2
    Another approach might be to denormalize your data base. Sometimes you can collapse multiple tables into single tables with `NULL` columns without increasing the row count. – Ted Hopp May 05 '11 at 19:24
  • If you step back from the query... is there anywhere you can reproduce the same result set by using a lot of WHERE clauses instead of a lot of JOIN statements? – Mark D May 12 '11 at 23:37
  • @Mark No I don't think I can. – Biff MaGriff May 13 '11 at 15:36
  • A question with a code example "how could I make these 65 joins fewer" could be successful over in codegolf.stackexchange.com ;) – JOG Jan 11 '18 at 20:58

3 Answers3

3

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?

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
3

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.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
2

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.

Tony the Pony
  • 40,327
  • 71
  • 187
  • 281