Feb 11, 2020

The fastest way to create SharePoint list items

When you need to create many SharePoint list items, be sure to choose the fastest approach to do it.

Non-scientific research to the fastest way to create SharePoint list items

There are times when you need to create a lot of SharePoint list items. Whether it’s for migrating data or seeding a demo tenant. Finding the fastest way to do it, could save you a lot of time.

There are different ways to create SharePoint list items, from using the REST API to using the Microsoft Graph. And surprisingly, the method that’s the fastest is the one you’d not expect.

To find out which is the fastest way to create list items in SharePoint, I devised a simple test. I wanted to create 100 list items, all consisting of just the Title. In my test, I’d compare the times to create items using the following approaches:

SharePoint REST API
SharePoint REST API v2 (also known as Vroom)
Microsoft Graph
SharePoint REST API batched
Microsoft Graph batched

In my tests, I omitted using SharePoint CSOM since it requires an SDK and doesn’t run PowerShell core which I used to run the tests. Since CSOM is a wrapper around SharePoint APIs, it’s improbable that it would be faster than calling REST directly, but for completeness, you could have a look at it as well.

I’ve compared the different execution methods in the same time frame, running on the same computer. I ran the tests a few times to verify that a particular result was not an anomaly. Here is what I’ve got.

SharePoint REST

I’ve tested creating list items using the SharePoint REST API with the following script:

$token = "abc"
$stoken = ConvertTo-SecureString -String $token -AsPlainText -Force

Measure-Command {
  for ($i = 0; $i -lt 100; $i++) {
    $item = "Item $($i+1)"
    Write-Host $item

    Invoke-WebRequest "https://contoso.sharepoint.com/_api/web/lists/getByTitle(%27Items%27)/AddValidateUpdateItemUsingPath()" -Authentication Bearer -Token $stoken -Method Post -Body "{""formValues"":[{""FieldName"":""Title"",""FieldValue"":""$item""}]}" -Headers @{ "content-type" = "application/json;odata=nometadata" }
  }
}

SharePoint REST v2 (Vroom)

To test the SharePoint REST v2 API, also known as Vroom, I’ve adjusted the script to the following:

$token = "abc"
$stoken = ConvertTo-SecureString -String $token -AsPlainText -Force

Measure-Command {
  for ($i = 0; $i -lt 100; $i++) {
    $item = "Item $($i+1)"
    Write-Host $item

    Invoke-WebRequest "https://contoso.sharepoint.com/_api/v2.0/sites/root/lists/cd6f43ff-e88e-415f-b283-b035e8f79f4e/items" -Authentication Bearer -Token $stoken -Method Post -Body "{""fields"":{""Title"":""$item""}}" -Headers @{ "content-type" = "application/json" }
  }
}

Microsoft Graph

Another way to create list items in SharePoint is by using the Microsoft Graph, which will then relay the request to SharePoint REST API v2. Here is the script I used to test it:

$token = "abc"
$stoken = ConvertTo-SecureString -String $token -AsPlainText -Force

Measure-Command {
  for ($i = 0; $i -lt 100; $i++) {
    $item = "Item $($i+1)"
    Write-Host $item

    Invoke-WebRequest "https://graph.microsoft.com/v1.0/sites/root/lists/cd6f43ff-e88e-415f-b283-b035e8f79f4e/items" -Authentication Bearer -Token $stoken -Method Post -Body "{""fields"":{""Title"":""$item""}}" -Headers @{ "content-type" = "application/json" }
  }
}

SharePoint REST API batched

SharePoint Online supports batching requests. Depending on what you need to accomplish, batching can help you to minimize the number of web requests that you need to execute. Creating batched requests manually is pretty complicated so ideally, you’d use an SDK for it, but for this test, I was interest in the performance of the API rather than a particular SDK, so I used the following script to test it:

$token = "abc"
$stoken = ConvertTo-SecureString -String $token -AsPlainText -Force

