Inserting multiple items at once in Azure Mobile Services

Article
01/02/2013

With the Azure Mobile Services client SDK, it’s fairly simple to insert an item into a table in Azure. Inserting multiple items can be also done fairly easily, simply inserting one at a time. But there are some scenarios where we want to actually do multiple insertions at once, to minimize the number of networking requests made from the client to the service. In the E2E Test Application that we have for our iOS client, the first thing we do in our query tests is to pre-populate a table with some data (if the table was empty), and since we don’t that to affect the test performance (we use about 250 items), we decided to insert them all at once. This has also been asked in our forums, so I decided to post how I went out to implement it.

The idea is fairly simple: instead of sending one item, we send a list of items which we want to be inserted. At the server side, instead of letting the default behavior for the insert operation kick in, we instead loop through the items and insert them one-by-one. Since the database and the instance running the mobile services are often located in the same datacenter, the latency between the two components is a lot smaller than from the client making the insert call and the mobile service, so we have some significant performance gains. And by having the logic to do multiple insertions at the server side, we don’t need to implement that at the client (doing it in managed code is trivial with the async / await keywords; doing that in iOS or in JavaScript not so much).

The way that I chose to implement the logic was that the service would receive not an array of items directly, but an object with one of its members being the array to be inserted. That’s easier to implement in the managed client (using an IDataMemberJsonConverter instead of an ICustomMobileServiceTableSerialization). For JavaScript it really doesn’t matter, since we’re dealing with JS objects in either case. And for the iOS client, we currently can only do that, since the insert operation only takes a dictionary (not an array) as an argument.

Let’s look at the server script. In this example, we’re adding populating our table called ‘iosMovies’, and the data to be inserted is an array in the “movies” property of the received item. The first thing we do is to check whether we actually need to do the insertion (that’s something specific to the scenario of my app; maybe in your case you always want to do the insertion, so that step won’t be necessary). By reading only the first element of the table, we can check whether the table is populated or not (since this script runs in the “insert’ operation, we assume that the table is either fully populated or it needs the items to be inserted). If the data is already there, we respond with an appropriate status. Otherwise, we proceed with the multiple insert operations.

function insert(item, user, request) {
var table = tables.getTable('iosMovies');
table.take(1).read({
success: function (items) {
if (items.length > 0) {
// table already populated
request.respond(200, { id: 1, status: 'Already populated' });
} else {
// Need to populate the table
populateTable(table, request, item.movies);
}
}
});
}

Populating the table with multiple items is done by looping through the items to be inserted, and inserting them one by one (*). Since the insert operation in the table object is asynchronous, we can’t use a simple sequential loop; instead, we’ll insert each element when the callback for the previous one is called. After the last one is inserted, we can then respond to the client with an appropriate status.

There’s one extra step that we had to do in this example, which caught me by surprise at first – updating a field of type Date (or DateTime in the managed client, or NSDate in the iOS client). When I first ran that code, the table was created, and the data was seemingly correct. But when I checked the type of the columns in the database, my field ‘ReleaseDate’ had been translated into a column of type ‘string’. ‘datetime‘ is a supported primitive type in Azure Mobile Services, so I was expecting the column to have the appropriate value. The problem is that dates and strings are transmitted using the same type in JSON, string (there’s no such a thing as a “JSON date”). If a string value arrives with a specific format (the ISO 8601 date format, with millisecond precision), then the Mobile Service runtime converts that value into a JavaScript Date object. However, that conversion is only done in top-level objects – it doesn’t traverse the whole incoming object graph (for performance reasons), so in my case my “date” value ended up as a normal string. To fix that, prior to inserting the data we fixed that by converting the ‘ReleaseDate’ field in in the input array.

function populateTable(table, request, films) {
var index = 0;
films.forEach(changeReleaseDate);
var insertNext = function () {
if (index >= films.length) {
request.respond(201, { id: 1, status: 'Table populated successfully' });
} else {
var toInsert = films[index];
table.insert(toInsert, {
success: function () {
index++;
if ((index % 20) === 0) {
console.log('Inserted %d items', index);
}
insertNext();
}
});
}
};
insertNext();
}
function changeReleaseDate(obj) {
var releaseDate = obj.ReleaseDate;
if (typeof releaseDate === 'string') {
releaseDate = new Date(releaseDate);
obj.ReleaseDate = releaseDate;
}
}

So that’s the script for the server. Now for the client code. Let’s look at the flavors we have. First, managed code, where we have the classes for that data:

[DataTable(Name = "w8Movies")]
public class AllMovies
{
[DataMember(Name = "id")]
public int Id { get; set; }
[DataMember(Name = "status")]
public string Status { get; set; }
[DataMember(Name = "movies")]
[DataMemberJsonConverter(ConverterType = typeof(MovieArrayConverter))]
public Movie[] Movies { get; set; }
}
public class MovieArrayConverter : IDataMemberJsonConverter
{
public object ConvertFromJson(IJsonValue value)
{
// unused
return null;
}
public IJsonValue ConvertToJson(object instance)
{
Movie[] movies = (Movie[])instance;
JsonArray result = new JsonArray();
foreach (var movie in movies)
{
result.Add(MobileServiceTableSerializer.Serialize(movie));
}
return result;
}
}
[DataTable(Name = "w8Movies")]
public class Movie
{
public int Id { get; set; }
public string Title { get; set; }
public int Duration { get; set; }
public DateTime ReleaseDate { get; set; }
public int Year { get; set; }
public bool BestPictureWinner { get; set; }
public string Rating { get; set; }
}

And to insert multiple movies at once, we create an instance of our type which holds the array, and call insert on that object:

Func<int, int, int, DateTime> createDate =
(y, m, d) => new DateTime(y, m, d, 0, 0, 0, DateTimeKind.Utc);
AllMovies allMovies = new AllMovies
{
Movies = new Movie[]
{
new Movie {
BestPictureWinner = false,
Duration = 142, Rating = "R",
ReleaseDate = createDate(1994, 10, 14),
Title = "The Shawshank Redemption",
Year = 1994 },
new Movie {
BestPictureWinner = true,
Duration = 175, Rating = "R",
ReleaseDate = createDate(1972, 3, 24),
Title = "The Godfather",
Year = 1972 },
new Movie {
BestPictureWinner = true,
Duration = 200, Rating = "R",
ReleaseDate = createDate(1974, 12, 20),
Title = "The Godfather: Part II",
Year = 1974 },
new Movie {
BestPictureWinner = false,
Duration = 168, Rating = "R",
ReleaseDate = createDate(1994, 10, 14),
Title = "Pulp Fiction",
Year = 1994 },
}
};
try
{
var table = MobileService.GetTable<AllMovies>();
await table.InsertAsync(allMovies);
AddToDebug("Status: {0}", allMovies.Status);
}
catch (Exception ex)
{
AddToDebug("Error: {0}", ex);
}

For Objective-C, we don’t need to create the types, so we can use the NSDictionary and NSArray classes directly. The implementation of the ‘getMovies’ method can be found in our GitHub repository.

NSArray *movies = [ZumoQueryTestData getMovies];
NSDictionary *item = @{@"movies" : movies};
MSTable *table = [client getTable:queryTestsTableName];
[table insert:item completion:^(NSDictionary *item, NSError *error) {
if (error) {
NSLog(@"Error populating table: %@", error);
} else {
NSLog(@"Table is populated and ready for query tests");
}
}];

Similarly for JavaScript, we can just use “regular” objects and arrays to insert the data:

var table = client.getTable('w8Movies');
var allMovies = {
movies: [
{
BestPictureWinner: false,
Duration: 142,
Rating: "R",
ReleaseDate: new Date(Date.UTC(1994, 10, 14)),
Title: "The Shawshank Redemption",
Year: 1994
},
{
BestPictureWinner: true,
Duration: 175,
Rating: "R",
ReleaseDate: new Date(Date.UTC(1972, 3, 24)),
Title: "The Godfather",
Year: 1972
},
{
BestPictureWinner: true,
Duration: 200,
Rating: "R",
ReleaseDate: new Date(Date.UTC(1974, 12, 20)),
Title: "The Godfather: Part II",
Year: 1974
},
{
BestPictureWinner: false,
Duration: 168,
Rating: "R",
ReleaseDate: new Date(Date.UTC(1994, 10, 14)),
Title: "Pulp Fiction",
Year: 1994
}
]
};
table.insert(allMovies).done(function (inserted) {
document.getElementById('result').innerText = inserted.status || allMovies.status;
});

That’s about it. This is one of the ways we have to prevent multiple networking requests between the client and the mobile service to perform multiple insertions at once.

(*) Bonus info: As I mentioned before, this still does multiple calls, between the mobile service and the database, but since they’re co-located, the latency is small. You also can make really only one call, from the service to the database, by using the mssql object, and creating one insert request for multiple rows at once. You’d need to do the translation between the data types and the SQL expression yourself, and use the “union all” trick to create a temporary table and insert from that table – see an example below. Notice that you’d also need to create the table columns (if they don’t exist yet), since the dynamic schema feature doesn’t work with the mssql object – once you go down to that level, you’re in full control of the database communication.

INSERT INTO w8Movies (Title, [Year], Duration, BestPictureWinner, Rating, ReleaseDate)
SELECT 'The Shawshank Redemption', 1994, 142, FALSE, 'R', '1994-10-14'
UNION ALL
SELECT 'The Godfather', 1972, 175, TRUE, 'R', '1972-03-24'
UNION ALL
SELECT 'The Godfather: Part II', 1974, 200, TRUE, 'R', '1974-12-20'
UNION ALL
SELECT 'Pulp Fiction', 1994, 168, TRUE, 'R', '1994-10-14'

So which one to use? I really didn’t see much advantage in going full SQL for my scenario. Once a communication between the mobile service and the database is successful, chances are that over the next second or so the subsequent ones will be as well, so doing ~250 insert operations, although not really an atomic transaction, has a very good chance of being one. As usual, it may vary according to the scenario of your application.

Inserting multiple items at once in Azure Mobile Services

Additional resources