Hi,
We have performance issues with the following pattern, we don't understand why this is very slow in DataObjects and if it's a bug in DataObjects or if we are not using it properly.
The pattern we are trying to understand :
Document doc = Query.All<Document>().Single();
List<Fact> allFacts = doc.Facts.ToList();
foreach (var extract in doc.Extracts)
{
var myFact = allFacts.Where(f => f.Extract == extract && f.Index == 2).Single();
}
Here, allFacts is a list, so we suppose all entities have already been read from database (even the field [Extract.Id]). The issue is with f.Extract == extract which triggers entity materializations (seen with a profiler) but we think it shouldn't be necessary, as we already have loaded it in doc.Facts.ToList(). Even using Prefetch doesn't solve the issue (doc.Facts.Prefetch(f=>f.Extract).ToList();). you'll see that in the following tests.
Could you please have a look and let us now what is the issue here ?
Please note that we would really like to avoid having to use a join or a groupby pattern, as in the last tests, we, developers shouldn't have to worry about this.
Duration of the following tests :
test1_subqueries_with_list [0:10.039] Success
test2_subqueries_with_prefetch [0:10.986] Success
test3_subqueries_with_prefetch_id [0:11.227] Success
test4_subqueries_with_key_compare [0:10.454] Success
test5_subqueries_with_id_compare [0:18.222] Success
test6_subqueries_with_group_by_with_list [0:00.722] Success
test7_subqueries_with_group_by_with_dictionary [0:00.170] Success
test8_subqueries_with_join [0:00.192] Success
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;
namespace UnitTests
{
[TestClass]
public class SubQueriesPerfs
{
private static string connectionString = @"sqlserver://localhost\SQL2014/DO50-Tests";
#region Model
[HierarchyRoot]
public class Document : Entity
{
[Field, Key]
public long Id { get; private set; }
[Field,Association(PairTo = "Document", OnOwnerRemove = OnRemoveAction.Cascade, OnTargetRemove = OnRemoveAction.Clear)]
public EntitySet<Extract> Extracts { get; set; }
[Field,Association(PairTo = "Document", OnOwnerRemove = OnRemoveAction.Cascade, OnTargetRemove = OnRemoveAction.Clear)]
public EntitySet<Fact> Facts { get; set; }
[Field]
public string Name { get; set; }
}
[HierarchyRoot]
public class Extract : Entity
{
[Field, Key]
public long Id { get; private set; }
[Field]
public Document Document { get; set; }
[Field,Association(PairTo = "Extract", OnOwnerRemove = OnRemoveAction.Cascade, OnTargetRemove = OnRemoveAction.Clear)]
public EntitySet<Fact> Facts { get; set; }
[Field]
public string Name { get; set; }
}
[HierarchyRoot]
public class Fact : Entity
{
[Field, Key]
public long Id { get; private set; }
[Field]
public Document Document { get; set; }
[Field]
public Extract Extract { get; set; }
[Field]
public int Index { get; set; }
}
private static Domain CreateDomain(SessionOptions sessionOptions)
{
DomainConfiguration config = new DomainConfiguration(connectionString);
config.UpgradeMode = DomainUpgradeMode.Recreate;
config.Types.Register(typeof(Document).Assembly);
SessionConfiguration defaultSessionConfig = config.Sessions.Default;
if (defaultSessionConfig == null)
{
defaultSessionConfig = new SessionConfiguration(WellKnown.Sessions.Default);
config.Sessions.Add(defaultSessionConfig);
}
defaultSessionConfig.Options = sessionOptions;
return Domain.Build(config);
}
#endregion
private static Domain domain;
[ClassInitialize]
public static void ClassInitialize(TestContext testContext)
{
SessionOptions sessionOptions = SessionOptions.ValidateEntities | SessionOptions.AutoActivation | SessionOptions.AutoSaveChanges | SessionOptions.NonTransactionalReads;
domain = CreateDomain(sessionOptions);
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = new Document();
for (int i = 0; i < 1000;i++)
{
Extract extract = new Extract {Document = doc};
for (int j = 0; j < 10; j++)
{
Fact fact = new Fact(){Document = doc, Extract = extract, Index = j};
}
}
ts.Complete();
}
}
}
[TestMethod]
public void test1_subqueries_with_list()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
List<Fact> allFacts = doc.Facts.ToList();
foreach (var extract in doc.Extracts)
{
var myFact = allFacts.Where(f => f.Extract == extract && f.Index == 2).Single();
}
}
}
}
[TestMethod]
public void test2_subqueries_with_prefetch()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.Prefetch(f=>f.Extract).ToList();
foreach (var extract in doc.Extracts)
{
var myFact = allFacts.Where(f => f.Extract == extract && f.Index == 2).Single();
}
}
}
}
[TestMethod]
public void test3_subqueries_with_prefetch_id()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.Prefetch(f=>f.Extract.Id).ToList();
foreach (var extract in doc.Extracts)
{
var myFact = allFacts.Where(f => f.Extract == extract && f.Index == 2).Single();
}
}
}
}
[TestMethod]
public void test4_subqueries_with_key_compare()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.Prefetch(f=>f.Extract.Id).ToList();
foreach (var extract in doc.Extracts)
{
var extractKey = extract.Key;
var myFact = allFacts.Where(f => f.Extract.Key == extractKey && f.Index == 2).Single();
}
}
}
}
[TestMethod]
public void test5_subqueries_with_id_compare()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.Prefetch(f=>f.Extract.Id).ToList();
foreach (var extract in doc.Extracts)
{
var myFact = allFacts.Where(f => f.Extract.Id == extract.Id && f.Index == 2).Single();
}
}
}
}
[TestMethod]
public void test6_subqueries_with_group_by_with_list()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.GroupBy(f => f.Extract).Select(f => new {Key = f.Key, Facts = f.Select(a=>a)}).ToList();
foreach (var extract in doc.Extracts)
{
var extractFacts = allFacts.Single(f => f.Key == extract).Facts;
var myFact = extractFacts.Single(f => f.Index == 2);
}
}
}
}
[TestMethod]
public void test7_subqueries_with_group_by_with_dictionary()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts = doc.Facts.GroupBy(f => f.Extract).ToDictionary(g=>g.Key.Id, g=>g.ToList());
foreach (var extract in doc.Extracts)
{
var entry = allFacts[extract.Id];
var myFact = entry.Single(f => f.Index == 2);
}
}
}
}
[TestMethod]
public void test8_subqueries_with_join()
{
using (Session session = domain.OpenSession())
{
using (TransactionScope ts = session.OpenTransaction())
{
Document doc = Query.All<Document>().Single();
var allFacts =
(from e in doc.Extracts
join f in doc.Facts on e equals f.Extract
into facts
select new {Extract=e, Facts=facts}).ToList();
foreach (var extract in doc.Extracts)
{
var extractFacts = allFacts.Single(f => f.Extract == extract).Facts;
var myFact = extractFacts.Single(f => f.Index == 2);
}
}
}
}
}
}
asked
Mar 12 '18 at 10:01
Benoit Nesme
43●20●20●24