带有cfqueryparam的cfquery可以获取多长时间有限制？ [英] Is there a limit on how long a cfquery with cfqueryparam can get?

问题描述

我有一些代码紧密地插入数千条记录，最近，当我引入< cfqueryparam> 时，CF崩溃了。

I have some code that does a tight loop to insert thousands+ records and recently when I introduce <cfqueryparam>, CF crashes.

类似...

<cfquery>
  <cfloop query="qBars">
    INSERT INTO Foo 
    SET 
      xx = <cfqueryparam value="#qBars.aa#" sqltype="CF_SQL_VARCHAR">,
      yy = <cfqueryparam value="#qBars.bb#" sqltype="CF_SQL_INTEGER">
  </cfloop>
</cfquery>

以前，它很漂亮，没有< cfqueryparam> 。但是，使用cfqueryparam时，如果 qBars 的记录数很大（10,000+），可能会出现问题。

This used to work beautifully without <cfqueryparam>. With cfqueryparam however, I guess it might be problematic when the recordcount of qBars is large (10,000+).

现在...我该怎么办？重构要在数据库级别处理的整个事情？

Now... What shall I do? Refactor the whole thing to be handled in DB level?

谢谢

推荐答案

老实说，如果我需要将10k +记录加载到数据库中，则不会使用CFQUERY。您的数据库几乎肯定具有批量加载数据的能力，因此，建议对此进行调查。

To be honest, if I was needing to load 10k+ records into the DB, I would not use CFQUERY. Your DB will almost certainly have the capability to bulk load data, so I recommend investigating that.

我认为允许的最大绑定参数数量将是DB引擎，而不是通过CF或底层JDBC。但是您没有提到要使用哪个数据库，因此很难在那里找到答案。

I think the maximum number of bind parameters allowed would be a restriction set by the DB engine rather than by CF or the underlying JDBC. But you don't mention which DB you're using, so it's hard to research an answer for you there.

我四处窥探，发现此表适用于SQL服务器： http://msdn.microsoft.com/en-us/library/ms143432 .aspx 。它没有具体提到一个内联查询可以有多少个绑定参数，但是他们提到的proc或函数参数的2100的数字与我之前能够在列表中传递的参数的最大数目相同（例如WHERE IN子句）。我一直以为这是列表的最大大小，但实际上，实际上这是一般多少个参数的临界值。这对您来说很容易测试...用1005次迭代尝试循环，看看是否可行。然后尝试1006次迭代，我可能会期望它失败。

I did some snooping around and found this table for SQL Server: http://msdn.microsoft.com/en-us/library/ms143432.aspx. It does not specifically mention how many bind params an inline query can have, but the figure of 2100 they mention for params for a proc or function is the same as the maximum number of parameters I've been able to pass in a list before (like for a WHERE IN clause). I always thought it was the maximum size for a list, but perhaps it's actually the cut off for how many params in general. This would be easy for you to test... try your loop with 1005 iterations and see if it works. Then try it with 1006 iterations, and me might expect it to fail.

当然，如果您碰巧在SQL Server上...

That is, of course, if you happen to be on SQL Server...

另外，您说事情崩溃了，但是您没有说出错误是什么...当您询问此类信息时，将此类信息包括在内总是很有帮助的问题。

Also, you say the thing crashes, but you don't say what the error is... it's always helpful to include this sort of information when you're asking this sort of question.

这篇关于带有cfqueryparam的cfquery可以获取多长时间有限制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！