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

asked Jan 28 '12 at 18:03

Anton's gravatar image

Anton
9335


One 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

answered Jan 28 '12 at 22:57

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

edited Jan 29 '12 at 01:29

Thanks a lot, now it's absolutely clear.

Is it possible to simplify the solution by using a global cache for such data (ASubItem, BSubItem in my example), so we don't need to retrieve it from the database at all?

I saw some information on oncoming release of Cache API, but I could not find any details on how will it work, and when will it be available. If you could disclose some more information about your plans on global caching, it would really help us to make a decision, because in general, after some preliminary testing we are very satisfied with your product.

(Jan 29 '12 at 17:37) Anton Anton's gravatar image

Greetings Anton,

L2 Cache support is preliminary planned for 4.7 version (Summer 2012), see the roadmap.

(Jan 31 '12 at 00:58) Dmitri Maximov Dmitri%20Maximov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×5

Asked: Jan 28 '12 at 18:03

Seen: 2,286 times

Last updated: Jan 31 '12 at 00:58

powered by OSQA