TClientDataSet和大批量插入

Question

在我的应用程序中，我使用带有select (MSSQL)的TADOQuery，并将其链接到TClientDataSet。我需要插入大约一百万条记录并ApplyUpdates。

那么我在SQL Server Profiler中看到了什么？我发现对于每个插入的行，我们都有3个查询：插入脚本的sp_prepare，带有某些值的sp_execute以及sp_unprepare。

我只想为所有记录准备sql一次，然后再取消准备。我该怎么做？

补充说明：

在查询中，我有一个用于执行存储过程的脚本：

tmpQuery := DefineQuery(FConnection, [
  'exec up_getOperatorDataSet ',
  '  @tablename     = :tablename, ',
  '  @operator      = :operator, ',
  '  @forappend     = :forappend, ',
  '  @withlinksonly = :withlinksonly, ',
  '  @ids           = :ids '
], [
  Param(ftString, sTableName),
  Param(ftInteger, FOperatorId),
  Param(ftBoolean, opForAppendOnly in OpenParams),
  Param(ftBoolean, opOnlyWithModelLinks in OpenParams),
  Param(ftString, sIds)
], Result);

它从表sTableName中选择所有字段，并带有一些参数。

从分析器中插入的示例：

步骤1：

declare @p1 int
set @p1=486
exec sp_prepare @p1 output,N'@P1 int,@P2 int,@P3 datetime,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 varchar(128),@P10 bit,@P11 numeric(19,4),@P12 smallint,@P13 smallint,@P14 smallint,@P15 smallint',N'insert into parser_prices
  (operator_id, request_id, date, nights, model_hotel_id, model_meal_id, model_room_id, model_htplace_id, spo, hotelstop, price, frout_econom, frout_business, frback_econom, frback_business)
values
  (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15)
',1
select @p1

步骤2：

exec sp_execute 486,21,2000450,'2009-12-04 00:00:00',14,2118,22,-9555,18,'2009-10.MSK.Bali.13.10.09-27.03.10',0,15530.0000,3,3,3,3

步骤3：

exec sp_unprepare 486

这是针对所有新行的。

- silent

---

我们能看到一个脚本的例子吗？如果您能给它参数化，那么您可以预先准备好它，然后每次重新执行之前只需更改参数即可。 - J__

Answer 1

因为您正在调用存储过程，而不是在代码中使用内联查询，所以SQL Server将每次对存储过程的调用视为单独的调用，并因此每次都会准备和取消准备。我不确定是否有解决这个问题的方法。

如果存储过程中发生的任何事情可以通过代码中的查询完成，则可以使用以下结构，该结构仅在第一次准备SQL语句时进行准备：

{Prepare the insert query}
ADOQuery1.SQL.Append('INSERT INTO Tablename');
ADOQuery1.SQL.Append('(StringField1, IntField2)');             {repeat as necessary}
ADOQuery1.SQL.Append('VALUES (:sFieldValue1, :sFieldValue2)'); {repeat as necessary}
ADOQuery1.SQL.Prepare;

{In a For, While, Repeat loop, use:}
ADOQuery1.ParamByName('sFieldValue1').AsString := 'Value for field 1';
ADOQuery1.ParamByName('sFieldValue2').AsInteger := 2;
ADOQuery1.ExecSQL;

如果我没有正确地使用ADOQuery组件的属性和方法名称，那么请原谅，因为我现在不在我的Delphi PC上，而且我通常不使用TADO组件，但是这个概念仍然适用于TDataSet概念。

- J__

---

是的，这正是我所需要的，但要自动化 :) - silent

你所说的自动是什么意思？你是指它需要在数据库触发器中吗？ - J__

自动化意味着我希望组件能够生成正确的查询，而无需创建额外的对象。 - silent

很抱歉，我仍然不确定我理解了。您说您正在使用TADOQuery，因此我只是使用该类型的组件来准备查询。然后，您可以设置参数值并在循环中执行查询。 - J__

服务器端（TADOQuery）->客户端（TClientDataSet）。当我在TClientDataSet中更改某些内容并应用更新时，它会自动将更改发送到服务器端，并且类似于DataSetProvider或AdoQuery的东西会自动准备此插入脚本。您的解决方案是在服务器端处理更改并手动执行准备好的查询。 - silent

Answer 2

思考...

1. 你不需要准备存储过程调用。实际上，它已经准备好了。在大多数客户端实现中，你可以关闭它。

2. 你可能无法一次处理一百万行数据。你有一个批量大小限制（例如单个DB调用）为256 MB（假设默认4k网络数据包）。

3. 在其他客户端实现中，你可以设置“批量大小"（与第2点的概念不同），比如10000，这样你只需要进行100次调用而不是一百万次。

- gbn

---

1. 我不准备我的选择
2. 我知道
3. 那又怎样？ :)

- silent

@silent：1. 你正在准备，因为SQL Profiler显示它：它可能是自动的，但通常可以关闭。3. 你能否批量调用...毕竟，这是你问题的重点，不是吗？ - gbn

使用TSQLResolver在Provider中生成了Insert SQL语句，而不是由我手动编写。我进行了调试并发现，在通过TADOCommand执行之前它没有prepared标志。 - silent

Answer 3

我认为其他答案可能有助于调整性能，尽管我认为您使用的访问TClientDataSet的方法并不重要，因为无论哪种方式，实际的数据库更新都是分离的（并自动生成的）。

如果每行更新作为单独的准备，则似乎这是Borland的一个糟糕的设计选择，因为显然更新多行将需要相同的查询，只是每次具有不同的参数。

另一方面，TClientDataSet是针对内存数据库而设计的，这意味着相对较小的数据库。使用类似一百万行的东西可能超出了预期的用例。

另一方面，在此时从应用程序中替换ClientDataSet可能会麻烦。我会说，在您的应用程序的性能敏感部分，我会跟踪修改的行并使用上述例程编写手动更新程序。除此之外，您可以尝试修改TClientDataSet的源代码以使其更有效，或者子类化它并覆盖应用更改的方法。

（就我个人而言，我在我的程序中使用SQLite3进行存储，因此ClientDataSet用处不大，我也没有太多玩过它）。

- Noah

Answer 4

答案在TADOConnection中使用的提供程序中。现在从MSDASQL切换到SQLOLEDB，一切都正常了，不需要任何额外的查询。

- silent