Question

If execute something like this .Where(s => (s.Style as Style1).Field1 == "asas" || (s.Style as Style1).Field1 == "asas1" || (s.Style as Style1).Field1 == "asas2")

get sql query with 3 left join, expected 1 left join

Sample DO 5.0.7

using System;
using System.Linq;
using System.Transactions;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;

class Program
{
    static void Main(string[] args)
    {
        var dc = new DomainConfiguration("sqlserver", @"Data Source=localhost\MSSQLSERVER2016; Initial Catalog=DO40-Tests; Integrated Security=True;Connection Timeout=300;Application Name=Werp;");

        dc.Sessions.Add(new SessionConfiguration("Default")
        {
            BatchSize = 25,
            DefaultIsolationLevel = IsolationLevel.ReadCommitted,
            CacheSize = 1000,
            DefaultCommandTimeout = 6000,
            Options =
                                 SessionOptions.Default
                                 | SessionOptions.NonTransactionalReads
                                 | SessionOptions.AutoActivation
                                 | SessionOptions.SuppressRollbackExceptions
                                 | SessionOptions.ValidateEntityVersions,
        });

        dc.Types.Register(typeof(Style1));
        dc.Types.Register(typeof(Style2));
        dc.Types.Register(typeof(Status));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;

        using (var domain = Domain.Build(dc))
        {
            using (var session = domain.OpenSession())
            using (session.Activate())
            using (var t = session.OpenTransaction())
            {
                new Status(Guid.NewGuid()) { Name = "Test" };

                t.Complete();
            }

            using (var session = domain.OpenSession())
            using (session.Activate())
            using (var t = session.OpenTransaction())
            {
                session.Query.All<Status>()
                    .Where(s => (s.Style as Style1).Field1 == "asas" || (s.Style as Style1).Field1 == "asas1" || (s.Style as Style1).Field1 == "asas2")
                    .ToArray();

                t.Complete();
            }
        }
    }
}

[HierarchyRoot]
[Index("Name", Unique = true, Name = "Status.IX_Name")]
[Serializable]
[KeyGenerator(KeyGeneratorKind.None)]
public class Status : Entity
{
    public Status(Guid id)
        : base(id)
    {
    }

    [Field]
    [Key]
    public Guid Id { get; }

    [Field]
    public string Name { get; set; }

    [Field]
    public string Description { get; set; }

    [Field]
    public StyleBase Style { get; set; }
}

[HierarchyRoot]
[Serializable]
[KeyGenerator(KeyGeneratorKind.None)]
public class StyleBase : Entity
{
    public StyleBase(Guid id)
        : base(id)
    {
    }

    [Field]
    [Key]
    public Guid Id { get; }

    [Field]
    public string Name { get; set; }

    [Field]
    public string Description { get; set; }
}

[Serializable]
public class Style1 : StyleBase
{
    public Style1(Guid id)
        : base(id)
    {
    }

    [Field]
    public string Field1 { get; set; }
}

[Serializable]
public class Style2 : StyleBase
{
    public Style2(Guid id)
        : base(id)
    {
    }

    [Field]
    public string Field2 { get; set; }
}

Sql query

SELECT [a].[Id], 
       100 AS [TypeId], 
       [a].[Name], 
       [a].[Description], 
       [a].[Style.Id] 
FROM   [dbo].[Status] [a] 
       LEFT OUTER JOIN [dbo].[Style1] [b] 
                       INNER JOIN [dbo].[StyleBase] [c] 
                               ON ( [c].[Id] = [b].[Id] ) 
                    ON ( [a].[Style.Id] = [c].[Id] ) 
       LEFT OUTER JOIN [dbo].[Style1] [d] 
                       INNER JOIN [dbo].[StyleBase] [e] 
                               ON ( [e].[Id] = [d].[Id] ) 
                    ON ( [a].[Style.Id] = [e].[Id] ) 
       LEFT OUTER JOIN [dbo].[Style1] [f] 
                       INNER JOIN [dbo].[StyleBase] [g] 
                               ON ( [g].[Id] = [f].[Id] ) 
                    ON ( [a].[Style.Id] = [g].[Id] ) 
