neisbut Jan 17 2019 at 07:59

JOIN локальной коллекции и DbSet в Entity Framework

11 min

17K

.NET*C#*SQL*

Translation

+17

Comments 25

MonkAlex Jan 17 2019 at 08:12

Последний вариант, имхо, специфический. Может стоит всё таки создавать «предметные» таблички под каждый дорогой запрос, чем вот такую общую свалку?

neisbut Jan 17 2019 at 08:17

Не совсем понял, о какой свалке речь? Последний вариант вообще не требует создания таблиц в базе.

MonkAlex Jan 17 2019 at 08:42

А, читал по диагонали, понял. В mssql такой вариант будет ограничен стандартными 2к значениями минус значения на остальные операции, правильно понимаю?
ПС: ну да, у постгре то лимита нет, ему проще =)

neisbut Jan 17 2019 at 09:02

Да, у MSSQL ограничение 2100 параметров. Но дело в том, что можно передавать значения через параметры, а можно текстом (в теле запроса). Для этого у MemoryJoin есть переключатель — ViaParameters, ViaSqlQueryBody и Auto (default). В случае с Auto — он будет пытаться определить провайдера и количество необходимых параметров. И если это MSSQL и нужно < 2100 параметров — все значения будут переданы параметрами, если больше — будет внедрять прямо в тело запроса. Кстати в тестовой программе я использовал ViaSqlQueryBody, не смотря на то, что использовал Postgres.

mayorovp Jan 17 2019 at 09:29

В сравнении не хватает варианта «не использовать EF, обойтись хранимкой и табличным параметром».

UnclShura Jan 17 2019 at 10:49

Поддерживаю! Вообще непонятно к чему такие извращения, когда все решается обычными средствами базы.

mayorovp Jan 17 2019 at 10:57

«Извращения» как раз понятно зачем. Когда на проекте уже используется EF — любой новый запрос следует прежде всего попытаться написать средствами EF. Именно EF и является теми самыми «обычными средствами».

Или вы спрашиваете зачем вообще нужно EF и вообще LINQ? Тут тоже всё довольно просто: LINQ проверяется компилятором и поддерживает контекстные подсказки в IDE. Да и объектно-реляционное исчисление куда ближе к ООП чем принятое в SQL просто реляционное исчисление.

UnclShura Jan 17 2019 at 11:54

К Linq кроме наличия [кривого] «человеческого» синтаксиса вопросов нет. А вот к Linq2Sql и вообще EF (обобщим до ORM вообще) — да есть вопросы. Для маленьких проектов размер самого EF не очень подходит, для больших он кроме вреда никакой пользы не несет.

EF решал изначально три задачи — абстракция от движка базы, позволял неосилившим SQL хоть как-то работать с базой и seamless работа с разными источниками. Абстрагироваться от движка значит терять производительность. Вторую цель я вообще принять как валидную не могу (и ее частично решал linq2sql, хоть и криво). А простая работа в общем виде с разными источниками невозможна в принципе. Разумеется я не имею ввиду источники с мизерными 100К записей, которые можно в память выкачать.

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

AgentFire Jan 17 2019 at 12:55

О, коллега, вы тоже сторонник Linq2Sql?

UnclShura Jan 17 2019 at 19:54

Вообще-то нет :) Я за то чтобы в базу лазить средствами для этого предназначеными — SQL.

AgentFire Jan 17 2019 at 19:56

не понял, голый SQL?

UnclShura Jan 17 2019 at 20:02

Да

AgentFire Jan 17 2019 at 23:29

а в чем прекол?
а, я понял, вы на асме наверное пишите

mayorovp Jan 17 2019 at 12:58

Хорошо, а какой linq-провайдер вы тогда посоветуете для решения заявленной в статье задачи? И как она будет решаться?

UnclShura Jan 17 2019 at 20:02

Никакой. Создаете временную таблицу, заполняете ее ключами из памяти, запускаете SELECT с JOIN к этой таблице, результаты читаете потоком, матчите в коде.

