Lightspeed vs Linq2SQL performance

I'm currently trying out Lightspeed to see if we can use it as a replacement for LINQ2SQL and for that I made a small performance test. Its a simple bulk insert test to check insert performance compared to L2S, its build up of 3 tables (Users, UserAttributes and Attributes). The original database is much bigger but for our test we're only using those 3 tables.

In our test scenario there are 16 records in the Attributes table. Our test scenario will try to insert 500 users with each 16 attributes, resulting in 8500 records inserted in total. Not an extreme amount.

The UserAttributes table consists of a value and links to both the user table and the attribute table (and since Lightspeed doesnt support composite keys, an Id field).

The code that does the bulk insert is as follows:

// .NET 3.5 (LINQ)
Stopwatch watch = new Stopwatch();
watch.Start();
LightSpeedContext context = new LightSpeedContext("Development");
using (var unitOfWork = context.CreateUnitOfWork())
{
    for(int i = 0; i < 500; i ++)
    {
        var user = new User();
        user.CompanyId = 8;
        user.Access = 0;
        user.EmailAddress = "lightspeed" + i + "@whatever.nl";
        user.IsBanned = false;
        user.IsOptOut = false;
        user.Nickname = "lightspeed" + i;
        user.Password = "132423643563456";
        user.Username = "lightspeed" + i;
        unitOfWork.Add(user);

        foreach (var attribute in unitOfWork.Attributes)
        {
            var ua = new UserAttribute();
            ua.Attribute = attribute;
            ua.Value = "blaat";
            ua.User = user;
        }
    }
    unitOfWork.SaveChanges();
}
watch.Stop();

var watch2 = new Stopwatch();
watch2.Start();
DataClasses1DataContext context2 = new DataClasses1DataContext();
var query2 = from o in context2.User2s where o.CompanyId == 8 select o;
for(int i = 0; i < 500; i ++)
{
    var user = new User2();
    user.CompanyId = 8;
    user.Access = 0;
    user.EmailAddress = "linq" + i + "@whatever.nl";
    user.IsBanned = false;
    user.IsOptOut = false;
    user.Nickname = "linq" + i;
    user.Password = "132423643563456";
    user.Username = "linq" + i;
    context2.User2s.InsertOnSubmit(user);

    foreach (var attribute in context2.Attribute2s)
    {
        var ua = new UserAttribute2();
        ua.Attribute2 = attribute;
        ua.Value = "blaat";
        ua.User2 = user;
    }
}
context2.SubmitChanges();
watch2.Stop();
Console.WriteLine(watch.ElapsedMilliseconds);
Console.WriteLine(watch2.ElapsedMilliseconds);
Console.ReadKey();

Now the code has both the Lightspeed and L2S implementation and dumps the time spend on both routines. Now if there was a small difference I wouldnt bring up this subject, but the Lightspeed version takes 144 seconds while the L2S implementation takes 19 seconds to do the same work.

Any idea what could cause this extreme difference?

Hi Jeremy,

Definitely not what we would expect - as a rule insert performance for LightSpeed is pretty much on par with L2S, on the read side, L2S is slightly faster in most scenarios we have looked at as LightSpeed has a higher cost of object construction.

I have just done a quick local test here using the code you provided but am not seeing any real difference between the two. I am running the tests against a local server so the timing are a lot lower than yours. I also made an optimization to both runs to remove the use of unitOfWork.Attributes / context.Attribute2s within the loop as this causes a select to be executed during each iteration, so you can simply fetch this once at the beginning of the section and refer to an in memory list instead.

For the scenario and code above I am seeing:

i = 500

LS: 3850ms

L2S: 3167ms

 

i = 1000

LS = 8041ms

L2S = 6877ms

 

i = 5000

LS = 47087ms

L2S =  44458ms

 

Couple of questions on this; Are you using Int32/Int64 or Guid based identity fields in your model? And additionally are there any other indexes on the table (other than the clustered PK)?

One thing which might be of use is to look at a profiler session to understand where the time is being taken from the databases point of view - are the inserts generating an equal amount of resource utilization etc.

I changed the foreach with the attributes a bit so that it saves that to a list (using ToList()), it did shave off a few seconds. (The results are now 134s and 16s)

All identity fields are int32-ident fields, I've set the "Identity Method" to "IdentityColumn" for all entities and set "Identity Column Name" to "Id". Everything else is left to their default settings.

The Users table has a PK on the Id field and a bunch of FK's (most of which arent in the lightspeed model).

The UserAttributes table has a PK on the Id field and two FK's (One to the Users table and one to the Attributes table).

The Attributes table again has a PK on the Id field and a couple of FK's (One to the UserAttributes table, a few others to tables not in the model).

There are no other indexes present.

I put a stopwatch around the SaveChanges and SubmitChanges and it seems that it spends most of its time executing those two functions. Whats odd is that during the SaveChanges, the test application uses up 100% CPU of a single core. (e.g. 25% CPU on a quad core system) When I compared that to the L2S SubmitChanges (which is also the majority of the time) the test application was using 17-20% (which I still find a lot for something that should mostly be database work). (note the database is running on a seperate quad-core server, connected using gigabit)

Later today, when I have some time, I'll setup a new database (instead of using this existing one) and see if it displays the same issue. Though that would still not explain the huge difference that I'm seeing now. Considering both implementations use the same database, I would expect the same performance (preferably better of course ;))

I will also see if I have time to play with the SQL Profiler/Tracer, see if the Lightspeed implementation does more queries.

One small addition, not sure if it matters, the Value field in the UserAttributes table is an nvarchar(MAX) field. Also not inserting the UserAttributes causes the test to perform much faster (around 1 second for each implementation).

I just finished some database profiling, and while I'm not an expert, it doesnt look like the issue is with the database. 99.9% of the inserts done have a '0' duration. I also changed the value field from a nvarchar(max) to a nvarchar(128) (and even a varchar(128)) which did nothing to performance.

Turning on logging in LightSpeed gave me the same results:

INSERT INTO UserAttributes
(
  AttributeId,
  UserId,
  Value
)
VALUES
(
  32,
  30381,
  'blaat'
);

SELECT scope_identity()

--> Time:   0 ms

So I'm still clueless :)

Hi Jeremy,

That logging did indeed help enlighten things - I see you are using an identity column. Ive just done another quick test here and am now able to reproduce the horrible performance which is specific to inserts under the identity column identity strategy it appears.

The good news is that this looks to be a relatively straightforward fix to get rid of the issue for this case. Thanks for reporting this! :)

I will be pushing up a candidate fix shortly so this should be available in the next nightly (20090810) and above, if you could grab this tomorrow and see if this dramatically improves your perf, ie it should be inline with L2S as per above and let us know - that would be much appreciated.

 

Jeremy

 

Oops, that should actually have been the 20090811 nightly or above as Ivan rightly corrected me :)

Just reporting back after testing build 20090811 and I'm impressed :)

The performance test just finished in 6 seconds (Lightspeed, on the SQL server) vs 13 seconds (L2S, on the SQL server), thats a pretty big improvement (the difference was even bigger when the test was run on another server, 7.4 vs 17). Its still double the time of your test, but that might be other factors. The L2S implementation for you is also faster.

Only real issue left is composite keys, we do use those at several places in our database. I'll have to look into the possibility of adding an identifier (like I did with this test).