临时表资源限制
我有两个应用程序（服务器和客户端），它们使用 TQuery 通过 TDCOMConnection 与 TClientDataSet 连接， 在某些情况下，clientdataset 打开大约 300000 条记录，然后应用程序会抛出异常“临时表资源限制”。
有什么解决方法可以解决这个问题吗？ （除了“不要打开这么大的数据集”？）
更新：哎呀，很抱歉有 30 万条记录，而不是 300 万条。
i have two applications (server and client), that uses TQuery connected with TClientDataSet through TDCOMConnection,
and in some cases clientdataset opens about 300000 records and than application throws exception "Temporary table resource limit".
Is there any workaround how to fix this? (except "do not open such huge dataset"?)
update: oops i'm sorry there is 300K records, not 3 millions..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
该错误可能来自 TQuery 而不是 TClientDataSet。 当使用 TQuery 时，它会创建一个临时表，您可能会遇到这个限制。 然而，话虽如此，将 3,000,000 条记录加载到 TClientDataSet 中也是一个坏主意，因为它会尝试将每条记录加载到内存中 - 如果每条记录只有几个字节，这可能是可能的，但它可能仍然会杀死你的机器（显然每个 1kb 至少需要 3GB RAM）。
您应该尝试将数据分成更小的块。 如果是 TQuery 失败，这将意味着调整 SQL（更少的字段/更少的记录）或转移到更好的数据库（毕竟 BDE 有点累了）。
The error might be from the TQuery rather than the TClientDataSet. When using a TQuery it creates a temporary table and it might be this limit that you are hitting. However in saying this, loading 3,000,000 records into a TClientDataSet is a bad idea also as it will try to load every record into memory - which maybe possible if they are only a few bytes each but it is probably still going to kill your machine (obviously at 1kb each you are going to need 3GB of RAM minimum).
You should try to break your data into smaller chunks. If it is the TQuery failing this will mean adjusting the SQL (fewer fields / fewer records) or moving to a better database (the BDE is getting a little tired after all).
你已经有了答案。 不要在 ClientDataSet (CDS) 中打开如此庞大的数据集。
CDS 中的 300 万行是一个巨大的内存负载（根据每行的大小，它可能是巨大的）。
使用 CDS 的全部目的是快速处理可在内存中操作的小型数据集。 添加这么多行是荒谬的； 使用真实的数据集，或者重新设计一些东西，这样你就不需要一次检索这么多行。
You have the answer already. Don't open such a huge dataset in a ClientDataSet (CDS).
Three million rows in a CDS is a huge memory load (depending on the size of each row, it can be gigantic).
The whole purpose of using a CDS is to work quickly with small datasets that can be manipulated in memory. Adding that many rows is ridiculous; use a real dataset instead, or redesign things so you don't need to retrieve so many rows at a time.
超过 300 万条记录对于一次处理来说太多了。 我的猜测是您正在执行导出或类似的操作，这需要通过网络发送许多记录。 可以用来减少此问题的一种方法是让中间层生成导出文件，然后将该文件传送到客户端（最好首先使用 ZLIB 或类似的东西进行压缩）。
如果您将数据拉回客户端以供查看，请考虑仅发送摘要信息，然后允许客户端一次挖掘一部分数据。 用户会感谢你，因为你的表现会大幅提升，而且他们不必挖掘他们不关心的记录。
编辑
即使 300,000 条记录也难以一次处理。 如果你有那么多硬币，你能带得着吗？ 但如果你把它变成更大的面额，就可以了。 如果您将数据发送给客户以获取报告，那么我强烈建议采用汇总方法......给他们提供大局，让他们慢慢深入研究数据。 发送分组数据，然后让它们慢慢打开。
如果这是搜索结果界面，则设置返回记录数限制+1。例如要显示100条记录，则将限制设置为101。仍然只显示100，最后一条记录意味着有MORE more 100 条记录，因此客户需要调整搜索条件以返回较小的子集。
over 3 million records is way too much to handle at once. My guess is that you are performing an export or something like that which requires that many records to be sent down the wire. One method you could use to reduce this issue would be to have the middle-tier generate an export file, and then deliver that file to the client (preferably compressing first using ZLIB or something simular).
If you are pulling data back to the client for viewing purposes, then consider sending summary information only, and then allowing the client to dig thier way thru the data a portion at a time. The users would thank you because your performance will go way up and they won't have to dig thru records they don't care about looking at.
EDIT
Even 300,000 records is way too much to handle at once. If you had that many pennies, would you be able to carry them all? But if you made it into larger denominations, you could. if your sending data to the client for a report, then I strongly suggest a summary method... give them the large picture and let them drill slowly into the data. send grouped data and then let them open up slowly.
If this is a search results screen, then set a limit of the number of records to be returned + 1. For example to display 100 records, set the limit to 101. Still only display 100, the last record means that there were MORE than 100 records so the customer needs to adjust thier search criteria to return a smaller subset.
临时表资源限制不是对单个查询的限制。 这是所有开放查询的限制。 因此，这可能是您当时关闭所有其他查询的解决方案。
如果无法使用ADO连接，也可以设计分页机制，逐页查询数据。
祝你好运
Temporary table resource limit is not a limit for one single query. it is the limit for all open queries together. so it may be a solution for you to close all other queries at the time.
if it is not possible for you to use ADO connection, also you can design a paging mechanism for querying data page by page.
GOOD LUCK