In example below, every query with 'store' do steps:

  1. Create temp table
  2. Insert data
  3. Query data
  4. Delete temp table

Why?

My opinion that steps (1,2,4) no need execute every time

And second question, what difference between using Store method and using Enumerable value direct in query (see second 'for' in example)

namespace Sample
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using Xtensive.Orm;
    using Xtensive.Orm.Configuration;

    class Program
    {
        static void Main(string[] args)
        {
            var dc = new DomainConfiguration("sqlserver://localhost/DO40-Tests");

            dc.Types.Register(typeof(TestEntity));

            dc.UpgradeMode = DomainUpgradeMode.Recreate;
            var sessionConfiguration = new SessionConfiguration(SessionOptions.AutoActivation | SessionOptions.ServerProfile);

            using (var domain = Domain.Build(dc))
            {
                using (var session = domain.OpenSession(sessionConfiguration))
                using (session.Activate())
                using (var t = session.OpenTransaction())
                {
                    new TestEntity { Name = "Test name" };

                    t.Complete();
                }

                using (var session = domain.OpenSession(sessionConfiguration))
                using (session.Activate())
                using (var t = session.OpenTransaction())
                {
                    var list = new List<string>();

                    for (var i = 0; i < 500; i++)
                    {
                        list.Add("test");
                    }

                    var store = Session.Current.Query.Store(list);

                    for (var c = 0; c < 5; c++)
                    {
                        Session.Current.Query.All<TestEntity>().Join(store, o => o.Name, i => i, (o, i) => o).Count();
                    }

                    for (var c = 0; c < 5; c++)
                    {
                        Session.Current.Query.All<TestEntity>().Count(e => list.Contains(e.Name));
                    }

                    t.Complete();
                }
            }
        }
    }

    [HierarchyRoot]
    public class TestEntity : Entity
    {
        [Key]
        [Field(Nullable = false)]
        public Guid Id { get; private set; }

        [Field(Length = 400)]
        public string Name { get; set; }
    }
}

Queries (every query execute 5 times)

with 'Store'

CREATE TABLE [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946] ([c01umn] nvarchar(4000) COLLATE Cyrillic_General_CS_AS)
exec sp_executesql N'INSERT INTO [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946] ([c01umn]) VALUES (@p1_0);
INSERT INTO [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946] ([c01umn]) VALUES (@p2_0);
INSERT INTO [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946] ([c01umn]) VALUES (@p3_0);

...

SELECT COUNT_BIG(*) AS [c01umn1] FROM [dbo].[TestEntity] [a] INNER JOIN [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946] ON ([a].[Name] = [#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946].[c01umn]);
DELETE FROM [dbo].[#Tmp_55326e4a-bf32-4d62-afab-dbb52b5fc946];

with list.Contains(e.Name)

CREATE TABLE [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453] ([C0] nvarchar(4000) COLLATE Cyrillic_General_CS_AS)
exec sp_executesql N'INSERT INTO [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453] ([C0]) VALUES (@p1_0);
INSERT INTO [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453] ([C0]) VALUES (@p2_0);
INSERT INTO [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453] ([C0]) VALUES (@p3_0);

...

SELECT COUNT_BIG(*) AS [c01umn1] FROM (SELECT [a].[Id], 100 AS [TypeId], [a].[Name] FROM [dbo].[TestEntity] [a]) [b] WHERE  EXISTS (SELECT [#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453].[C0] FROM [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453] WHERE ([b].[Name] = [#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453].[C0]));
DELETE FROM [dbo].[#Tmp_377456e1-ecb0-48ef-aedb-efde8a271453];

asked Jun 23 '16 at 04:00

Gushchin%20Anton's gravatar image

Gushchin Anton
5123


One Answer:

Hello Anton

DataObjects.Net creates an instance of SqlStoreProvider per every use of Store() in a query and this providers create, lets say, its own temporary table with unique name and only DataObjects.Net knows these names. You cannot get them. Data won't be used later so why don't we clean the tables up? Of course, there is a scenarion when temporary table could be used more than once but so far DataObjects.Net does not support this use case cleaning up is more preferable option.

Speaking of difference between using of QueryEndpoint.Store() and IEnumerable<t>.Contains() for local collection, QueryEndpoint.Store() leads to a temporary table creation 100% sure. Instead, using of IEnumerable<t>.Contains() cause one of two optional behaviors - DO may use complex condition if count of elements isn't big (less than 256), otherwise, it will use temporary table.

answered Jul 01 '16 at 04:24

Alexey%20Kulakov's gravatar image

Alexey Kulakov
48215

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

powered by OSQA