mayorovp Jan 17 2019 at 23:25

То есть у вас всё-таки есть вопросы к linq, раз вы его для этой задачи решили не использовать?

UnclShura Jan 18 2019 at 12:25

К Linq вопросов таки нет — это лучшее, что случилось с языком за последние годы. Идея использовать Linq везде где только можно безусловно красива. Но красота эта разбивается о суровую реальность. При больших объемах (а они достаточно небольшие с точки зрения базы) и требованиях сильно отличающихся от простого CRUD (а это 93.84% случаев) приходится использовать базо-специфичные фичи. А именно один индекс, но ни в коем случае не другой, от ключать оптимизатор запросов, использовать только предкомпилированые запросы, хинты в теле запроса (привет /*parallel*/!), всякие странные методы загрузки данных (привет бинарным external tables в Firebird) и т.д. Т.е. приходится составить [не]нормальный SQL в уме и потом пытаться EF/linq2sql заставить его сгенерить. И вот тут-то и появляется вопрос «а нафига?»

Просто я живу в мире розовых Exadata, Infiniband и прочего злобного энтепрайза. База в несколько терабайт и три сотни таблиц — обычное дело. И вот тут-то и начинаешь думать не только о красоте но и о всяких NFR, которые и начинают ооочень сильно толкать в сторону использования вендорских фич.

neisbut Jan 17 2019 at 12:39

Хорошая идея, хотя думаю результат будет ожидаемым. Я пользовался Postgres, а там нет табличных параметров, но можно использовать jsonb. Для 64k элементов у меня получилось следующее:

MemoryJoin — 3871 s
StoredProc — 2505 s

Скрин

AgentFire Jan 17 2019 at 12:57

А почему не рассмотрен вариант с SqlBulkCopy?

Он загружает любого размера данные во временную таблицу, делает это низкоуровнево и максимально быстро, поддерживает транзакции и вообще!

neisbut Jan 17 2019 at 16:39

Да, SqlBulkCopy вполне может быть дополнением к способу 5, но не более. И, кстати, он вполне рабочий, но есть и недостатки (в статье они все перечислены).
В моем же случае было не применимо, потому что я использовал Postgres.

RouR Jan 17 2019 at 22:23

1. Добавить в БД колонку fastHash, и при вставке хранить вычисленный fastHash(s.Ticker, p.TradedOn, p.PriceSourceId).
2. Дальше модификация способа 3. Multiple Contains — Для данных на клиенте считаешь массив uniqHash и делаешь запрос на сервер с одним Contains

neisbut Jan 18 2019 at 07:15

Отличная идея! Об этом способе я забыл, спасибо! Хотя у него есть существенный (для меня) недостаток: дело в том, что цены на бумаги могут поступать с опозданием и тогда условие для TradedOn должно быть не =, а <=. И также может потребоваться, чтобы вариант PriceSourceId был не один. В этом случае реализовать такой запрос, используя хэш будет сложно, если вообще возможно. А в MemoryJoin можно сделать это как обычно, например так:

код

var queryData = context.FromLocalList(reducedData);
var pricesQuery = from t in queryData
                  from p in context.Prices.Where(
                      x => x.Security.Ticker == t.Ticker &&
                      (x.PriceSourceId == t.PriceSourceId || x.PriceSourceId == 1) &&
                      x.TradedOn <= t.TradedOn)
                   .OrderByDescending(x => x.TradedOn)
                   .Take(1)
                  select p;

kukubadze Jan 30 2019 at 11:21

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

MirrorBoy Feb 4 2019 at 08:35

Небольшой оффтоп: почему везде в коде используется .ToList()?
Вы ж не собираетесь потом использовать весь функционал списка. Почему не что-то попроще? Например .ToArray().

kemsky Mar 15 at 15:20

Думаю можно зарегистрировать Complex Type и использовать, либо разложить на несколько примитивных коллекций и джойнить через индексы.