Linq2SQL vs NHibernate performance (have I gone mad?)

Posted by HeavyWave on Stack Overflow See other posts from Stack Overflow or by HeavyWave
Published on 2010-05-13T06:58:33Z Indexed on 2010/05/13 7:04 UTC
Read the original article Hit count: 338

Filed under:
|

I have written the following tests to compare performance of Linq2SQL and NHibernate and I find results to be somewhat strange. Mappings are straight forward and identical for both. Both are running against a live DB. Although I'm not deleting Campaigns in case of Linq, but that shouldn't affect performance by more than 10 ms.

Linq:

    [Test]
    public void Test1000ReadsWritesToAgentStateLinqPrecompiled()
    {
        Stopwatch sw = new Stopwatch();
        Stopwatch swIn = new Stopwatch();
        sw.Start();
        for (int i = 0; i < 1000; i++)
        {
            swIn.Reset();
            swIn.Start();
            ReadWriteAndDeleteAgentStateWithLinqPrecompiled();
            swIn.Stop();
            Console.WriteLine("Run ReadWriteAndDeleteAgentState: " + swIn.ElapsedMilliseconds + " ms");
        }

        sw.Stop();
        Console.WriteLine("Total Time: " + sw.ElapsedMilliseconds + " ms");
        Console.WriteLine("Average time to execute queries: " + sw.ElapsedMilliseconds / 1000 + " ms");
    }

    private static readonly Func<AgentDesktop3DataContext, int, EntityModel.CampaignDetail>
        GetCampaignById =
            CompiledQuery.Compile<AgentDesktop3DataContext, int, EntityModel.CampaignDetail>(
                (ctx, sessionId) => (from cd in ctx.CampaignDetails
                                     join a in ctx.AgentCampaigns on cd.CampaignDetailId equals a.CampaignDetailId
                                     where a.AgentStateId == sessionId
                                     select cd).FirstOrDefault());

    private void ReadWriteAndDeleteAgentStateWithLinqPrecompiled()
    {
        int id = 0;
        using (var ctx = new AgentDesktop3DataContext())
        {
            EntityModel.AgentState agentState = new EntityModel.AgentState();
            var campaign = new EntityModel.CampaignDetail { CampaignName = "Test" };
            var campaignDisposition = new EntityModel.CampaignDisposition { Code = "123" };
            campaignDisposition.Description = "abc";
            campaign.CampaignDispositions.Add(campaignDisposition);
            agentState.CallState = 3;

            campaign.AgentCampaigns.Add(new AgentCampaign
            {
                AgentState = agentState
            });
            ctx.CampaignDetails.InsertOnSubmit(campaign);
            ctx.AgentStates.InsertOnSubmit(agentState);
            ctx.SubmitChanges();
            id = agentState.AgentStateId;
        }

        using (var ctx = new AgentDesktop3DataContext())
        {
            var dbAgentState = ctx.GetAgentStateById(id);
            Assert.IsNotNull(dbAgentState);
            Assert.AreEqual(dbAgentState.CallState, 3);
            var campaignDetails = GetCampaignById(ctx, id);

            Assert.AreEqual(campaignDetails.CampaignDispositions[0].Description, "abc");
        }

        using (var ctx = new AgentDesktop3DataContext())
        {
            ctx.DeleteSessionById(id);
        }
    }

NHibernate (the loop is the same):

    private void ReadWriteAndDeleteAgentState()
    {
            var id = WriteAgentState().Id;
            StartNewTransaction();

            var dbAgentState = agentStateRepository.Get(id);

            Assert.IsNotNull(dbAgentState);
            Assert.AreEqual(dbAgentState.CallState, 3);
            Assert.AreEqual(dbAgentState.Campaigns[0].Dispositions[0].Description, "abc");

            var campaignId = dbAgentState.Campaigns[0].Id;
            agentStateRepository.Delete(dbAgentState);

            NHibernateSession.Current.Transaction.Commit();

            Cleanup(campaignId);

            NHibernateSession.Current.BeginTransaction();
    }

Results:

NHibernate:
Total Time: 9469 ms
Average time to execute 13 queries: 9 ms

Linq:
Total Time: 127200 ms
Average time to execute 13 queries: 127 ms

Linq lost by 13.5 times! Event with precompiled queries (both read queries are precompiled).

This can't be right, although I expected NHibernate to be faster, this is just too big of a difference, considering mappings are identical and NHibernate actually executes more queries against the DB.

© Stack Overflow or respective owner

Related posts about nhibernate

Related posts about linq-to-sql