WHERE  ( ( [b].[Field1] = N'asas' ) 
          OR ( [d].[Field1] = N'asas1' ) 
          OR ( [f].[Field1] = N'asas2' ) );

Answer 1

0

Hello Anton

Unfortunately, DO can't handle such case. It translates your query as you write LinQ query. I will create a task for such case but it is not a major issue.

Try to use type conversion in select and then use its fields in Where clause

answered Jan 27 '17 at 02:51

Alexey Kulakov
772●2●5

Answer 2

0

However, this optimization is a difficult issue for DO? This optimization would much improve the queries that developers write.

answered Jan 30 '17 at 04:12

k_v_kuznetsov
34●2●2●6

Konstantin, do you want to make translation slower? Because if i did such optimization it would be a sort of rewriter which would traverse expression tree one more time. I couldn't "say" the rewriter "execute here but not there". It would work for each query.

(Feb 03 '17 at 06:24) Alexey Kulakov

Every time we add any checks in the code (or modify vizitor in our case), it makes our code a little slower. The point is in the value of it. For example, add 1 ms for visiting any queries, but to do this optimization seems to me reasonable.

(Feb 03 '17 at 08:41) k_v_kuznetsov

Answer 3

0	And if write .Where(s => (s.Style as Style1).Field1.In("asas", "asas1", "asas2")) it will be shorter and, probably, optimized answered Feb 01 '17 at 12:51 pil0t 207●57●57●63

Answer 4

0

pil0t: Try to do the same thing with the next .Where(s => (s.Style as Style1).Field1.StartsWith("asas") || (s.Style as Style1).Field1.StartsWith("asas1") || (s.Style as Style1).Field1StartsWith("asas2"))

answered Feb 03 '17 at 02:00

k_v_kuznetsov
34●2●2●6

pil0t is right. Such query optimization allows you to get rid of extra joins an make translation easier. If I wrote such query I would write it same way as pil0t did. Generally, it is what I offered you to do.

(Feb 03 '17 at 06:31) Alexey Kulakov

Actually, he is right that the request can be rewritten. But does this mean that the idea of optimization is bad by itself? I believe that this optimization will increase the speed of a large number of queries, since developers can not expect these two requests will be given a different SQL.

(Feb 03 '17 at 06:40) k_v_kuznetsov

Don't get me wrong. I agreed that it might be good to do something with it, I even promised to create a task for such optimization and I did it right that time i wrote my first answer. But I don't thing that this is urgent or even major case. Does such query lead to translation error? No. Does this query may be optimized by someone who writes such queries? Yes. May be I don't get something you want to say, then please explain me what I don't get because I don't know your solution. Anyway I thing you have more important cases for me to do.

(Feb 03 '17 at 09:41) Alexey Kulakov

My comment about "sort of rewriter" was so because I didn't think over the solution yet and this is the easiest way to replace multiple usages of exact expression by the only one. Probably there is another, more optimized way to perform such improvement, I don't know until I start investigation and try to solve the problem.

(Feb 03 '17 at 09:48) Alexey Kulakov

Answer 5

var query = from s in session.Query.All<Status>()
    let f1 = (s.Style as Style1).Field1
    where f1.Contains("as") || f1.Contains("a2")
    select s;

or in method chain style

var query =
session.Query.All<Status>()
    .Select(s => new {s, f1 = (s.Style as Style1).Field1})
    .Where(@t1 => @t1.f1.Contains("as") || @t1.f1.Contains("a2"))
    .Select(@t1 => @t1.s);

Produces:

SELECT [a].[id], 
       100 AS [TypeId], 
       [a].[name], 
       [a].[description], 
       [a].[style.id] 
FROM   [dbo].[status] [a] 
       LEFT OUTER JOIN [dbo].[style1] [b] 
                       INNER JOIN [dbo].[stylebase] [c] 
                               ON ( [c].[id] = [b].[id] ) 
                    ON ( [a].[style.id] = [c].[id] ) 
WHERE  ( ( [b].[field1] LIKE N'%as%' ) 
          OR ( [b].[field1] LIKE N'%a2%' ) );

Subscription:

Related questions