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.
