SQLite limitation - at most 64 tables in a join

[JoshG]

Hi There,

I'm currently using ActiveRecord->NHibernate->SQLite (in memory)

but I'm receiving an error when executing a query to fetch a
particular record on one of my tables. The error is as follows:

SQLite error
at most 64 tables in a join

I'm aware that joining 64 tables isn't necessarily a good thing,
however, I would like to know if there is a way to get NHibernate to
generate appropriate SQL to talk to SQLite and retrieve the required
information. Currently I imagine that this would count as a bug in
NHibernate's SQLite dialect...

Alternatively, if anyone has any ideas on how I could avoid the 64+
table join, I'd be interested, though that question should probably be
something for the ActiveRecord forum to address.

Thanks for your thoughts/help

Josh

[Richard Wilde]

Have you read this from http://www.sqlite.org/limits.html

Maximum Number Of Tables In A Join
SQLite does not support joins containing more than 64 tables. This limit arises from the fact that the SQLite code generator uses bitmaps with one bit per join-table in the query optimizer.
SQLite uses a very efficient O(N²) greedy algorithm for determining the order of tables in a join and so a large join can be prepared quickly. Hence, there is no mechanism to raise or lower the limit on the number of tables in a join.

Also as a side note I cannot imagine a query that I would need that joins such a large number of tables! 

HTH

Rippo

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[JoshG]

Yes Richard,

Thats why I am here.

I'm asking:
Since this is a documented limitation of SQLite and NHibernate is
supposed to hide the database implementation from us (as much as
possible), shouldn't a workaround be implemented inside NHibernate's
SQLite dialect?

Josh

On Aug 22, 3:37 pm, Richard Wilde <rich...@wildesoft.net> wrote:
> Have you read this fromhttp://www.sqlite.org/limits.html
>
> *Maximum Number Of Tables In A Join*

> SQLite does not support joins containing more than 64 tables. This limit
> arises from the fact that the SQLite code generator uses bitmaps with one
> bit per join-table in the query optimizer.
> SQLite uses a very efficient O(N²) greedy algorithm for determining the
> order of tables in a join and so a large join can be prepared quickly.
> Hence, there is no mechanism to raise or lower the limit on the number of
> tables in a join.
>
> Also as a side note I cannot imagine a query that I would need that joins
> such a large number of tables!
>
> HTH
> Rippo
>

[Gunnar Liljas]

The key here is "as much as possible". Even if there was a way to work around this issue in SQlite, the dialects don't have that kind of power. They work at quite a concrete SQL level, and can't really remodel the entire query strategy.

I suggest that you remodel the queries sent to NHibernate and perform necessary datashaping in your code to get the desired results.

/G

2011/8/25 JoshG <josh.gre...@inbilla.com>

[Darren Kopp]

look into getting rid of the 64 table join. Ideas as to how are hard without seeing your problem, but look into denormalizing the data into other tables for reporting.