Can SubSonic Scale

Contents

Summary

When you discuss scalability, you're not talking about speed - you're talking about availability and "the lack of curve" on the requests vs. response time chart. Ideally you want your response time to remain the same as your users go up - offering the golden "flat line" that means your site will scale. This flat line is achieved through various means and is in itself a hotly-debated topic.

There's a lot to this - server speed, number of servers, caching, indexing of the DB, etc. As many of you know, this is a very large topic.

Rather than discuss how applications should scale - I'll keep this focused on what you can expect from your data access.

What is Scaling?

Right off the bat I might suggest that the size of your DB has nothing to do with scaling - but "operations per second" - or something to that effect - in other words a very high traffic site that punches a mess of data in and out of the DB.

Test Number One: Inserting Lots Of Data

It's not easy to test this kind of thing - but what I can do is share with you some tests I run here on my local machine. I've beefed them up a bit to address operations per second (500,000 records). And then I doubled it. And then doubled it again.

For this first test I'll insert 1,000,000 orders into the orders table, and simultaneously insert 1,000,000 items into the Order Details table of Northwind.

I'll be sure to "interleave" this as well (an Order is entered, then an Order Detail) - to make sure indexing is honored and and that I close off any pending table operation that currently exists.

What I'm looking for with this test is a memory leak anywhere or if something "just isn't right". It's sort of like over-clocking your CPU - I want to run this operation and make sure it's nice and quick, and most importantly that my RAM doesn't start elevating. Here's the code for my test:

        static void Main(string[] args) {
 
            Console.WriteLine(DateTime.Now.ToString());
            DateTime dtStart = DateTime.Now;
 
            //create an Order
            //let's save a meeeeellllionnn items shall we?
            for (int i = 1; i < 1000000; i++) {
                Console.WriteLine("Creating order " + i.ToString());
                Order o = new Order();
                o.CustomerID = "ALFKI";
                o.EmployeeID = 5;
                o.Freight = 10;
                o.OrderDate = DateTime.Now;
                o.RequiredDate = DateTime.Now;
                o.ShipAddress = "Somwhere, Someday";
                o.ShipCity = "City";
                o.ShipCountry = "US";
                o.ShipName = "Shipper";
                o.ShippedDate = DateTime.Now;
                o.ShipPostalCode = "99999";
                o.ShipRegion = "KS";
 
                o.Save("me");
 
                OrderDetail detail = new OrderDetail();
                detail.OrderID = o.OrderID;
                detail.ProductID = 13;
                detail.Quantity = 1;
                detail.UnitPrice = 100;
                detail.Save("me");
            }
            DateTime dtEnd = DateTime.Now;
            Console.WriteLine("Done!" );
            Console.WriteLine("Started on " + dtStart.ToString());
            Console.WriteLine("Ended on "+dtEnd.ToString());
            Console.Read();
        }

This code is happily running away on my machine right now as I write this - which I understand isn't a perfect test scenario however what I'm looking for, specifically, are memory leaks and system "spin outs" - something to indicate that SubSonic "changes" with the size of the DB or somehow flips out when continually processing data.

Running this test doesn't move the server's RAM, which means there are no current leaks, and that's a good thing.

The results come in at 22 minutes (about 1500 records per second)

Test Number Two: Pulling Records Back Out

The next test loops the new records and pulls them all out, one by one into an object:

            //this is a record we just inserted above
            DateTime dtStart = DateTime.Now;
            for (int i = 10248; i < 1010248; i++) {
                Order o = new Order(i);
                Console.WriteLine("Hello from Order " + i.ToString());
 
            }
            DateTime dtEnd = DateTime.Now;
            Console.WriteLine("Done!");
            Console.WriteLine("Started on " + dtStart.ToString());
            Console.WriteLine("Ended on " + dtEnd.ToString());
            Console.Read();

This test finished in about four minutes :). That's loading up a million records, one by one, each with it's own DB call, in precisely 247 seconds. That's 4048 Order objects loaded per second (with no memory leaks).

Test Number Three: Loading Collections

Most people work with typed collections, and I wanted to see what would happen if I looped over every single new record (1,000,000 of em) and loaded n+10 into a collection (10248, in my Northwind DB, is the OrderID where the new data starts after the initial insert operations above):

           //Collection Loading test with 10 records
           DateTime dtStart = DateTime.Now;
           for (int i = 10248; i < 1010248; i++) {
               int nextTen=i+10;
               OrderCollection coll = new Select().From<Order>().Where("orderid")
                   .IsBetweenAnd(i, nextTen).ExecuteAsCollection<OrderCollection>();
               Console.WriteLine("Hello from Orders " + i.ToString()+" - "+nextTen);
 
 
           }
           DateTime dtEnd = DateTime.Now;
           Console.WriteLine("Done!");
           Console.WriteLine("Started on " + dtStart.ToString());
           Console.WriteLine("Ended on " + dtEnd.ToString());
           Console.Read();

The collections loaded in 4 minutes and 22 seconds - or 262 seconds. That's a total of 10,000,000 order objects loaded over 262 seconds, comes out to about 38,167 Order objects loaded a second.