Hi,
I am stuck with the situation when using of future queries significantly changes the output SQL and also increases the number of round-trips to the database.
Please consider the following example.
The object model is as follows:
[Serializable]
[HierarchyRoot]
public class Item : Entity
{
[Field, Key]
public int Id { get; private set; }
[Field]
public EntitySet<ASubItem> ASubItems { get; private set; }
[Field]
public EntitySet<BSubItem> BSubItems { get; private set; }
public Item(Session session)
: base(session)
{
}
}
[Serializable]
[HierarchyRoot]
public class ASubItem : Entity
{
[Field, Key]
public int Id { get; private set; }
public ASubItem(Session session)
: base(session)
{
}
}
[Serializable]
[HierarchyRoot]
public class BSubItem : Entity
{
[Field, Key]
public int Id { get; private set; }
public BSubItem(Session session)
: base(session)
{
}
}
The second part of the following code produces 3 simple SQL batches. The goal is to reduce them to 2 batches.
static void Test1()
{
var config = DomainConfiguration.Load("Default");
var domain = Domain.Build(config);
// Part 1.
Key aSubItemKey, bSubItemKey;
using (var session = domain.OpenSession())
{
using (var transactionScope = session.OpenTransaction())
{
ASubItem aSubItem = new ASubItem(session);
BSubItem bSubItem = new BSubItem(session);
aSubItemKey = aSubItem.Key;
bSubItemKey = bSubItem.Key;
transactionScope.Complete();
}
}
// Part 2.
using (var session = domain.OpenSession())
{
using (var transactionScope = session.OpenTransaction())
{
// These two requests need to be merged into one batch.
ASubItem aSubItem = session.Query.Single<ASubItem>(aSubItemKey);
BSubItem bSubItem = session.Query.Single<BSubItem>(bSubItemKey);
Item item = new Item(session);
item.ASubItems.Add(aSubItem);
item.BSubItems.Add(bSubItem);
transactionScope.Complete();
}
}
}
-- BATCH 1
exec sp_executesql N'SELECT [a].[Id], [a].[TypeId] FROM (SELECT [b].[Id], 101 AS [TypeId] FROM [dbo].[ASubItem] [b]) [a] WHERE ([a].[Id] IN (@p1_0_0_0));
',N'@p1_0_0_0 int',@p1_0_0_0=1
-- BATCH 2
exec sp_executesql N'SELECT [a].[Id], [a].[TypeId] FROM (SELECT [b].[Id], 102 AS [TypeId] FROM [dbo].[BSubItem] [b]) [a] WHERE ([a].[Id] IN (@p1_0_0_0));
',N'@p1_0_0_0 int',@p1_0_0_0=2
-- BATCH 3
exec sp_executesql N'INSERT INTO [dbo].[Item] ([Id]) VALUES (@p1_0);
INSERT INTO [dbo].[Item-ASubItems-ASubItem] ([Item], [ASubItem]) VALUES (@p2_0, @p2_1);
INSERT INTO [dbo].[Item-BSubItems-BSubItem] ([Item], [BSubItem]) VALUES (@p3_0, @p3_1);
',N'@p1_0 int,@p2_0 int,@p2_1 int,@p3_0 int,@p3_1 int',@p1_0=3,@p2_0=3,@p2_1=1,@p3_0=3,@p3_1=2
In the real project we have much more than 2 FK properties that have to be set before saving the object of type "Item"; and initializing each of these properties will cause additional round-trips to the database.
The obvious solution was to use future queries, but it only made things worse.
The "optimized" code:
static void Test2()
{
...
// Part 2.
using (var session = domain.OpenSession())
{
using (var transactionScope = session.OpenTransaction())
{
var aSubItem = session.Query.ExecuteDelayed(x => x.All<ASubItem>().Single(i => i.Key == aSubItemKey));
var bSubItem = session.Query.ExecuteDelayed(x => x.All<BSubItem>().Single(i => i.Key == bSubItemKey));
Item item = new Item(session);
item.ASubItems.Add(aSubItem.Value);
item.BSubItems.Add(bSubItem.Value);
transactionScope.Complete();
}
}
}
-- BATCH 1 - OK, VERY GOOD
exec sp_executesql N'INSERT INTO [dbo].[Item] ([Id]) VALUES (@p1_0);
SELECT TOP 2 [a].[Id], 101 AS [TypeId] FROM [dbo].[ASubItem] [a] WHERE ([a].[Id] = @p2_0);
SELECT TOP 2 [a].[Id], 102 AS [TypeId] FROM [dbo].[BSubItem] [a] WHERE ([a].[Id] = @p3_0);
',N'@p1_0 int,@p2_0 int,@p3_0 int',@p1_0=3,@p2_0=1,@p3_0=2
-- BATCH 2 (WHY??)
exec sp_executesql N'SELECT TOP 32 [a].[Item] AS [a.Item], [a].[ASubItem] AS [a.ASubItem], [b].[Id], [b].[TypeId] FROM [dbo].[Item-ASubItems-ASubItem] [a] INNER JOIN (SELECT [c].[Id], 101 AS [TypeId] FROM [dbo].[ASubItem] [c]) [b] ON ([a].[ASubItem] = [b].[Id]) WHERE ([a].[Item] = @p1_0);
',N'@p1_0 int',@p1_0=3
-- BATCH 3 (WHY?)
exec sp_executesql N'INSERT INTO [dbo].[Item-ASubItems-ASubItem] ([Item], [ASubItem]) VALUES (@p1_0, @p1_1);
SELECT TOP 32 [a].[Item] AS [a.Item], [a].[BSubItem] AS [a.BSubItem], [b].[Id], [b].[TypeId] FROM [dbo].[Item-BSubItems-BSubItem] [a] INNER JOIN (SELECT [c].[Id], 102 AS [TypeId] FROM [dbo].[BSubItem] [c]) [b] ON ([a].[BSubItem] = [b].[Id]) WHERE ([a].[Item] = @p2_0);
',N'@p1_0 int,@p1_1 int,@p2_0 int',@p1_0=3,@p1_1=1,@p2_0=3
-- BATCH 4
exec sp_executesql N'INSERT INTO [dbo].[Item-BSubItems-BSubItem] ([Item], [BSubItem]) VALUES (@p1_0, @p1_1);
',N'@p1_0 int,@p1_1 int',@p1_0=3,@p1_1=2
As you can see, now DO send 4 SQL batches to the server.
So, the question is how can I force DO to produce a result like this:
-- BATCH 1
exec sp_executesql N'INSERT INTO [dbo].[Item] ([Id]) VALUES (@p1_0);
SELECT TOP 2 [a].[Id], 101 AS [TypeId] FROM [dbo].[ASubItem] [a] WHERE ([a].[Id] = @p2_0);
SELECT TOP 2 [a].[Id], 102 AS [TypeId] FROM [dbo].[BSubItem] [a] WHERE ([a].[Id] = @p3_0);
',N'@p1_0 int,@p2_0 int,@p3_0 int',@p1_0=3,@p2_0=1,@p3_0=2
-- BATCH 2
exec sp_executesql N'INSERT INTO [dbo].[Item-ASubItems-ASubItem] ([Item], [ASubItem]) VALUES (@p2_0, @p2_1);
INSERT INTO [dbo].[Item-BSubItems-BSubItem] ([Item], [BSubItem]) VALUES (@p3_0, @p3_1);
',N'@p1_0 int,@p2_0 int,@p2_1 int,@p3_0 int,@p3_1 int',@p1_0=3,@p2_0=3,@p2_1=1,@p3_0=3,@p3_1=2