Некоторые аспекты оптимизации LINQ-запросов в C#.NET для MS SQL Server

  • Tutorial
LINQ вошел в .NET как новый мощный язык манипуляции с данными. LINQ to SQL как часть его позволяет достаточно удобно общаться с СУБД с помощью например Entity Framework. Однако, достаточно часто применяя его, разработчики забывают смотреть на то, какой именно SQL-запрос будет генерировать queryable provider, в вашем случае — Entity Framework.

Разберем два основных момента на примере.
Для этого в SQL Server создадим базу данных Test, а в ней создадим две таблицы с помощью следующего запроса:

Создание таблиц
USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Ref](
	[ID] [int] NOT NULL,
	[ID2] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ref] ADD  CONSTRAINT [DF_Ref_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Ref_ID] [int] NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
	[Ref_ID2] [int] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_Ref_ID]  DEFAULT ((0)) FOR [Ref_ID]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO


Теперь заполним таблицу Ref с помощью запуска следующего скрипта:

Заполнение таблицы Ref
USE [TEST]
GO

DECLARE @ind INT=1;

WHILE(@ind<1200000)
BEGIN
	INSERT INTO [dbo].[Ref]
           ([ID]
           ,[ID2]
           ,[Name])
    SELECT
           @ind
           ,@ind
           ,CAST(@ind AS NVARCHAR(255));

	SET @ind=@ind+1;
END 
GO


Аналогично заполним таблицу Customer с помощью следующего скрипта:

Заполнение таблицы Customer
USE [TEST]
GO

DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;

WHILE(@ind<=12000000)
BEGIN
	IF(@ind%3=0) SET @ind_ref=1;
	ELSE IF (@ind%5=0) SET @ind_ref=2;
	ELSE IF (@ind%7=0) SET @ind_ref=3;
	ELSE IF (@ind%11=0) SET @ind_ref=4;
	ELSE IF (@ind%13=0) SET @ind_ref=5;
	ELSE IF (@ind%17=0) SET @ind_ref=6;
	ELSE IF (@ind%19=0) SET @ind_ref=7;
	ELSE IF (@ind%23=0) SET @ind_ref=8;
	ELSE IF (@ind%29=0) SET @ind_ref=9;
	ELSE IF (@ind%31=0) SET @ind_ref=10;
	ELSE IF (@ind%37=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind%1190000;
	
	INSERT INTO [dbo].[Customer]
	           ([ID]
	           ,[Name]
	           ,[Ref_ID]
	           ,[Ref_ID2])
	     SELECT
	           @ind,
	           CAST(@ind AS NVARCHAR(255)),
	           @ind_ref,
	           @ind_ref;


	SET @ind=@ind+1;
END
GO


Таким образом мы получили две таблицы, в одной из которых более 1 млн строк данных, а в другой-более 10 млн строк данных.

Теперь в Visual Studio необходимо создать тестовый проект Visual C# Console App (.NET Framework):

image

Далее, необходимо для взаимодействия с базой данных добавить библиотеку для Entity Framework.
Чтобы ее добавить, нажмем на проект правой кнопкой мыши и выберем в контекстном меню Manage NuGet Packages:

image

Затем в появившемся окне управления NuGet-пакетами в окне поиска введем слово «Entity Framework» и выберем пакет Entity Framework и установим его:

image

Далее в файле App.config после закрытия элемента configSections необходимо добавить следующий блок:

<connectionStrings>
    <add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

В connectionString нужно вписать строку подключения.

Теперь создадим в отдельных файлах 3 интерфейса:

  1. Реализация интерфейса IBaseEntityID
    namespace TestLINQ
    {
        public interface IBaseEntityID
        {
            int ID { get; set; }
        }
    }
    

  2. Реализация интерфейса IBaseEntityName
    namespace TestLINQ
    {
        public interface IBaseEntityName
        {
            string Name { get; set; }
        }
    }
    

  3. Реализация интерфейса IBaseNameInsertUTCDate
    namespace TestLINQ
    {
        public interface IBaseNameInsertUTCDate
        {
            DateTime InsertUTCDate { get; set; }
        }
    }
    


И в отдельном файле создадим базовый класс BaseEntity для наших двух сущностей, в который войдут общие поля:

Реализация базового класса BaseEntity
namespace TestLINQ
{
    public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime InsertUTCDate { get; set; }
    }
}


Далее в отдельных файлах создадим наши две сущности:

  1. Реализация класса Ref
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace TestLINQ
    {
        [Table("Ref")]
        public class Ref : BaseEntity
        {
            public int ID2 { get; set; }
        }
    }
    

  2. Реализация класса Customer
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace TestLINQ
    {
        [Table("Customer")]
        public class Customer: BaseEntity
        {
            public int Ref_ID { get; set; }
            public int Ref_ID2 { get; set; }
        }
    }
    


Теперь создадим в отдельном файле контекст UserContext:

Реализация класса UserContex
using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer<UserContext>(null);
        }

        public DbSet<Customer> Customer { get; set; }
        public DbSet<Ref> Ref { get; set; }
    }
}


Получили готовое решение для проведения тестов по оптимизации с LINQ to SQL через EF для MS SQL Server:

image

Теперь в файл Program.cs введем следующий код:

Файл Program.cs
using System;
using System.Collections.Generic;
using System.Linq;

namespace TestLINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserContext db = new UserContext())
            {
                var dblog = new List<string>();
                db.Database.Log = dblog.Add;

                var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

                var result = query.Take(1000).ToList();

                Console.WriteLine(dblog[1]);

                Console.ReadKey();
            }
        }
    }
}


Далее запустим наш проект.

В конце работы на консоль будет выведено:

Сгенерированный SQL-запрос
SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])


Т. е. в целом весьма неплохо LINQ-запрос сгенерировал SQL-запрос к СУБД MS SQL Server.

Теперь изменим условие И на ИЛИ в LINQ-запросе:

LINQ-запрос
var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                || (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };


И вновь запустим наше приложение.

Выполнение вылетит с ошибкой, связанной с превышением времени выполнения команды в 30 сек:

image

Если посмотреть какой запрос при этом был сгенерирован LINQ:

image
, то можно убедиться в том, что выборка происходит через декартово произведение двух множеств (таблиц):

Сгенерированный SQL-запрос
SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    CROSS JOIN [dbo].[Ref] AS [Extent2]
    WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]


Давайте перепишем LINQ-запрос следующим образом:

Оптимизированный LINQ-запрос
var query = (from e1 in db.Customer
                   join e2 in db.Ref
                   on e1.Ref_ID equals e2.ID
                   select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
                        from e1 in db.Customer
                        join e2 in db.Ref
                        on e1.Ref_ID2 equals e2.ID2
                        select new { Data1 = e1.Name, Data2 = e2.Name });


Тогда получим следующий SQL-запрос:

SQL-запрос
SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3]
    FROM ( SELECT DISTINCT TOP (1000) 
        [UnionAll1].[C1] AS [C1], 
        [UnionAll1].[Name] AS [C2], 
        [UnionAll1].[Name1] AS [C3]
        FROM  (SELECT 
            1 AS [C1], 
            [Extent1].[Name] AS [Name], 
            [Extent2].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent1]
            INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
        UNION ALL
            SELECT 
            1 AS [C1], 
            [Extent3].[Name] AS [Name], 
            [Extent4].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent3]
            INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
    )  AS [Limit1]


Увы, но в LINQ-запросах условие соединения может быть только одно, потому здесь возможно сделать эквивалентный запрос через два запроса по каждому условию с последующим объединением их через Union для удаления дубликатов среди строк.
Да, запросы в общем случае получатся неэквивалентными с тем учетом, что могут быть возвращены полные дубликаты строк. Однако, в реальной жизни полные дублирующие строки не нужны и от них стараются избавляться.

Теперь сравним планы выполнения двух этих запросов:

  1. для CROSS JOIN в среднем время выполнения 195 сек:
    image
  2. для INNER JOIN-UNION в среднем время выполнения менее 24 сек:

Как видно из результатов, для двух таблиц с миллионами записей оптимизированный LINQ-запрос работает в разы быстрее, чем неоптимизированный.

Для варианта с И в условиях LINQ-запрос вида:

LINQ-запрос
var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };


почти всегда будет сгенерирован правильный SQL-запрос, который будет выполняться в среднем примерно 1 сек:

image
Также для манипуляций LINQ to Objects вместо запроса вида:

LINQ-запрос (1-й вариант)
var query = from e1 in seq1
                            from e2 in seq2
                            where (e1.Key1==e2.Key1)
                               && (e1.Key2==e2.Key2)
                            select new { Data1 = e1.Data, Data2 = e2.Data };


можно использовать запрос вида:

LINQ-запрос (2-й вариант)
var query = from e1 in seq1
                            join e2 in seq2
                            on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
                            select new { Data1 = e1.Data, Data2 = e2.Data };


где:

Определение двух массивов
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };


, а тип Para определяется следующим образом:

Определение типа Para
class Para
{
        public int Key1, Key2;
        public string Data;
}


Таким образом мы рассмотрели некоторые аспекты в оптимизации LINQ-запросов к MS SQL Server.

К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.

Также небольшой обзор проводился и здесь.

Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится здесь.
Также в этом репозитории в папке Plans находятся планы выполнения запросов с условиями ИЛИ.
Поддержать автора
Поделиться публикацией

Похожие публикации

