在 SQlite 中执行超过 64 个表连接的最快方法是什么？
我计划为 SQLite 生成查询，该查询将涉及 12 个表的许多联接，这将超过 SQLite 中的 64 个表联接限制。 （~250 个表连接或可能更多）这最终将在 Android 上运行。其背后的目的是根据正在生成的报告在结果集中拥有 X 数量的用户定义字段。
不幸的是，我不是 DBA，我不知道实现这一目标的最佳方法。
到目前为止，我认为选项是：
- 使用 2 个临时表来处理结果集，同时加入可能的最大数量。 （我以前在 SQLServer 中的解决方案，相当慢）
- 生成几列的结果集和一个要连接的键并将它们存储在
n临时表中。 （其中n小于64）然后将所有临时表连接到它们的公共键上。 - 创建一个临时表并一次插入或更新一次填充它。
- 不要进行大连接，而是执行许多选择并填充某种数据容器。
还有什么我应该考虑的吗？
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
ntemp tables. (Wherenis 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（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.
我认为当您尝试连接那么多表时，您无法在任何关系数据库平台上获得“快速” - 任何类型的内置优化都会放弃幽灵。当我在查询中看到多达十个表时，我可能会检查我的设计。
我认为您的架构设计需要重新审视。一个架构中有 250 多个表（在手机上！）对我来说没有意义 - 我在一个包含 200+GB 数据的数据库中运行多个企业应用程序，但仍然只有 84 个表。我从来没有加入他们所有人。您的所有表格都有不同的列吗？真的不一样吗？您可以发布一些来自
sqlite_master的条目吗？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?由于您的应用程序在 Android 设备上运行，我猜它会与某处服务器上的企业级数据库同步。真正的解决方案是在设备数据库上生成服务器数据的非规范化表示，以便更容易访问。
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.