32 Join Limit in SQLite

# The Problem

SQLite allows a maximum of 32 joins at any given moment (for good reason), and it is one of the databases we have to support. But when we do a ticket query, the ticket fields need to be joined with the `Field` table, which is then joined with either the `PrimitiveField` or the `ComplexField` table, which is then joined with the implementing class table (e.g. `MilestoneField`), which then *may* be joined with the target table (e.g. `Milestone`).

So for every query constraint (including ANDs and ORs) that the user specifies on the query page, there may be up to 4 joins in worst case. Thus if the user specifies 8 constraints or more, in worst case an `OperationalError` will occur.

# What we’ve tried

## Eager Joins

Do all the joins eagerly. I.e. Take all the fields that will be constrained, sieve them to leave only the unique tables, then do joins. Only after all these joins do we apply filters and ordering. This will do a join on the 3 tables mentioned above (i.e. the ticket field, `Field`, `PrimitiveField`/`ComplexField`) only once, then one join for each target table.

Disadvantages:

* This solution will increase the number of filters that can be applied only multiplicatively. I have reasoned mathematically that this multiplication constant is bound by 2. So the maximum number (in worst case) of filters increases to 16.

## Split Queries

Split a query into sub-queries, one for each field. Each sub-query will then have only 3 joins plus one if it’s a complex field. This will be done for each field, but the sub-query will be executed before we move on to the next field and so the maximum number of concurrent joins will be capped at 4.

Disadvantages:

* We will need to take the results of each sub-query and reconcile them manually. This can be done by taking the intersection of the resulting sets of tickets. This, however, circumvents database query optimizations and it will become very slow.
* We will need to do some extra joins at the end in order to do the ordering and grouping. This will require some work on our side when we would rather have the database do everything for us.

## Change the database schema

We may be able to change the database schema so that the queries don’t need to do as many joins.

Disadvantages:

* The current schema works very, very well. We really don’t want to change it. Nor do we want to change the way in which the tables are stored by Alchemy since that will lead to problems as far as the polymorphism is concerned.

## Change the #define in SQLite since it is open source

We need to explore this. Before we do, we should spend more time thinking whether (since we have more than 32 joins) we’re doing something wrong or not.

Advertisements

One Response to “32 Join Limit in SQLite”

  1. The Third Bit » Blog Archive » Nick’s Last Day Says:

    […] problems that join limits in SQLite are causing (and the ways he’s tried to get around […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: