<< Go Back

Server Script To Insert Table Items In Windows Azure Mobile Services

- 20 Mar 2013

I needed a script to load a database with dummy items. I created a Scheduler script with a simple loop to insert 1000 items into my table. I found that when I ran this script it would kick off and seemingly never complete. What was worse, my service was returning a 500 internal server error when I tried to hit it!

The underlying problem is that a Mobile Service running in free mode (the default configuration) throttles the number of connections that can be made and in a tight loop, you can quickly exceed that and put the service in a bad state. The solution is to wait for previous inserts to complete before inserting new items. You can safely insert a batch of items (say 10) and once that is complete submit another batch of insert operations to avoid this connection limit.

Let’s create a script, step by step and discuss it so you can apply the same logic to your situation.

In my example I am using Scheduler but you may have similar logic in an Insert or Update script.

The problem script

First the problem script (THIS WILL NOT WORK – DO NOT RUN THIS SCRIPT):

The issue is that line 11 executes asynchronously and we quickly run out of connections!

Let’s discuss a scheme to work around this async behavior and connection limit.

Building a script to solve the issue

In this script we will insert the same 1000 records but do this in batches of 10. When we are done inserting the batch of 10, we will kick off another batch of 10 until we have completed the task.

I changed the script and started out by defining some variables and calling a yet to be defined function ‘insertItems’

Note the comments and what we are using them for!

Next we will define the ‘insertItems’ function. Note that this function only inserts 10 records (the value I chose for batchSize)! We will need to do some more work to kick off the next batch of inserts! The insert method call on the table takes an Object as the second parameter. This object defines two members: success: and error:. I set each of these members to point to functions that will be called on success or error from the insert operation on the table.

Now we need to actually add the success and error callback functions. The first (insertComplete) is called if there were no errors during the insert. It will have the logic to see if we have completed the current batch. If we have completed inserting the records in the batch and have more records to process, we will call insertItems again to process the next batch. To track the number of items we have completed in the batch we also define a variable to track that! The second (errorHandler) will keep track of the error count then call insertComplete to use it’s logic for determining if we need to kick off a new batch or not.

Note: You have to define the success and error functions before you use them in the insert function. If you do not you will get an error similar to this:

Error in script ‘/scheduler/testLoad.js’. Error: Invalid callback options passed to ‘insert’. Callback options must be an object with at least one ‘success’ or ‘error’ property of type ‘function’.

Putting it all together

Assembling all of these pieces you can now test the script. If it is a Scheduler script you can configure it as an ‘On demand’ script and run once. Then check the log to ensure your records were processed. Any script errors will show up in the Mobile Services log as well.

Once you develop your script, you need to TEST, TEST and then of course TEST AGAIN. Check the boundary conditions (0 records, 1 record, 999 records) for whatever is inside and outside your expected conditions. Along with what I have discussed, you need to adapt this logic to your situation. For example, if you are not generating dummy data this won’t work for you! You will be able to study the logic however and apply it to your situation. The key parts are the success and error functions that allow you to detect when you have processed the current batch and how to kick off the next batch. You will also need to determine when you have reached the end of your processing to stop batching and log the results!

Complete Code Listing

You should type all of this in yourself so you don’t become a cut and paste programmer. However… I do it too Winking smile

Special thanks to Paul Batum who helped me with this problem!

Code listing for sample (Copy Code):

</p>

function testLoad(){
    //Write to my service log 
    console.info("Running 'PrePopulateTable' job.");
    // The table we will be inserting into 
    var todoTable = tables.getTable('TodoItem');
    // The number of records to insert at a time (to avoid blowing our connection limit) 
    var batchSize = 10;
    // The number of records I want to generate dummy data for 
    var recordsToInsert = 1000;
    // The number of records processed 
    var totalCount = 0;
    // The number of insert failure 
    var errorCount = 0;

    // Start inserting items! 
    insertItems();

    // define the insertItems function 
    function insertItems() {
        // local var shared with the insertComplete callback function. 
        // This is scoped to this function so if the function is called again 
        // it will be reset to 0 
        var batchCompletedCount = 0;

        // insertComplete is called for each record inserted into the table 
        // called when the insert completes async. Also called by the errorHandler callback 
        var insertComplete = function() {
            // increment the number of records we have completed the insert call for 
            batchCompletedCount++;
            // increment the total number of records processed 
            totalCount++;
            // if we have completed all of the records in this current batch 
            // or don't have any more records to process we either... 
            if(batchCompletedCount === batchSize || totalCount === recordsToInsert) {
                if(totalCount < recordsToInsert) {
                    insertItems(); // kick off the next batch 
                } else {
                    // or we are done 
                    // report the status of the job to the log and don't do any more processing 
                    console.log("Job 'PrePopulateTable' complete. %d Records processed. There were %d errors.", totalCount, errorCount);
                }
            }
        };
        // called if there was an error doing the insert 
        var errorHandler = function(err) {
            // I want to track the number of errors for reporting, 
            // you may want to do something different here 
            errorCount++;
            console.warn("Ignoring insert failure as part of batch.", err);
            // call insertComplete simply to use the logic to track the batch count, 
            // total count and kick off the next batch if necessary. 
            // you may want to do something different! 
            insertComplete();
        };
             // insert some items! 
        for(var i = 0; i < batchSize; i++) {
            // num is used to generate some unique dummy data 
            var num = totalCount + i;
            // dummy data item to insert 
            var item = { text: "This is Item number: " + num };
            //This table insert completes async so we have a success and error callback set of functions 
            // defined that will be called when the insert completes 
            todoTable.insert(item, {
                success: insertComplete,
                error: errorHandler
            });
        }
    }
}