你曾经遇到过一个查询，SQL Server无法执行，因为它引用了太多的表？ [英] Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?

问题描述

您是否曾看过任何错误讯息？

Have you ever seen any of there error messages?

- SQL Server 2000

-- SQL Server 2000

无法为视图或函数解析分配辅助表。

超出了查询中的最大表数（256）。

Could not allocate ancillary table for view or function resolution.
The maximum number of tables in a query (256) was exceeded.

- SQL Server 2005

-- SQL Server 2005

查询中的表名过多。最大允许值为256.

Too many table names in the query. The maximum allowable is 256.

如果是，您做了什么？

给定？确信客户简化他们的要求？数据库不规范化？

Given up? Convinced the customer to simplify their demands? Denormalized the database?

@（每个人都希望我发布查询）：

@(everyone wanting me to post the query):

1. 我不确定是否可以在答案编辑窗口中粘贴70千字节的代码。


2. 即使我可以因为这70千字节的代码将引用20或30的意见，我也将必须张贴，否则代码将无意义。

我不想听起来像我在这里吹嘘，但问题不在于查询。查询是最优的（或至少几乎是最优的）。我花了无数的时间优化它们，寻找每一列和每一个可以删除的表。想象一下，一个报表有200或300列，必须填充一个SELECT语句（因为这是几年前的设计，当它仍然是一个小报告）。

I don't want to sound like I am boasting here but the problem is not in the queries. The queries are optimal (or at least almost optimal). I have spent countless hours optimizing them, looking for every single column and every single table that can be removed. Imagine a report that has 200 or 300 columns that has to be filled with a single SELECT statement (because that's how it was designed a few years ago when it was still a small report).

推荐答案

对于SQL Server 2005，我建议使用表变量和部分构建数据。

For SQL Server 2005, I'd recommend using table variables and partially building the data as you go.

为此，请创建一个表变量，表示要发送给用户的最终结果集。

To do this, create a table variable that represents your final result set you want to send to the user.

然后找到你的主表（比如你上面的例子中的订单表），并拉出这些数据，加上一点补充数据，只能说一次加入（客户名称，产品名称）。你可以做一个SELECT INTO把它直接放入你的表变量。

Then find your primary table (say the orders table in your example above) and pull that data, plus a bit of supplementary data that is only say one join away (customer name, product name). You can do a SELECT INTO to put this straight into your table variable.

从这里，遍历表，并为每一行，做一堆小的SELECT查询检索您的结果集所需的所有补充数据。

From there, iterate through the table and for each row, do a bunch of small SELECT queries that retrieves all the supplemental data you need for your result set. Insert these into each column as you go.

完成后，您可以从表变量中执行一个简单的SELECT *，并将此结果集返回给用户。

Once complete, you can then do a simple SELECT * from your table variable and return this result set to the user.

我没有任何硬数字，但有三个不同的实例，我已经工作到目前为止，这些小查询实际上比工作更快大量选择查询与一堆连接。

I don't have any hard numbers for this, but there have been three distinct instances that I have worked on to date where doing these smaller queries has actually worked faster than doing one massive select query with a bunch of joins.

这篇关于你曾经遇到过一个查询，SQL Server无法执行，因为它引用了太多的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！