Комментарии 374

    +8
    LINQ вошел в .NET как новый мощный язык манипуляции с данными. LINQ to SQL как часть его позволяет достаточно удобно общаться с СУБД с помощью например Entity Framework. Однако, достаточно часто применяя его, разработчики забывают смотреть на то, какой именно SQL-запрос будет генерировать LINQ.

    Никак не отменяя сказанного в статье далее, хочу заметить одну вещь: за генерацию запросов к СУБД LINQ не отвечает. За это отвечает queryable provider, в вашем случае — Entity Framework. Смените провайдер — получите другие запросы, другие ошибки и другие оптимизации.

      –2
      Так может предложите queryable provider, который этот запрос сделает правильно? У Вас же большой опыт работы с LINQ.
        +2

        понятие правильности субъективно. но это еще не все, понятие правильности в контексте конкретной БД зависит только от конфигурации этой БД, а именно от сгенерированного этой самой БД плана выполнения запроса.


        как думаете, существует ли query провайдер, умеющий собирать с БД несколько разных планов, для каждого конкретного linq-выражения, генерируя различные варианты sql запроса, а потом еще и определять правильность каждого?

        0
        Большое спасибо за уточнение-поправил
        +2
        Интереснее в данном случае выглядит ваш диалог на stackoverflow с Technology Architect David Browne из Microsoft (как я понял, он как раз отвечает то ли за LINQ, то ли за сам MS SQL Server), который утверждает, что запрос с CROSS JOIN должен выполняться как и запрос с UNION. Но после того как вы привели планы, он где-то потерялся.
          0
          Да, за сам запрос утверждал, что inner и cross дадут одинаковые планы.
          Структуры планов будут одинаковыми в большинстве случаев, но выподнение каждого его элемента будет отличаться.
          Каждый может ошибаться-все мы люди.
          И потому внимание заострил на технических аспектах, а не на ошибки человека пусть даже из Microsoft
            +1
            Да, за сам запрос утверждал, что inner и cross дадут одинаковые планы.

            Кстати и логически и судя по плану, они действительно будут одинаковыми.

            Единственное чего я не могу понять, у вас в создании таблиц нету индекса по ID2 в Ref соответственно не совсем понятно, что она во втором подзапросе делает (по вашему скрину не понятно), по идее она должна была по этому несуществующему индексу бежать (и сейчас там идет Index Scan вместо Index Seek, и вам просто повезло что так быстро нашлись в Ref нужные записи). А можете индекс по ID2 в Ref добавить и проверить первый запрос?
              0
              Индексы добавлял как по разным полям, так и общий:
              cross join план не изменился, а inner join-union стал лучше
                +1

                Ну я не то чтобы сильно удивлён. Просто для чистоты эксперимента надо было проверить.

                  0
                  Согласен
            +1
            Technology Architect David Browne из Microsoft (как я понял, он как раз отвечает то ли за LINQ, то ли за сам MS SQL Server)

            Если не секрет, из чего вы это поняли? Вот описание его позиции:


            I'm a Technical Architect working at the Microsoft Technology Center in Dallas, helping customers design and implement solutions on the Microsoft platform
              0

              По косвенным признакам (в частности по его блогу). Но возможно я ошибся, и в Майкрософт такие шильдики раздают всем подряд.

                0

                Какие шильдики?

                  0
                  Architect'ы всякие.
                    +1

                    Нет, позиция technical architect подразумевает определенные обязанности. Не каждому.

              0
              который утверждает, что запрос с CROSS JOIN должен выполняться как и запрос с UNION

              Я вот не нашел в дискуссии такого утверждения.


              Что, конечно, не отменяет того, что EF генерит неоптимальный запрос для данного конкретного случая. Вопрос в том, всегда ли альтернативный запрос оптимальнее.

                +2

                Проблема-то не в запросах, а в планах, сами-то запросы эквивалентны.


                Я вот не уверен, что оптимизация запросов должна быть задачей именно Linq провайдера, а не оптимизатора запросов СУБД.


                От Linq провайдера же я ожидаю прозрачность: построение SQL запроса должно быть предсказуемым и не должно зависеть от каких-то хитрых эвристик.

                  0
                  Это как раз большое заблуждение.
                  Всегда нужно понимать что происходит за кадром хотя бы примерно.
                  Иначе с каждым новым уровнем абстракций Вы все ближе будете к бизнес-процессам или предметным процессам, но все меньше будете разработчиком и программистом.
                  Никакая мащина, никакой фреймворк, никакая суперумная сеть не сможет всегда делать хорошо на 100% свою работу без определенных оптимизаций на всех уровнях абстракций.
                    0

                    Ну да, нужно. Но если вы за понимание — почему вы выступаете против того, чтобы LINQ провайдеры занимались трансляцией и не лезли в оптимизацию?

                      0
                      Где я такое утверждал?
                      Я расписал, что как лучше сделать запросы в LINQ некоторые приемы и все
                        0

                        Вот тут:


                        Это как раз большое заблуждение.

                        Если вы имели в виду что-то другое, то я не понимаю что именно.

                          0
                          «Я вот не уверен, что оптимизация запросов должна быть задачей именно Linq провайдера, а не оптимизатора запросов СУБД» и «От Linq провайдера же я ожидаю прозрачность: построение SQL запроса должно быть предсказуемым и не должно зависеть от каких-то хитрых эвристик», т к эвристика всегда будет при выборе оптимального плана как и его построение. Также эвристика будет и при создании SQL-запроса оптимального для данных условий.
                          Плюс что выше я написал:
                          «Всегда нужно понимать что происходит за кадром хотя бы примерно.
                          Иначе с каждым новым уровнем абстракций Вы все ближе будете к бизнес-процессам или предметным процессам, но все меньше будете разработчиком и программистом.
                          Никакая мащина, никакой фреймворк, никакая суперумная сеть не сможет всегда делать хорошо на 100% свою работу без определенных оптимизаций на всех уровнях абстракций»
                          Именно такого утверждения «почему вы выступаете против того, чтобы LINQ провайдеры занимались трансляцией и не лезли в оптимизацию?» я не делал.
                            +2

                            Брр. Вот смотрите, я пишу что LINQ провайдеры должны заниматься трансляцией и не лезть в оптимизацию. Вы пишите что я заблуждаюсь — а потом сразу же отрицаете, что выступаете против того что LINQ провайдеры должны заниматься трансляцией и не лезть в оптимизацию.


                            Так все-таки, что должны делать LINQ провайдеры?

                              0
                              Оптимальным образом генерировать SQL-запросы. Но это эвристика и потому программисту надо писать оптимальные LINQ-запросы
                                +1

                                Чтобы программисту писать оптимальные LINQ-запросы, эти запросы должны оставлять меньше сюрпризов. Что противоречит идее "оптимальной" генерации SQL-запросов.

                                  0
                                  Проблема в эвристике-без нее никуда и нужно заранее думать что будет за кадром, помочь всегда писать предсказуемый запрос
                      +2
                      Всегда нужно понимать что происходит за кадром хотя бы примерно.

                      Ключевое слово тут примерно. Иначе так можно до мышей машинного кода дойти.
                      Иначе с каждым новым уровнем абстракций Вы все ближе будете к бизнес-процессам или предметным процессам, но все меньше будете разработчиком и программистом.

                      Ну тут сложный философский вопрос. Пилот современного Боинга — меньший пилот, чем пилот кукурузника?
                      Никакая мащина, никакой фреймворк, никакая суперумная сеть не сможет всегда делать хорошо на 100% свою работу без определенных оптимизаций на всех уровнях абстракций.

                      Это палка о двух концах. Premature оптимизацию тоже никто не отменял.
                        0
                        Иначе с каждым новым уровнем абстракций Вы все ближе будете к бизнес-процессам или предметным процессам,

                        … это и есть задача этих абстракций.


                        но все меньше будете разработчиком и программистом.

                        А вот это совершенно не обязательно.

                        +1

                        Я, в принципе, согласен с тем, что построение должно быть предсказуемым, а оптимизацией должна заниматься СУБД. Но с другой стороны, провайдер — он для конкретной СУБД. Если для этой СУБД известно, что из двух семантически эквивалентных запросов один всегда выигрышнее по производительности, почему бы провайдеру не выбирать его? Опять же, я понимаю, что есть сложность разработки, время и вот это все. Но концептуально в этом есть свой пойнт.

                          0
                          Так пока не сделали оптимизацию на должном уровне-нужно это учитывать. Я об этом. В остальном согласен.
                        0
                        Inner join с union в большинстве случаев будет быстрее cross join. А в данном споре было вообще просто cross и inner соединения. Это утверждение прямо следует из реляционной алгебры
                          0
                          А в данном споре было вообще просто cross и inner соединения.

                          А для них планы запросов полностью эквиваленты.


                          select top 1000 e1.Name, e2.Name
                          from Customer as e1
                              cross join Ref as e2
                          where e1.Ref_ID = e2.ID and e1.Ref_ID2 = e2.ID2

                          vs


                          select top 1000 e1.Name, e2.Name
                          from Customer as e1
                              inner join Ref as e2 on e1.Ref_ID = e2.ID and e1.Ref_ID2 = e2.ID2

                          Дает одинаковый план выполнения с clustered scan + clustered seek c seek predicate Seek Keys[1]: Prefix: [Test].[dbo].[Ref].ID = Scalar Operator([Test].[dbo].[Customer].[Ref_ID] as [e1].[Ref_ID]), над которыми nested loops join.

                            0
                            Структуры одинаковые, но каждый элемент будет выполняться по разному, потому и разность будет во времени выполнения.
                              0

                              Нет, не будет. Там полностью идентичные поддеревья. В SSMS есть Showplan comparison, ровно для этого.

                                +1
                                Не думал, что когда-нибудь скажу это, но согласен с lair.

                                Собственно cross join это и есть inner join ON TRUE.
                                  0
                                  Так, коллеги здесь в статье разбирается CROSS JOIN и INNER JOIN-UNION с условием ИЛИ-сравнить можете и сами.
                                  С тем архитектором техническим спор был с условием И между CROSS JOIN и INNER JOIN-также картинки там были приложены
                                    0
                                    Так, коллеги здесь в статье разбирается CROSS JOIN и INNER JOIN-UNION с условием ИЛИ-сравнить можете и сами.

                                    Поучительно посмотреть на разницу между INNER JOIN по OR и UNION двух INNER JOIN. Ниже gandjustas уже объяснил, почему так получается.


                                    У меня, кстати, есть весьма важный вопрос: а какой бизнес-задаче соответствует ваша модель данных, и почему она сделана именно такой, а не так, как принято в EF? В частности, почему вы делаете JOIN, а не переходы по навигационным свойствам?


                                    В частности, изменится ли что-то, если Ref.ID и Ref.ID2 будут оба ссылаться на Customer.ID?


                                    С тем архитектором техническим спор был с условием И между CROSS JOIN и INNER JOIN-также картинки там были приложены

                                    В тех картинках хорошо видно, что у вас разные условия в CROSS JOIN и INNER JOIN (проще говоря, вы условие джойна в CROSS JOIN пропустили вообще). При одинаковом условии JOIN планы запросов одинаковые.

                                      0
                                      «В тех картинках хорошо видно, что у вас разные условия в CROSS JOIN и INNER JOIN (проще говоря, вы условие джойна в CROSS JOIN пропустили вообще)»-там на картинке видно, что условие есть «where r.ID=1».
                                      К этим тестам привел меня анализ запросов на MS SQL и код ревью LINQ-запросов.
                                      В тестах максимально упростили ситуацию и фильтры.
                                      Но если на атомарном уровне так, то можно легко догадаться что на проде будет.
                                      Здесь же важно было заострить внимание как лучше переписать LINQ-запрос для генерации оптимального SQL-запроса.
                                        +3
                                        там на картинке видно, что условие есть «where r.ID=1».

                                        Ну так WHERE r.ID=1 — это ограничение по одной таблице из двух, а где c.[Ref_ID] = r.[ID]?


                                        Но если на атомарном уровне так, то можно легко догадаться что на проде будет.

                                        Нет, нельзя. Потому что другие отношения между данными приводят к другим запросам.

                                          0
                                          да уже увидел, спасибо
                              +3

                              Я больше того скажу, планы запросов


                              select top 100 e1.Name, e2.Name
                              from Customer as e1
                                  cross join Ref as e2
                              where e1.Ref_ID = e2.ID or e1.Ref_ID2 = e2.ID2

                              и


                              select top 100 e1.Name, e2.Name
                              from Customer as e1
                                  inner join Ref as e2 on e1.Ref_ID = e2.ID or e1.Ref_ID2 = e2.ID2

                              (при добавленном индексе на Ref.ID2, покрывающем Name) тоже полностью эквивалентны. Собственно, у них и время выполнения одинаковое: 26 секунд.

                                0
                                Для следующих запросов у меня планы одинаковые, но обработка данных в них разная:
                                SELECT *
                                    FROM  [dbo].[Customer] AS c
                                    CROSS JOIN [dbo].[Ref] AS r
                                    WHERE c.[Ref_ID] = r.[ID]
                                	AND r.[ID]=1
                                

                                и
                                SELECT *
                                    FROM  [dbo].[Customer] AS c
                                    INNER JOIN [dbo].[Ref] AS r ON c.[Ref_ID] = r.[ID]
                                	WHERE r.ID=1
                                

                                Это те запросы, которые были здесь
                                И там же приложены картинки планов.
                                В этой же статье обсуждается условие ИЛИ и сравниваются CROSS JOIN с INNER JOIN-UNION, а не просто с INNER JOIN
                                  +3

                                  А у меня опять-таки, планы запросов полностью одинаковые: clustered index scan Customer для RefID=1 и clustered index seek Ref для ID=1. А по вашей ссылке в запросе с CROSS нет условия c.[Ref_ID] = r.[ID] (на что вам там указали).

                                    +1
                                    так стоп-CROSS JOIN и выполняется без условия соединения.
                                    Хотя погодите-да блин)
                                    Надо было ее в конец записать-тот тест был неверным.
                                    Благодарю за поправку)
                            0
                            Я вот не нашел в дискуссии такого утверждения.

                            There's no obvious reason why you shouldn't use the query that generates a CROSS JOIN
                            And the query may be very expensive, but changing it from a CROSS JOIN to some other kind of join isn't really going to help
                            Вопрос в том, всегда ли альтернативный запрос оптимальнее.

                            С такой статистикой таблиц и такими индексами всегда.
                              0
                              There's no obvious reason why you shouldn't use the query that generates a CROSS JOIN
                              And the query may be very expensive, but changing it from a CROSS JOIN to some other kind of join isn't really going to help

                              И ни слова про union. Разговор просто про замену CROSS JOIN на другой JOIN.


                              С такой статистикой таблиц и такими индексами всегда.

                              Это и есть не всегда.

                          0

                          А если индексы добавить?

                            0
                            Индексы на все запоосы не сделать. Однако, cross join проиграет inner join при одинаковых условиях
                              0

                              При желании можно и на все.


                              При наличии индексов план изменится и итоговые различия могут оказаться несущественными.

                                0
                                Можно, но когда разных вариаций сотни и тысячи запросов, то это будет проблематично. Да и неэффективно. На счет индексов-сделал отдельно на каждый столбец, участвующих в соединении и потом один индекс на оба поля. При CROSS JOIN он так и не воспользовался новыми индексами, а при INNER JOIN-UNION стал план лучше.
                                  0

                                  Так вы код покажите. Вижу в плане с CrossJoin Index Scan внизу, который пробегает по таблице Ref столько раз, сколько в Cutomer.
                                  У меня есть предположение, что если в Ref сделать индекс по ID2 include Name, то СУБД догадается что можно сделать два запроса и объединить результаты.


                                  Еще бы неплохо проверить как Foreign key повлияет на запросы.

                                    0
                                    Код весь описан в статье-ничего лишнего нет и в проекте тоже ничего лишнего нет-ну если только по умолчанию XML от EF и прочие вещи самой .NET-среды. Тут же все пошагово описано-можете повторить и убедиться. Код заполнения данных тоже приведен. Сами запросы как на LINQ, так и на T-SQL приложены
                                      +2

                                      Я процитирую вас же:


                                      К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют.

                                      Но сейчас вы сами пытаетесь игнорировать что происходит "за кадром" в оптимизаторе запросов SQL Server.
                                      По факту вы создали довольно плохую схему БД, которая неоптимальна для ваших запросов и пытаетесь на ней доказать какое-то утверждение.
                                      Вам уже больше одного человека предлагают сделать схему оптимальнее и код написать более похожий на реальный.


                                      Вы при этом настойчиво отбиваетесь, предлагая другим все сделать самостоятельно, хотя вы даже не потрудились выложить исходники в открытый доступ. Думаете кто-то реально будет ваш код из статьи копировать?

                                        0
                                        Это еще упрощенный вариант.
                                        На продах обычно еще сложнее и хуже.
                                        А что не так со схемой БД? Что не так с запросами?
                                        Все коды выложены в статье.
                                        Чтобы перепроверить, нужно создать все самому как тут написано и провести замеры, а также убедиться какие запросы создает EF к MS SQL Server.
                                        На гит обычно выкладывают рабочие проекты, а не маленькую тестовую программу, которую легко можно описать в статье в том числе и весь ее код. Что и было сделано.
                                        Также я в личке ответил Вам, что пришлю Вам в архиве весь проект и скрипт создания БД, если Вы укажите свой адрес. Но тоже есть и в публикации.
                                          0

                                          В схеме не хватает индексов, не хватает внешних ключей.


                                          Код двух запросов у вас не эквивалентный, потому что UNION удаляет дубликаты, а кортеж Ref_ID, Costomer.Name,Ref.Name уникальность никак не гарантирует.


                                          Планы запросов приведены без костов, а поэтому есть вероятность что в первом плане были physical reads, а во втором нет.


                                          Код выложенный в статье невозможно скомпилировать и запустить. От этого теряется повторяемость эксперимента.

                                            0
                                            Планы запросов приведены без костов, а поэтому есть вероятность что в первом плане были physical reads, а во втором нет.

                                            Ну по планам уже очевидно, что проблема не в этом. И не в индексах. Проблема в том, что ни SQL Server, ни LINQ не оптимизируют исходный запрос с OR. С другой стороны всегда можно сказать «не держите его так».
                                              0

                                              Как раз по планам и не очевидно. Потому что утверждать что план А лучше Б можно, смотря на косты (estimated cost).


                                              Если при этом план с большим костом оказывается быстрее, то значит вы сами себя где-то обманули.


                                              В этом примере обман заключается в том, что не имея статистики по распределению значений ключей соединения SQL Server считает количество совпадений в JOIN равными и небольшими. Можно в books online посмотреть сколько он там считает по дефолту. А реальная статистика показывает что 100 есть совпадения по первому условию.


                                              И проблема вовсе не в EF.

                                              0
                                              Что мешает повторить и в случае другого результата приложить этот результат?
                              0
                              Так есть же все индексы как раз. И именно благодаря их использованию, второй план выполняется быстро. Она и в первом и втором подзапросе бежит по Customer и по индексу быстро находит Ref, пробежав 1000 записей сразу выходит. А в первом плане так как условие OR, все индексы сразу отключаются (в современных SQL серверах оптимизаторы вообще не сильно умеют с OR'ами работать), и она по сути перебором ищет первые 1000 записей: то есть берет первую из Customer и первую из Ref, проверяет подошло или нет (естественно нет), берет вторую из Ref и т.п.
                                +2

                                Для начала почитайте различия между Index и Clustered Index.
                                Второй план выполняется быстро не благодаря кластерному индексу (любой человек в теме посмеется над этим выражением), а благодаря перекосу в данных. Перекос заключается в том, что первая часть условия джоина, которая перед OR, покрывает 100% строк, поэтому по факту выполняется запрос


                                SELECT TOP (1000) 
                                    [Extent1].[Ref_ID] AS [Ref_ID], 
                                    [Extent1].[Name] AS [Name], 
                                    [Extent2].[Name] AS [Name1]
                                    FROM  [dbo].[Customer] AS [Extent1]
                                    CROSS JOIN [dbo].[Ref] AS [Extent2]
                                    WHERE [Extent1].[Ref_ID] = [Extent2].[ID] 

                                SQL Server естественно об этом не знает, поэтому для запроса с OR генерирует "универсальный" план. Который оказывается крайне ущербным для такой схемы.


                                Если сделать индексы и внешние ключи, то SQL Server построит статистику и вполне сможет построить план оптимальнее. А если убрать перекос в данных, то вполне возможно что оба запроса будут примерно одинаково работать.


                                Оптимизатор SQL Server довольно хорошо работает с OR в условиях, вплоть до того, что может сделать два параллельных запроса к индексам и объединить их результаты.

                                  0
                                  Не хочу Вас чем-либо оскорбить, но в данном случае Вы сильно ошибаетесь.
                                  Повторите у себя все то, что я писал выше и с индексами и с FK и убедитесь сами.
                                  По планам как раз видно, что индексы выручают сильно.
                                    0

                                    В чем ошибаюсь? Думаете я план читать не умею?
                                    Может покажете estimated косты запросов?
                                    Может сделаете индексы и проверите еще раз?


                                    Вы про какие индексы? Которые не показываете? Или которых нет на тех скриншотах, которые вы привели?

                                  0
                                  Какой перекос и какие 100% строк она покрывает?

                                  Там два подзапроса, каждый из которых выполняется независимо. И я всего лишь пересказал этот план простыми словами. Да там не хватает индекса по ID2, но автор говорил что построил его, и как я понял в плане второго подзапроса Cluster Index Scan, поменялось на Index Seek (уже не clustered но не суть), а первый не изменился никак.

                                  Если сделать индексы и внешние ключи, то SQL Server построит статистику и вполне сможет построить план оптимальнее. А если убрать перекос в данных, то вполне возможно что оба запроса будут примерно одинаково работать.

                                  Это каким образом? План с union'ами очевиден и понятен и всегда будет работать быстро. С cross join по вашему какой план она должна построить, даже при правильной статистике и индексах?
                                    +1
                                    Какой перекос и какие 100% строк она покрывает?

                                    Попробую объяснить по простому.
                                    Вот SQL Server видит условие джоина:


                                    ([Extent1].[Ref_ID] = [Extent2].[ID]) OR ([Extent1].[Ref_ID2] = [Extent2].[ID2])

                                    Он не знает ничего про значения в Ref_ID, Ref_ID2 и ID2, поэтому считает условно вероятность выполнения обоих условий одинаковым.
                                    План строит исходя из этого предположения.


                                    По факту у нас для первых 1000 строк из Customer всегда находится строка в Ref, соответствующая условию [Extent1].[Ref_ID] = [Extent2].[ID]


                                    Поэтому вручную переписанный запрос, поделенный на две части, выполняется полностью в первой части, до второй даже не доходит.


                                    Подзапросы выполняются не независимо, а последовательно. Как будет выполняться вторая часть зависит от первой. Вы просто не умеете план читать.


                                    Это каким образом? План с union'ами очевиден и понятен и всегда будет работать быстро.

                                    Слишком сильное заявление.


                                    С cross join по вашему какой план она должна построить, даже при правильной статистике и индексах?

                                    Идеально это запрос к двум индексами и склейка результатов.


                                    Да там не хватает индекса по ID2, но автор говорил что построил его, и как я понял в плане второго подзапроса Cluster Index Scan, поменялось на Index Seek (уже не clustered но не суть), а первый не изменился никак.

                                    Сорри, но в вопросах быстродействия нельзя верить на слово.

                                      0
                                      По факту у нас для первых 1000 строк из Customer всегда находится строка в Ref, соответствующая условию [Extent1].[Ref_ID] = [Extent2].[ID]
                                      Поэтому вручную переписанный запрос, поделенный на две части, выполняется полностью в первой части, до второй даже не доходит.

                                      Да, согласен, 0 во втором подзапросе я не заметил. Только что это меняет? То есть это объясняет, почему отсутствие индекса по ID2 не повредило запросу, но не более.
                                      Про перекос и 100% все равно не понял. То есть то что по каждому Ref_ID есть запись в Ref с ID это по вашему перекос? Вообще это как раз обычная ситуация, в обратную сторону был бы перекос.
                                      Слишком сильное заявление.

                                      Ок, при данной статистике и индексах.
                                      Идеально это запрос к двум индексами и склейка результатов.

                                      Это и есть второй запрос. Но как видим она это не делает. На postgres точно, жаль ms sql под рукой нет воспроизвести, это минут 10 работы.
                                      Сорри, но в вопросах быстродействия нельзя верить на слово.

                                      Ок, автор обновите план пожалуйста, а от не отстанут же :).
                                        0
                                        Я выложил в гитхаб-сможет любой проверить и поэкспериментировать:
                                        github.com/jobgemws/TestLINQ
                                          0
                                          Да, согласен, 0 во втором подзапросе я не заметил. Только что это меняет? То есть это объясняет, почему отсутствие индекса по ID2 не повредило запросу, но не более.

                                          Отсутствие индекса и статистики повредило первому запросу, который без UNION. Это самое главное. Оптимизатор SQL Server достаточно умный, но он не всегда обладает данными.


                                          Ок, при данной статистике и индексах.

                                          Которых нет, ага.


                                          Это и есть второй запрос. Но как видим она это не делает.

                                          Это потому что нет статистики и индексов (как минимум), может еще и другие причины есть, но без индексов точно не сделает такой запрос.


                                          На postgres точно, жаль ms sql под рукой нет воспроизвести, это минут 10 работы.

                                          На postgres в разы более слабы оптимизтор. От не использует и 10% матана, который заложен в оптимизатор SQL Server.

                                            0
                                            Так в итоге при ИЛИ в условии у Вас CROSS JOIN стал быстрее чем INNER JOIN-UNION?
                                              +1

                                              Я еще не смотрел, посмотрю опишусь.

                                                0

                                                Дело не в CROSS JOIN. Собственно, ваш запрос полностью эквивалентен INNER JOIN с OR внутри. Это, к сожалению, не помогает SQL Server построить более эффективный (на этих данных) план выполнения.


                                                Почему — вопрос интересный. Но это вопрос к MS SQL, а не к EF.

                                                  0
                                                  Интересовало время выполнения и получается, что при LINQ-запросе с UNION быстрее, чем LINQ с from-from. Первый сгенерирует inner join с union, а второй-cross join.
                                                  Более того, по этим выявленным результатам были переписаны некоторые LINQ-запросы (самые часто выполняемые), и взаимоблокировки полностью ушли с одного продовского сервера, скорость выполнения запросо также возросла.
                                                    0

                                                    INNER и CROSS+WHERE с одинаковым предикатом дают одинаковые планы, это гарантирует вам SQL SERVER


                                                    CROSS + WHERE A OR B в общем случае не дает такой же результат, как
                                                    INNER A UNION INNER B или INNER A UNION ALL INNER B. Первый может дать меньше строк, второй больше. Это не эквивалентные запросы в общем случае и точно LINQ не будет делать такое преобразование.

                                                      0
                                                      Да, математически это разные запросы.
                                                      Но на практике полные дубли строк обычно не нужны.
                                                      В нашем случае были нужны недублирующие строки.
                                                      Честно говоря, не могу вспомнить где были нужны полные дубли для вывода.
                                                        +1

                                                        Важно то, что вы пытаетесь сравнить неодинаковые запросы.

                                                          0
                                                          Здесь был приведен пример и его оптимизация для получения нужной выборки. Полные дубликаты обычно не нужны и в основном от них как-раз избавляются
                                                            +1

                                                            Это не совсем правильный (я бы даже сказал совсем неправильный) подход к оптимизации.


                                                            Во-первых оптимизация БД начинается с правильной схемы, а потом уже идут правильные запросы. Потому что при правильной схеме встроенный оптимизатор SQL Server прекрасно справляется с запросами, в которых нет очевидных ошибок.


                                                            Во-вторых оптимизация без понимания почему SQL Server сделал такой план, а не другой — это выглядит как попытка выкинуть число на игральных костях.

                                                              0
                                                              Правильно-это только в вакууме. В кровавом энтерпрайзе ищут оптимальное решение-не лучшее заметьте, а именно оптимальное-грамотно взвешенное где хуже можно, а где лучше, но чтобы в целом подходило по ТЗ или по оговоренным рамкам.
                                                                +1

                                                                Я много занимался оптимизацией именно в "кровавом ынтерпрайзе". И в первую очередь смотрел на схему, индексы и ошибки в запросах, а потом уже пытался переписывать запросы, меняя их семантику.

                                                                  0
                                                                  Но не все можно переписать даже в обозримом будущем, когда система уже работает и долго и в нее фигачили и фигачат кучу фич. Более того, не все дадут изменить в обозримом будущем по разным причинам.
                                                                    0

                                                                    Не все и нужно переписывать. Положительный результат достигается обычно локальными изменениями. Тут индекс сделали, там запрос поправили. Тут добавили сурогатный ключ, там таблицу добавили и прописали в 5 запросах джоины.

                                                                      0
                                                                      Вот) я и привел один примерчик-как можно лучше переписать)
                                                                      Результаты потом покажите плиз по Вашим тестам кто круче и при каких обстоятельствах-CROSS JOIN или INNER JOIN с UNION с учетом что дубли строк нам не нужны
                                                      0
                                                      CROSS + WHERE A OR B в общем случае не дает такой же результат, как INNER A UNION INNER B

                                                      О! Я вот смотрю на запросы уже полдня, и мне кажется, что где-то должен быть подвох. Можете привести пример, в каком случае эти запросы не дадут одинаковый результат?


                                                      (за исключением порядка записей, с ним я уже сам повозился)


                                                      UPD: вы про случай, когда INNER (...OR...) даст дубликаты, а UNION их вырежет?

                                                        +2

                                                        Таблицы


                                                        Customer
                                                        | ID    | Name  | ID_Ref | ID_Ref2 |  
                                                        | 1     | XXX   | 1      | 2       | 
                                                        | 2     | XXX   | 2      | 1       | 
                                                        
                                                        Ref
                                                        | ID    | ID 2    | Name   |
                                                        | 1     | 2       | XXX    |
                                                        | 2     | 1       | XXX    |

                                                        Во всех запросах будет SELECT Customer.Name, Ref.Name
                                                        1) INNER + AND — вернет две строки (ХХХ, ХХХ)
                                                        2) INNER + OR — вернет две строки (ХХХ, ХХХ)
                                                        3) CROSS + WHERE сработает аналогично INNER
                                                        4) UNION — вернет одну строку (ХХХ, ХХХ)
                                                        4) UNION ALL — вернет четыре строки (ХХХ, ХХХ)

                                                          0

                                                          Спасибо.

                                                            +1
                                                            Тут проблема в том, что в 4) UNION возвращает наименования, а не ключи. Если бы возвращал уникальные ключи, то никаких проблем бы не было.

                                                            To lair, вот запрос, который генерирует наша «функциональная СУБД»:
                                                            Запрос
                                                            (SELECT t1.key0 AS jkey0,
                                                            t0.key0 AS jkey1,
                                                            t1.key0 AS jprop0,
                                                            t0.key0 AS jprop1
                                                            FROM _auto_Main_Ref t0
                                                            JOIN _auto_Main_Customer t1 ON 1=1
                                                            WHERE t1.Main_refId2_Customer=t0.Main_id2_Ref
                                                            LIMIT 1000)
                                                            UNION
                                                            (SELECT t1.key0 AS jkey0,
                                                            t0.key0 AS jkey1,
                                                            t1.key0 AS jprop0,
                                                            t0.key0 AS jprop1
                                                            FROM _auto_Main_Ref t0
                                                            JOIN _auto_Main_Customer t1 ON 1=1
                                                            WHERE t1.Main_refId_Customer=t0.Main_id_Ref
                                                            LIMIT 1000)
                                                            LIMIT 1000

                                                            Он будет идентичен CROSS JOIN. И я загнал те же самые данные и проверил — даже на PostgreSQL там нормальный план и он выполняется меньше чем за секунду.
                                                              0
                                                              Тут проблема в том, что в 4) UNION возвращает наименования, а не ключи. Если бы возвращал уникальные ключи, то никаких проблем бы не было.

                                                              А если возвращать (только) ключи, то на MS SQL CROSS JOIN (не важно, с DISTINCT или без) выполняется меньше секунды, а UNION — порядка 30 секунд.

                                                                0
                                                                Не понимаю, почему в CROSS JOIN есть вообще разница наименования или ключи возвращать? Это же никак не влияет на вычисление рядов. Почему изменился план?

                                                                А с UNION можно план? Как вообще в нем MS SQL может там получить 30 секунд, если там по сути надо взять два простых запроса, выполнить взяв максимум первые 1000 записей, а затем просто связать (отрубив дубликаты, но на 1000 записей — это мизер).
                                                                  0
                                                                  А с UNION можно план?

                                                                    0
                                                                    А теперь, если в каждый подзапрос UNION вставить TOP 1000?
                                                                      +1

                                                                      Если я ничего не путаю, это недопустимое преобразование, потому что результат в общем случае изменится.

                                                                        0
                                                                        Это почему он изменится? Мы исходим из того, что нужно получить любые тысячу записей. Даже если был бы задан порядок, то логичнее всего получить 1000 первых (по заданному порядку) в первом подзапросе, 1000 первых во втором, затем их объединить и выбрать опять первые 1000.

                                                                        А в этом плане, как я понял, MSSQL берет ВСЕ записи в первом, ВСЕ записи во втором, объединяет, а затем берет первую 1000.
                                                                          +1

                                                                          А нет, если возвращать именно уникальные ключи, и на них делать TOP 1000, то не изменится, здесь вы правы.

                                                                            0

                                                                            (del, подумаю еще)

                                                                          0
                                                                          Коллеги, линк разбираем и как лучше сгенерировать SQL запрос
                                                                            +1

                                                                            Чтобы понять, как лучше сгенерировать SQL-запрос, надо понять, какой запрос надо генерировать.

                                                                              0
                                                                              Железно, полностью согласен
                                                      0
                                                      «На postgres в разы более слабы оптимизатор. От не использует и 10% матана, который заложен в оптимизатор SQL Server.»-откуда такие выводы? СУБД одного типа примерно одинаково должны работать, а уж СУБД, созданная учеными, и подавно. Просто нужно правильно настроить. В предыдущей статье мы с коллегами за несколько недель добились примерно одинаковых показателей для 1С на обеих СУБД.
                                                        0

                                                        И этот факт не отменяет что на postres слабый оптимизатор. Информация по обоим есть в сети, изучайте.

                                                          –1
                                                          Громкое заявление. При правильных настройках будет несильно отличаться по производительности от скуля и оракла.
                                                          В предыдущей статье все детально описано.
                                                            +1
                                                            Ну начнем на самом деле с того, что в postgres нет оптимизации predicate pushdown. Вообще. Как класса.

                                                            То есть запрос на Linq, 1C или голом SQL:
                                                            SELECT A.x FROM (SELECT invoiceid, SUM(quantity) AS x FROM invoiceDetail BY invoice) A JOIN selectedinvoices B ON a.invoiceid = b.invoiceid
                                                            Может смело положить всю базу (если у вас под сотню миллионов строк инвойсов). MS SQL вроде такой частный случай разрулит (правда в более сложных случаях те же яйца будут). И как вы настройками это решите?
                                                              0

                                                              На обеих СУБД такой запрос может вызвать проблемы.
                                                              По возможности лучше его переписать.
                                                              Иначе придется уже на уровне СУБД магией заниматься

                                                                0
                                                                Ну, в MS SQL он не вызовет проблемы, собственно именно поэтому лицензии на MS SQL стоят много денег, в отличии от Postgres.
                                                                Ну и не от вас же зависит, под какую СУБД разработчик проверял свой запрос.
                                                                По возможности лучше его переписать.

                                                                Кому? Вы будете дядю Васю, который на 1С сварганил такой запрос, искать? Который может уже давно в этой компании не работает. Или сами рискнете его поправить?
                                                                  0

                                                                  Я имел в виду, если есть возможность, то сразу писать нормально. В данном случае рассматривались LINQ-запросы и такую оптимизацию заложить можно.
                                                                  А в целом Вы правы-проще поставить скуль и он в большинстве случаев сможет лучше пережевать тот говнокод, что ему будут подавать различные компоненты. А постгрес нужно очень тонко настроить, включая ОС, ФС и само железо, а сама поддержка обойдется дороже, чем на скуле

                                                                0

                                                                Настолько все плохо?


                                                                Я уж хотел ругать postgres за то, что они не убирает бесполезные джоины, типа такого:


                                                                select a.* 
                                                                from A a
                                                                join B b on a.FK = b.PK
                                                                where b.primarykey = x

                                                                Но без predicate pushdown это вообще жопаю. Причем простая оптимизация вроде...

                                                                  0
                                                                  Зато как автомат калашникова. Тупой, но предсказуемо тупой.

                                                                  Потому как справедливости ради и в MS SQL эти predicate pushdown'ы не фонтан. ЕМНИП, он даже с UNION'ами не справляются, не то что с проталкиванием нескольких предикатов. Я просто помню, что когда тестил адаптер под MS SQL и выключал predicate pushdown, который у нас приложение делает, MS SQL точно также во многих случаях начинал всю базу лопатить. Собственно по их патентам видно, что весь их predicate pushdown это набор каких-то эвристик для частных случаев.

                                                                  Да и даже в этом обсуждении видно, что MS SQL DISTINCT TOP 1000 внутрь подзапросов не умеет проталкивать, чего от него еще ожидать.
                                                                    +1

                                                                    Предикат сверху union не такой частый кейс.
                                                                    Насчет эвристик не знаю, примитивная вещь. Даже на уровне linq легко сделать, а уж в оптимизаторе и подавно.


                                                                    Судя по планам TOP 100 как раз прокидывает в самый низ. Заканчивая обход индекса при достижении нужного количество записей в резалтсете.


                                                                    PS. АК прекрасный автомат, за много лет ничего лучше не придумали.

                                                                      0
                                                                      Предикат сверху union не такой частый кейс.
                                                                      Насчет эвристик не знаю, примитивная вещь. Даже на уровне linq легко сделать, а уж в оптимизаторе и подавно.

                                                                      На уровне linq очень многих вещей нельзя сделать, так как у него статистики нет в принципе. Он многие решения физически принимать не может.
                                                                      В оптимизаторе можно много чего сделать, только почему то не делается :(
                                                                      Судя по планам TOP 100 как раз прокидывает в самый низ. Заканчивая обход индекса при достижении нужного количество записей в резалтсете.

                                                                      Как раз наоборот, он поэтому 27 секунд и выполняется, посмотрите обновленную статью и обновленный план (я сам удивился когда увидел)
                                                                      ps: хотя план не обновили, только время выполнения, но он тут где-то сверху в комментах был.
                                                                        0

                                                                        Коллеги, напомню, чтобы так не мучаться с планами каждый раз, нужно создавать такие LINQ-запросы, которые создают предсказуемые для выполнения SQL-запросы. О чем и посвещена публикация для отдельного упрощенного примера.
                                                                        А все, что не удалось в LINQ, то придется делать уже на уровне самой СУБД

                                                                          0
                                                                          нужно создавать такие LINQ-запросы, которые создают предсказуемые для выполнения SQL-запросы

                                                                          То есть как минимум написать свой queryable provider, потому что вы не контролируете, как именно чужой провайдер преобразует LINQ-запрос в SQL.

                                                                            0

                                                                            можно и так, а можно так, чтобы не сильно зависеть от реализации оптимизатора в конкретном провайдере

                                                                              0

                                                                              Я не про оптимизатор говорю, а про транслятор. Вы не можете написать код так, чтобы не зависеть от реализации транслятора (ну или от спецификации, по которой он работает, если таковая вообще существует).

                                                                                0

                                                                                но можно к этому максимально приблизиться

                                                                                  0

                                                                                  … и как же?

                                                                                    0

                                                                                    писал выше и не только выше и многократно в комментариях, и в самой статье в частном порядке разобрано как

                                                                                      –1

                                                                                      Неа. В статье разбрано только то, что на данной версии EF на конкретной конфигурации маппинга конкретный LINQ-запрос дает конкретный SQL-запрос, который вы считаете более выгодным. Нет никакого способа доказать, что EF будет всегда генерить такой SQL-запрос в ответ на такой LINQ-запрос. Нет никакого способа доказать, что этот SQL-запрос всегда выгоднее, чем другой.


                                                                                      Поэтому нет, вы не приблизились к написанию предсказуемых SQL-запросов, вы просто нашли точечное решение, которое сейчас у вас работает. Что, как я неоднократно говорил, само по себе полезно, но не факт, что подлежит обобщению.

                                                                          0

                                                                          linq это не отдельный язык запросов, это способ генерации SQL.
                                                                          В оптимизаторе SQL Server много чего делается, даже чуть больше чем рядовой программист может представить, и делается на практике когда есть индексы и статистика. В их отсутствии оптимизатор не делает практически ничего.


                                                                          Поэтому и 27 секунд, потому что оптимизатор не угадал с порядком джоинов, запрос обошел почти 100% записей чтобы получить нужную тысячу. Но остановился сразу при получении результатов.

                                                                            0

                                                                            вопрос не в том сколько, а в том как он обработает данные-вот в чем заключается главная задача оптимизатора. Статистика ведь может и устареть и не успеть обновиться-тоже из практики. А на индексы уповать для всех сложных запросов не стоит.
                                                                            В оптимизации можно выделить следующие шаги:
                                                                            1) оптимизация и кэширование на уровне всего приложения
                                                                            2) оптимизация LINQ-запроса и SQL-запроса
                                                                            3) и только здесь идет оптимизация на уровне СУБД
                                                                            Я раньше тоже думал, что нужно все оптимизации на СУБД проводить-написал на эту тему десятки статей на разных ресурсах. Но со временем меня осенило-нужно просто писать на всех уровнях абстракций понятный для исполнения код, а не надеяться, что код с душком выполнит система оптимально. Надо мыслить шире и решать причины проблемы на том уровне абстракций, где она и закладывается.
                                                                            Однако, упреждающую оптимизацию в фоне лучше проводить всегда и на всех уровнях абстракций, в том числе и на СУБД.

                                                                              0

                                                                              Статистика автоматом обновляется при изменении 10% таблицы. Так что в реальности она не может успеть устареть настолько сильно, чтобы стать неэффективной.
                                                                              Есть особые случаи на очень больших таблицах, но на паре миллионов все будет работать хорошо.


                                                                              Это опять аргумент в пользу бедных. Даже если индексы и статистика могут не сработать в 1% случаев это не повод их не использовать.

                                                                              0
                                                                              Поэтому и 27 секунд, потому что оптимизатор не угадал с порядком джоинов, запрос обошел почти 100% записей чтобы получить нужную тысячу. Но остановился сразу при получении результатов.

                                                                              Какой порядок Join, вы о чем? Там всего 2 join'а. Как его можно не угадать.
                                                                              Вот план.
                                                                              А вот картинка lair'а:
                                                                              image
                                                                              В обоих случаях видно что никакой TOP внутрь он не протолкнул, а значит ему пришлось на HashJoin вообще перейти. Тег Hash в обоих планах и на картинке это видно.

                                                                              И вы серьезно думаете что и автор и lair не догадались статистику обновить?
                                                                                0

                                                                                Как top протолкнуть в UNION? При текущей схеме и запросах?

                                                                                  0
                                                                                  Что значит как? Взять и протолкнуть:
                                                                                  SELECT 
                                                                                      [Limit1].[C1] AS [C1], 
                                                                                      [Limit1].[C2] AS [C2], 
                                                                                      [Limit1].[C3] AS [C3]
                                                                                      FROM ( SELECT DISTINCT TOP (1000) 
                                                                                          [UnionAll1].[C1] AS [C1], 
                                                                                          [UnionAll1].[Name] AS [C2], 
                                                                                          [UnionAll1].[Name1] AS [C3]
                                                                                          FROM  (SELECT DISTINCT TOP (1000) 
                                                                                              1 AS [C1], 
                                                                                              [Extent1].[Name] AS [Name], 
                                                                                              [Extent2].[Name] AS [Name1]
                                                                                              FROM  [dbo].[Customer] AS [Extent1]
                                                                                              INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
                                                                                          UNION
                                                                                              SELECT DISTINCT TOP (1000) 
                                                                                              1 AS [C1], 
                                                                                              [Extent3].[Name] AS [Name], 
                                                                                              [Extent4].[Name] AS [Name1]
                                                                                              FROM  [dbo].[Customer] AS [Extent3]
                                                                                              INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
                                                                                      )  AS [Limit1]

                                                                                  тем более что ORDER нигде не указан, а значит может быть любым.
                                                                                    0

                                                                                    Вы понимаете что при таком переписывании запрос внешний селект может дать от 1 до 1000 записей?

                                                                                      +1

                                                                                      На самом деле, не может: внутренний селект уже вернул уникальные записи. Другое дело, что там теперь есть лишний DISTINCT, но его же и убрать можно.

                                                                                        0

                                                                                        да, действительно. Но результат двух запросов все равно неэквивалентный. Поэтому сам SQL Server не будет делать такое преобразование.

                                                                                          0

                                                                                          Мне, на самом деле, очень любопытно, как разработчики оптимизатора определяют границы эквивалентности.


                                                                                          (я просто помню, как мы сами при разработке некоей оптимизации сначала написали тесты на полную эквивалентность, потом когда они в некоторых случаях попадали, внимательно посмотрели, вспомнили, какие гарантии мы даем наружу, и часть проверок эквивалентности убрали, потому что иначе было слишком неэффективно)

                                                                                            0

                                                                                            Они описаны в реляционной алгебре насколько я понимаю.

                                                                                              +1

                                                                                              Ну, это тоже было бы формулировкой: "мы считаем эквивалентными те запросы, которые эквивалентны в терминах реляционной алгебры (для незнакомых см. туда-то)", но я подозреваю, что даже такого заявления они себе не могут позволить сделать публично.

                                                                                                0

                                                                                                Это немного другой вопрос. Те семантические преобразования, которые есть сейчас, описаны в BOL. Легко убедиться что они не поменяют семантику запроса.


                                                                                                Некоторые преобразования вытекают из реляционной алгебры, некоторые из здравого смысла. Например ditinct фактически не присутствует в плане если в проекции есть ключи.

                                                                                            +1
                                                                                            Но результат двух запросов все равно неэквивалентный.

                                                                                            Это с чего вдруг? Если вы не указали ORDER SQL сервер не гарантирует порядок. Соответственно они эквивалентны. То есть да могут вернуть разные значения, но в документации по любому SQL серверу написано, что один и тот же запрос может через секунду вернуть другие значения (просто из-за каких-то внутренних операций со страницами).

                                                                                            Собственно если бы даже был ORDER, то его тоже можно было бы протолкнуть внутрь подзапросов и получить эквивалентные запросы.
                                                                                    0

                                                                                    (del, уже спросили)

                                                          +1
                                                          А если убрать перекос в данных, то вполне возможно что оба запроса будут примерно одинаково работать.

                                                          Извините, но это как? Говорить клиенту, что простите, у вас данные «перекошенные». Выровняйте их, пожалуйста.
                                                            0

                                                            В первую очередь надо сообщить СУБД об этом. Путем создания индекса и\или статистики.


                                                            Во вторую очередь переписать запросы так, чтобы внутренние соображения SQL Server насчет кардинальности джоинов соответствовали представлениям писателя запросов.


                                                            А для тестового примера можно и данные перегенерировать.

                                                              0
                                                              представлениям писателя запросов.

                                                              Кстати, а откуда писатель запросов вообще должен статистику знать, чтобы «правильно писать запросы»? Ее вроде как только SQL сервер знает.
                                                                +3

                                                                Человек всегда знает о данных больше чем машина. Потому что для машины это всего лишь числа, а для вас они имеют смысл или просто вы знаете алгоритм по которому эти числа генерируются.


                                                                SQL Server при этом сам ничего не знает. Знает он только если создать нужые объекты — индексы, статистику, внешние ключи. Иначе оптимизатор полагается на константы и простые эверистики, которые предполагают равномерное распределение данных и равноценность предикатов.


                                                                Суть работы DBA по созданию и оптимизации схемы данных — передать как можно больше этих знаний тупой железке, чтобы она сама могла генерировать оптимальные планы запросов.

                                                                0
                                                                А что мешает query provider'у самому формировать запросы с UNION? Там то явно трактовки разной не будет. Почему разработчик вообще должен «подчищать» за EF, если с логической точки зрения запрос сформирован правильным (то есть простым способом).

                                                                А для тестового примера можно и данные перегенерировать.

                                                                  +1

                                                                  Во-первых еще не доказано что запрос с UNION лучше (имеет меньше cost). Это он в данной схеме с таким набором данных отработал лучше, по случайному совпадению.
                                                                  Во-вторых чем проще LINQ переводится в SQL, тем проще его писать.

                                                                    +1

                                                                    Продублирую из соседней ветки:


                                                                    CROSS + WHERE A OR B в общем случае не дает такой же результат, как
                                                                    INNER A UNION INNER B или INNER A UNION ALL INNER B. Первый может дать меньше строк, второй больше. Это не эквивалентные запросы в общем случае и точно LINQ не будет делать такое преобразование.

                                                                      0
                                                                      В большинстве случаев дубликаты строк и не нужно выводить. Даже если это и получается-стараются вытаскивать уникальные наборы в результирующей выборке.
                                                                      А иначе как в приложении вы будете обрабатывать записи с одинаковыми ключами?
                                                                        0

                                                                        На фразе "с одинаковыми ключами" мой внутренний реляционный движок приуныл...

                                                                          0
                                                                          Не ну правда-вносите изменения и потом отправляете на сохранение.
                                                                          Во-первых, пользователь растеряется куда вводить когда дубли, а во вторых куда сохранять. Хотя второе еще можно решить.
                                                                          Просто приведите пример в жизни, когда нужны дубликаты строк?
                                                                            0

                                                                            Так дело не в том, что дубликаты строк нужны, а в том, что надо строить такие запросы, которые их не будут давать.

                                                                              0
                                                                              Ок, если сможете-дайте лучше решение. Пока нашли такое, но если есть предложение лучше-с радостью его рассмотрю)
                                                                              И только не надо там про индексы и FK-не всегда это возможно сделать для всех запросов думаю понятно почему.
                                                                                +1

                                                                                Ну как бы вам сказать.


                                                                                Берем ваш первый запрос:


                                                                                from e1 in db.Customer
                                                                                from e2 in db.Ref
                                                                                where (e1.Ref_ID == e2.ID) || (e1.Ref_ID2 == e2.ID2)
                                                                                select new { Data1 = e1.Name, Data2 = e2.Name }

                                                                                Он, как вы и говорили, за 30 секунд не выполняется (почти 7 минут на моей машине). Окей.


                                                                                Берем ваш второй запрос:


                                                                                (from e1 in db.Customer
                                                                                join e2 in db.Ref
                                                                                on e1.Ref_ID equals e2.ID
                                                                                select new { Data1 = e1.Name, Data2 = e2.Name })
                                                                                .Union(
                                                                                  from e1 in db.Customer
                                                                                  join e2 in db.Ref
                                                                                  on e1.Ref_ID2 equals e2.ID2
                                                                                  select new { Data1 = e1.Name, Data2 = e2.Name })

                                                                                Он тоже за 30 секунд не выполняется (почти четыре минуты на моей машине). Окей.


                                                                                А теперь — маленький трюк. Просто превратим сделаем ваш первый запрос полностью эквивалентным второму по возвращаемому набору данных. Видите разницу в запросах?


                                                                                (
                                                                                from e1 in db.Customer
                                                                                from e2 in db.Ref
                                                                                where (e1.Ref_ID == e2.ID) || (e1.Ref_ID2 == e2.ID2)
                                                                                select new { Data1 = e1.Name, Data2 = e2.Name }
                                                                                ).Distinct();

                                                                                Вот во что он разворачивается в MS SQL:


                                                                                SELECT 
                                                                                    [Limit1].[C1] AS [C1], 
                                                                                    [Limit1].[Name] AS [Name], 
                                                                                    [Limit1].[Name1] AS [Name1]
                                                                                    FROM ( SELECT DISTINCT TOP (1000) 
                                                                                        [Extent1].[Name] AS [Name], 
                                                                                        [Extent2].[Name] AS [Name1], 
                                                                                        1 AS [C1]
                                                                                        FROM  [dbo].[Customer] AS [Extent1]
                                                                                        CROSS JOIN [dbo].[Ref] AS [Extent2]
                                                                                        WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
                                                                                    )  AS [Limit1]

                                                                                200 миллисекунд.

                                                                                  0

                                                                                  Планы можно посмотреть?
                                                                                  Estimated и Actual если не сложно

                                                                                    0

                                                                                    Execution plan XML от actual execution (я не выводил estimated) подойдет?

                                                                                      0

                                                                                      да

                                                                                          0

                                                                                          А планы других запросов?

                                                                                            0

                                                                                            Там вроде три XML, от трех запросов.

                                                                                              0

                                                                                              Сорри, слепой.


                                                                                              Планы с distinct и без оного эквивалентны. Cost у них одинаковый.
                                                                                              Но в первом случае внешний цикл был по Customer, а внутренний по Ref. Чтобы его "оптимизировать" SQL Server прогнал всю таблицу ref через промежуточную таблицу, что вызвало огромное количество операций увеличения tempdb (проверьте, он огромный).


                                                                                              Во втором запросе (с distinct) внешний цикл был по Ref, а внутренний по Customer и "внезапно" потребовалось обойти всего 4 записи из Ref и чуть меньше 200 000 из Customer.


                                                                                              Такие ошибки в оценке селективности свидетельствуют о нехватке индексов и внешних ключей.


                                                                                              Запрос с Union честно все перебирает, сортирует и убират дубли и выбирает первую 1000. Из-за ошибки в оценке селективности выделенного зранее обхъема памяти не хватило для сохранения и сортировки результатов и SQL Serverиспользовал tempdb, что и дало время около 3 минут.


                                                                                              Короче можно сделать вывод, что вывод автора статьи не стоит ничего. Небольшое изменение начальных условий кардинально меняет план запроса и время выполнения. Оптимизацией это назвать у меня язык не поворачивается.

                                                                                                0

                                                                                                Вот и мне казалось, что когда подобные мелкие изменения сильно влияют на производительность, да еще и контринтуитивно, распространять их влияние на все другие кейсы — преждевременно.


                                                                                                Спасибо за анализ.

                                                                                                  0
                                                                                                  Лучше изначально заложить простоту в понимание запроса, чем потом бороться с последствиями.
                                                                                                  Данный кейс нам помог-потому и поделился.
                                                                                                  Весь линк я не рассматривал.
                                                                                                  И да, нужно все перепроверять на конкретных примерах и условиях. Впрочем как и любую другую информацию.
                                                                                                    0
                                                                                                    Лучше изначально заложить простоту в понимание запроса

                                                                                                    … и поэтому вы написали более сложный запрос вместо более простого.


                                                                                                    Данный кейс нам помог-потому и поделился.

                                                                                                    Есть маленький нюанс: вы заменили запрос на неэквивалентный (и раньше, если я правильно помню, об этом в тексте не было ни слова; я, например, не заметил и поверил вам). У людей могут быть совершенно другие задачи.

                                                                                                      0
                                                                                                      Про дубликаты я дополнил везде-и в реалиях никто дубликаты не выводит.
                                                                                                      Я привел стабильный пример и достаточно шустрый, т к там два простых запроса и DISTINCT делает тоже что и UNION
                                                                                                        0
                                                                                                        Про дубликаты я дополнил везде-и в реалиях никто дубликаты не выводит.

                                                                                                        Тогда почему в вашем изначальном запросе они не отсекались?


                                                                                                        Я привел стабильный пример и достаточно шустрый

                                                                                                        Ну да, если проигнорировать чужие измерения...

                                                                                                          0
                                                                                                          Потому что в случае с такими условиями их никогда не будет-что внутреннее, что декартово произведение по условиям на равенство по нескольким полям с учетом, что каждому элементу из первого множества будет соответствовать не более одного элемента из второго. Т е как бы да-поведение ключей.
                                                                                                          НЕ стал описывать, т к привел скрипты наполнения данными-в таком случае дублей не будет даже если вы все данные перемножите между собой.
                                                                                                          Более того я тоже задумался а возможны ли в принципе дубли если в условиях равенство.
                                                                                                          Перемножьте A на B там уже не будет дублей, а сверху фильтр и все-дублей не было изначально, не будет и потом
                                                                                                            0
                                                                                                            Потому что в случае с такими условиями их никогда не будет

                                                                                                            Эм. Ничто в вашей схеме данных этого не гарантирует. Что мешает нескольким Customer иметь одинаковые RefID? Одинаковые RefID2? Нескольким Ref иметь одинаковые ID2?

                                                                                                              0
                                                                                                              Изначальный запрос подобен реальному с прода-возможно где-то есть ограничение на уровне схемы или в серверном компоненте
                                                                                                                0

                                                                                                                А вы эти "где-то существующие дополнительные ограничения" не приводите, и предлагаете что нам, что серверу самостоятельно об этом догадываться. Нехорошо.

                                                                                                                  0
                                                                                                                  в целом скорее всего Вы правы, но старался по максимуму упростить задачу. В любом случае предложенный вариант и дубли убирает. А первый вариант-это как данность с прода. Т е тупо вот такая какаха пришла и она порой дает всплески-DBA сделай нам чудо...DBA конечно делает, но потом решает и добивается ревью LINQ запросов и обучает разрабов как не надо писать и все счастливы и не нужны на каждый пшик индексы и обходимся более дешевым железом. Все счастливы)
                                                                                                                  Нет конечно полностью без индексов никак-бывают сложные ситуации, но это должно быть исключение, а не правило. Надо лечить источник проблем, а симптомы лишь тогда, когда на данный момент источник полечить не удается. Решение когда DBA делает ревью кода, который создает SQL-запросы позволило сократить проблемы в разы
                                                                                                                    0
                                                                                                                    в целом скорее всего Вы правы, но старался по максимуму упростить задачу

                                                                                                                    Нельзя это делать, теряя важные стартовые условия. Вы — потеряли.


                                                                                                                    DBA конечно делает, но потом решает и добивается ревью LINQ запросов и обучает разрабов как не надо писать и все счастливы

                                                                                                                    Дадада. "Все счастливы". Если то, что приведено выше, пример такого счастья, то без меня, пожалуйста.


                                                                                                                    Решение когда DBA делает ревью кода, который создает SQL-запросы

                                                                                                                    Я надеюсь, у этого DBA есть хотя бы лет пять опыта в работе с соответствующей технологией?

                                                                                                                      0
                                                                                                                      Есть не 5, а 3. Это я)
                                                                                                                      Если еще интересует-по C# более 10-ти лет, на скуле-чуть меньше. Опыт нелинейный, т к работаю еще на одну компанию аутсорс постоянно и еще на некоторых временных работах.
                                                                                                                      Года ничего не значят-можно и все 8 не туда потерять
                                                                                                                      Так, Ваш запрос в итоге дольше выполняется-там с картинками привел-замазывал внизу название сервера и логин криво, т к уже сонный
                                                                                                                        0
                                                                                                                        Так, Ваш запрос в итоге дольше выполняется

                                                                                                                        На вашей машине в ваших условиях. На моей машине в моих условиях — наоборот. Планы я привел.

                                                                                                  0
                                                                                                  Не знаю, нам помогли эти рещения.
                                                                                                  Поделилмя детально что было, как сделали и что получили.
                                                                                                  Спасибо
                                                                                                    0
                                                                                                    Смысл всего было изначально, чтобы генерировались такие SQL-запросы через LINQ-запросы, которые бы были всегда стабильны в выполнении. Имхо, inner join будет лучше cross join хотя бы потому, что из реляционной алгебры известно, что внутреннее соединение быстрее декартового произведения. А предсказать на все случаи жизни как оптимизатор СУБД выполнит запрос практически невозможно.
                                                                                                    Да, можно крутить оптимизацию СУБД. Но если можно сразу построить оптимальный запрос по синтаксису, который с большей вероятностью поймет оптимизатор, тогда почему бы этим не воспользоваться? Зачем писать в LINQ from-from, когда нужен inner join? Пока вы поймете какой индекс нужен, этот запрос не один раз опрокинет прод.
                                                                                                    Я с этими линками собаку сьел. И мне тоже пели, что планы хорошие и быстро. А в проде потом факап, который в частности и мне приходилось и не только индексами разгребать.
                                                                                                    Пишите максимально понятно и не надейтесь на оптимизатор.
                                                                                                    Лучше сразу посмотреть какой запрос создается и улучшить LINQ-запрос так, чтобы SQL-запрос всегда или максимально всегда выполнялся предсказуемым способом
                                                                                                      0
                                                                                                      Имхо, inner join будет лучше cross join хотя бы потому, что из реляционной алгебры известно, что внутреннее соединение быстрее декартового произведения.

                                                                                                      Вы забыли один нюанс: INNER JOIN и CROSS JOIN — это всего лишь синтаксис конкретного языка. Транслируются они во внутреннее соединение или в декартово произведение — личное дело СУБД.

                                                                                                        0
                                                                                                        Но INNER JOIN более вероятнее будет транслироваться не как декартово произведение, а CROSS JOIN вы можете заложить что угодно, но вот что будет выбрано внутреннее соединение-решать не Вам, а оптимизатору
                                                                                                          0

                                                                                                          Во всех случаях решать не мне, а оптимизатору.

                                                                                                            0
                                                                                                            И потому лучше писать так запросы, что оптимизатору было меньше маневров «додумывать» тем более что он может не туда «додумать».
                                                                                                            То что он может cross join обработать как inner join, это потому что оптимизатор улучшили. Но это не значит, что можно извините «говнокоду» позволять существовать
                                                                                                              0
                                                                                                              То что он может cross join обработать как inner join, это потому что оптимизатор улучшили.

                                                                                                              Да нет, потому что в SQL больше одного варианта для джойнов. Собственно, есть описанный в документации вариант:


                                                                                                              SELECT pv.ProductID, v.BusinessEntityID, v.Name
                                                                                                              FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
                                                                                                              WHERE pv.BusinessEntityID=v.BusinessEntityID

                                                                                                              Для которого явно сказано, что он эквивалентен INNER JOIN.


                                                                                                              Но это не значит, что можно извините «говнокоду» позволять существовать

                                                                                                              А где вы говнокод-то нашли?

                                                                                                                0
                                                                                                                когда вместо INNER генерится в запрос CROSS-по-моему код может душком отдавать при более сложных условиях, чем с явно указанным INNER
                                                                                                                  0
                                                                                                                  когда вместо INNER генерится в запрос CROSS

                                                                                                                  А почему, собственно, вместо?


                                                                                                                  У вас в LINQ что написано? Правильно:


                                                                                                                  from e1 in db.Customer
                                                                                                                  from e2 in db.Ref
                                                                                                                  where (e1.Ref_ID == e2.ID) || (e1.Ref_ID2 == e2.ID2)

                                                                                                                  Там есть join? Правильно, нет. А что там написано? Правильно, типичное соединение "старого" (как документация на T-SQL пишет, "pre-SQL-92") типа, когда две таблицы указываются во FROM, а условие — в WHERE. И это с чистой совестью пишется в CROSS JOIN, потому что это полный честный эквивалент.

                                                                                                                    0
                                                                                                                    и это не всегда хорошо (CROSS для одного, INNER-для другого, ну не просто же так они по разному пишутся в конце концов)
                                                                                                                      0

                                                                                                                      Что конкретно не всегда хорошо? То, что LINQ-провайдер транслирует ваше намерение в T-SQL максимально близко?

                                                                                                                        0
                                                                                                                        должен транслировать в INNER, чтобы не смущать никого-я немного устал если честно. Продолжу завтра-оценю предложенный Вами запрос
                                                                                                                          0

                                                                                                                          Почему должен-то? Вы не написали join, вы написали другую конструкцию.

                                                                                                                            0
                                                                                                                            Изначально дано from-from, переписали на join-union
                                                                                                                              0

                                                                                                                              Переписали — получили UNION. Все логично, вроде.


                                                                                                                              Нет, серьезно. Пока что EF весьма достоверно транслирует ваши намерения в T-SQL, ну а тот уже работает в меру знаний оптимизатора. И что не так?

                                                                                                                                0
                                                                                                                                Я устал честно-не могу достучаться до Вас изначально писать так, чтобы генерировался предсказуемый код, а не CROSS JOIN, который оптимизатор превратит в INNER JOIN
                                                                                                                                  0

                                                                                                                                  Кому писать? Разработчику? Жертвуя читаемостью? Ну да, это типичная оптимизация "по месту", с этим никто не спорит.


                                                                                                                                  Я думал, вы про провайдер говорите, что он вам не тот код генерит, который вы хотите.

                                                                                                                                    –3
                                                                                                                                    Я устал честно-не могу достучаться до Вас

                                                                                                                                    Достучаться до lair? Серьезно? Вам ему что 2+2 = 4 будет тяжело доказать, он всегда вам сможет возразить, что может это в троичной системе исчисления. И даже то, что 4 в ней вообще не существует его не сильно смутит.
                                                                                                                                      0
                                                                                                                                      я вообще даже не про планы эти-то, я про генерацию SQL-кода, а планы просто в конце привел как частный случай. Неужели так сложно понять, что надо писать так, чтобы оптимизатор выполнял предсказуемо. Тогда даже уже не так важно какая там СУБД коль код будет нормально генерироваться
                                                                                                                                        0
                                                                                                                                        Неужели так сложно понять, что надо писать так, чтобы оптимизатор выполнял предсказуемо.

                                                                                                                                        Тогда лучше писать сразу на SQL, чтобы уж точно никакой дополнительный транслятор не включался.

                                                                                                                                          0
                                                                                                                                          Да, такие места есть-это критически важные для скорости и отчеты.
                                                                                                                                          Более того иногда даже длл пишут на C++ и там делают обработку-например связанные с обработкой очень большого количества целей-водных, наземных, где данные генерятся со скоростью свыше 4 млрд строк в час. Там никакой C# с СУБД не выдержат. И вот тут то только разработчик и программист справятся-вспоминаем об абстракциях, что я говорил выше)
                                                                                                                                            0
                                                                                                                                            А так SQL-язык выглядит более изящно и красиво, чем LINQ to SQL если уж так со всех сторон посмотреть. Просто его ввели для облегчения маппинга данных-скрещения ужа с ежом вот и все. Но нужно понимать какова цена этому, когда придется спуститься на абстракцию ниже для большей производительности и лучшего масштабирования, а когда позволительно увеличить время обработки данных в разы ради скорости разработки решения, а когда это недопустимо в виду ситуаций опасных не только для жизни людей, но и целых объектов
                                                                                                                                              0
                                                                                                                                              А так SQL-язык выглядит более изящно и красиво, чем LINQ to SQL если уж так со всех сторон посмотреть.

                                                                                                                                              Неа. Например, с повторным использованием в SQL как-то не очень.

                                                                                                                                                –1
                                                                                                                                                Ах, эта сладкая песня про повторное использование… позвольте отправить Вас в книгу одного из создателей .NET:
                                                                                                                                                Бен Уотсон «Высокопроизводительный код на платформе .NET» и там подробно описано, что в большинстве случаев для внеших систем такие как СУБД лучше писать LINQ-запрос в одном месте во избежании генерации неоптимальных SQL-запросов.
                                                                                                                                                Вы можете сколь угодно разбивать LINQ-конструкции, но только те, что в вашем окружении. Например, для LINQ to Objects. Но для LINQ to SQL это в будущем порождает плохие запросы, которые невсегда сможет пережевать оптимально оптимизатор СУБД
                                                                                                                                                  0

                                                                                                                                                  Производительность обычно противоречит изяществу и красоте. Это неудивительно, да.


                                                                                                                                                  Это, однако, не повод заниматься копипастой кода, потому что стоимость поддержки тоже имеет значение (и иногда весьма большое).

                                                                                                                                                    0
                                                                                                                                                    на сколько большое? кто-нить считал и сравнивал с простоем прода, когда все виснет или тормозит? Когда обрыв идет из-за перегрузок? Когда данные могут в конце концов стать некорректными. Никто не приводит конкретных цифр. Потому что всем выгоднее писать красиво и понятно, а производительность пусть железо тянет. Потому у нас с каждым годом софт все худшего качества по производительности и очень обжорного на ресурсы