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

Question

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.

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.

$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 ...
  ");
}

tiny example would be nice – Roman Pekar Nov 19 '13 at 11:07 — Roman Pekar, Nov 19 '13 at 11:07

score 0 · Answer 1 · answered Nov 19 '13 at 11:28

0

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

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

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

answered Nov 19 '13 at 11:28

Brendan Hill

3,406
4
32
61

I'm using php w/ sqlsrv ext. The problem is that the xml files are huge. I'm talking 50-60k queries in total. I parse every hour, on a scheduled service. it takes a few minutes between fetching xml, and parsing, mostly fetching. Each xml file contains 60-70 queries that must be executed. So, the idea was should I send these 60-70 queries together or one at a time. – user1695981 Nov 19 '13 at 11:34
Yes, batching them will give you some improvement. Presumably PHP has some string builder utility. – Brendan Hill Nov 20 '13 at 12:18

score 0 · Answer 2 · edited May 23 '17 at 11:56

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

Multiple INSERT statements vs. single INSERT with multiple VALUES

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

2 Answers2