Question

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

Accepted Answer

Hello Anton,

Those two "WHY" queries are caused by EntitySet preloading feature. We prefetch small amount of items for each EntitySet on first access. When you add new item to EntitySet we need to check if it is already added. EntitySet might not contain all of its items in memory, so if in-memory check didn't find existing item, we'll have to do a query. However on first such check DataObjects.Net tries to fetch at most 32 items for that EntitySet with the hope that futher checks will succeed in memory.

Why does it work so in the first case?

This is because EntitySet owner (item) is known to be newly created, so we can be sure that its EntitySet does not have any items that are stored in database, but have not been loaded into session.

Why does it work so in the second case?

When you first access any delayed query result (aSubItem.Value in your case) all delayed queries are executed. Before executing delayed queries all currently new or modified entities are saved. After such save item is no longer new. It has been saved to database, that turns optimization that avoids EntitySet preloading off.

To produce result like you expect, you should slightly rearrange your code:

var aSubItemQuery = session.Query.ExecuteDelayed(x => x.All<ASubItem>().Single(i => i.Key == aSubItemKey));
var bSubItemQuery = session.Query.ExecuteDelayed(x => x.All<BSubItem>().Single(i => i.Key == bSubItemKey));

var aSubItem = aSubItemQuery.Value; // This will trigger execution
                                    // of all registered queries.
var bSubItem = bSubItemQuery.Value;

Item item = new Item(session);
item.ASubItems.Add(aSubItem); // Item is still new here, no need to preload ASubItems
item.BSubItems.Add(bSubItem); // Same for BSubItems

Why does it work so in the first case?

Why does it work so in the second case?

Subscription:

Related questions