1
1

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' ) );

asked Jan 16 at 03:04

Anton%20Guschin's gravatar image

Anton Guschin
73233035


5 Answers:

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 at 02:51

Alexey%20Kulakov's gravatar image

Alexey Kulakov
48215

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

answered Jan 30 at 04:12

k_v_kuznetsov's gravatar image

k_v_kuznetsov
34226

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 at 06:24) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 08:41) k_v_kuznetsov k_v_kuznetsov's gravatar image

And if write .Where(s => (s.Style as Style1).Field1.In("asas", "asas1", "asas2")) it will be shorter and, probably, optimized

answered Feb 01 at 12:51

pil0t's gravatar image

pil0t
207535763

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 at 02:00

k_v_kuznetsov's gravatar image

k_v_kuznetsov
34226

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 at 06:31) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 06:40) k_v_kuznetsov k_v_kuznetsov's gravatar image

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 at 09:41) Alexey Kulakov Alexey%20Kulakov's gravatar image

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 at 09:48) Alexey Kulakov Alexey%20Kulakov's gravatar image
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%' ) );

answered Feb 03 at 02:13

pil0t's gravatar image

pil0t
207535763

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