Measure-Command {
  $r = @()
  $batchId = 'batch_e3b6819b-13c3-43bb-85b2-24b14122fed1'

  for ($i = 0; $i -lt 100; $i++) {
    $item = "Item $($i+1)"
    Write-Host $item

    $changeset = "changeset_$([Guid]::NewGuid().ToString())"
    $r +=
"--$batchId
Content-Type: multipart/mixed; boundary=$changeset

--$changeset
Content-Type: application/http
Content-Transfer-Encoding: binary

POST https://contoso.sharepoint.com/_api/web/lists/getByTitle(%27Items%27)/AddValidateUpdateItemUsingPath() HTTP/1.1
Content-Type: application/json;odata=nometadata

{""formValues"":[{""FieldName"":""Title"",""FieldValue"":""$item""}]}

--$($changeset)--

"
    if ($r.Count -eq 20) {
      $r += "--$batchId--"
      $payload = $r -join ''
      Invoke-WebRequest 'https://contoso.sharepoint.com/_api/$batch' -Authentication Bearer -Token $stoken -Method Post -Body $payload -Headers @{ "content-type" = "multipart/mixed; boundary=$batchId" }
      $r = @()
    }
  }
}

In my test, I used batches of 20 and 100 items and the results didn’t show much difference.

Microsoft Graph batched

Microsoft Graph supports batching requests as well. Comparing to batching of SharePoint REST API requests, batching in Microsoft Graph is significantly easier to use, even when you build your requests manually. Here is the script that I used to test it:

$token = "abc"
$stoken = ConvertTo-SecureString -String $token -AsPlainText -Force

Measure-Command {
  $r = @{ requests = @() }

  for ($i = 0; $i -lt 100; $i++) {
    $item = "Item $($i+1)"
    Write-Host $item

    $r.requests += @{
      id = $i+1
      url = "sites/root/lists/cd6f43ff-e88e-415f-b283-b035e8f79f4e/items"
      method = "POST"
      body = @{ fields = @{ Title = "$item" } }
      headers = @{ "content-type" = "application/json" }
    }
    if ($r.requests.Count -eq 20) {
      $payload = ConvertTo-Json $r -Depth 4
      Invoke-WebRequest 'https://graph.microsoft.com/v1.0/$batch' -Authentication Bearer -Token $stoken -Method Post -Body $payload -Headers @{ "content-type" = "application/json" }
      $r.requests = @()
    }
  }
}

Results

The following are the results of my tests sorted ascending by the total duration. When looking at the duration, you shouldn’t focus on the number of seconds creating items using the particular API took exactly, but instead, compare the relative results of the different methods.

Method	Duration (sec)
Microsoft Graph batched	4
SharePoint REST API batched	5,5
SharePoint REST	28,5
SharePoint REST v2 (Vroom)	34
Microsoft Graph	50

SharePoint REST API showed an average speed comparing to other methods of creating list items.

SharePoint REST API v2 turned out to be slower than v1. You would expect the new API to be more optimized, but perhaps its optimizations are related to matching the specifications of the Microsoft Graph rather than performance.

Creating list items using the Microsoft Graph took 1,75x as long as when using the SharePoint REST API. Microsoft Graph doesn’t execute its requests itself and relays them to the corresponding services. So behind the scenes, for creating SharePoint list items, it calls the SharePoint REST API v2. It’s unclear why the routing of requests to SharePoint would have such a big performance impact.

Unsurprisingly, batching requests turned out to have significant performance benefits. But here’s something I didn’t expect. Where the Microsoft Graph was the slowest when creating items one by one, it turned out to be the fastest when creating items in batches! Batching using the SharePoint REST API came in a close second also clearly proving the benefits of batching.

Conclusion

When you need to create many list items, you should highly consider batching. The test I ran, showed a clear performance benefit of batching comparing to creating items one by one. If you can’t use the Microsoft Graph, you should consider using an SDK for the SharePoint API as manually crafting and processing batched requests is rather cumbersome.

While Microsoft Graph wasn’t the fastest to create the test list items one by one, it’s still a key tool in Microsoft 365 developer’s toolbox. Modern solutions often span multiple services in Microsoft 365 and Microsoft Graph provides you with a consistent way to traverse the different resources. In most cases, the authentication and usage convenience heavily outweigh the little performance impact using it has on your solution so don’t disregard it for the little performance overhead using it adds.