Multiple small queries, or one big query while storing xml data into SQL Server

I'm parsing an xml file which I loop through and store information into a SQL Server. I send a MERGE query to either insert or update information.

我正在解析一个xml文件,我循环并将信息存储到SQL Server中。我发送MERGE查询以插入或更新信息。

Is it best to store this information in a variable, and send query after the loop has finished, or send numerous small queries within the loop? I expect 60-100 queries for each loop.

是否最好将此信息存储在变量中,并在循环结束后发送查询,或在循环内发送大量小查询?我希望每个循环有60-100个查询。

$DOM=simplexml_load_file($url);
$info=$DOM->info;
foreach($info as $i){
  $i_name=$i['name'];
  $i_id=$i['id'];
  ...
  $q=sqlsrv_query($conn,"
    MERGE dbo.members m USING (
      SELECT 
        '$i_name' as name,
        '$i_id' as id,
         ...
    ) s ON ( m.id=s.id ) 
      WHEN MATCHED THEN 
        UPDATE SET ...
      WHEN NOT MATCHED THEN
        INSERT ...
  ");
}

2 个解决方案

#1

My experience is that the best performance comes from batching the SQL statements several hundred at a time.

我的经验是,最好的性能来自于一次批量处理几百个SQL语句。

Hopefully the language your own (php? perl? can't tell) has a utility for this, otherwise you can easily code it up yourself.

希望你自己的语言(php?perl?无法分辨)有一个实用工具,否则你可以自己编写代码。

Of course, if your DB is on the same machine it probably makes no difference.

当然,如果你的数据库在同一台机器上,它可能没什么区别。

#2

It depends on various factors. You could setup a test scenario and check the performance of both options, then choose whatever is better for your case. We had a similar case and best option was to have a stored procedure that received a table with all the needed values.

这取决于各种因素。您可以设置测试场景并检查两个选项的性能,然后选择更适合您情况的选项。我们有一个类似的案例,最好的选择是让一个存储过程收到一个包含所有需要值的表。

Check this other similar questions, they are not exactly same as yours but I believe the answers given there will help you a lot.

检查其他类似的问题,它们与你的不完全相同,但我相信那里给出的答案会对你有很大的帮助。

Update or Merge very big tables in SQL Server

在SQL Server中更新或合并非常大的表

Multiple INSERT statements vs. single INSERT with multiple VALUES

多个INSERT语句与具有多个VALUES的单个INSERT

#1