Обратитесь к временной таблице в запросе Entity Framework

В памяти есть список list из 50 000 идентификаторов продуктов. Я хотел бы получить все эти продукты из БД. Использование dbContext.Products.Where(p => list.contains(p.ID)) генерирует гигантский IN в SQL - WHERE ID IN (2134,1324543,5675,32451,45735...), и это занимает вечность. Отчасти это связано с тем, что SQL Server требует времени для разбора такой большой строки, а также потому, что план выполнения неверен. (Я знаю это, пытаясь вместо этого использовать временную таблицу).

Поэтому я использовал SQLBulkCopy для вставки идентификаторов во временную таблицу, а затем запустил

dbContext.Set<Product>().SqlQuery("SELECT * FROM Products WHERE ID IN (SELECT ID FROM #tmp))"

Это дало хорошую производительность. Однако теперь мне нужны продукты с их поставщиками (несколько для каждого продукта). Используя пользовательскую команду SQL, нет способа вернуть сложный объект, о котором я знаю. Итак, как я могу получить продукты от их поставщиков, используя временную таблицу?

(Если бы я мог каким-то образом сослаться на временную таблицу в LINQ, тогда все было бы в порядке — я мог бы просто сделать dbContext.Products.Where(p => dbContext.TempTable.Any(t => t.ID==p.ID)). Если бы я мог сослаться на нее в UDF, это тоже было бы хорошо — но вы не можете. Я не могу использовать реальной таблице, так как одновременные пользователи оставят ее в несогласованном состоянии.)

Спасибо


sql c# sql-server entity-framework
person wezten    schedule 17.10.2013    source источник
comment
Вы можете использовать реальную таблицу, в которой есть столбец с userid в нем.   -  person qujck    schedule 17.10.2013
comment
Вы имеете в виду идентификатор продукта? Я уже упоминал об этой возможности в конце.   -  person wezten    schedule 17.10.2013
comment
Если вы включите идентификатор пользователя или какое-либо другое отличительное поле в свою таблицу фильтров (TempTable), у вас не возникнет проблем с одновременными пользователями.   -  person qujck    schedule 17.10.2013
comment
@qujck Это хорошая идея - у меня нет идентификаторов пользователей, но вместо этого я мог бы использовать GUID. На самом деле я начал пытаться использовать TransactionScope, но это лучше - я бы пометил его как ответ.   -  person wezten    schedule 18.10.2013
comment
@StephenByrne Я импортирую 50 000 продаж из файла в сетку - каждая строка = одна продажа. Сетке нужен доступ ко всем продуктам, которые были проданы, по разным причинам, таким как ограничения продукта, предупреждения и т. д. Это не совсем мой сценарий, но я думаю, что функционально эквивалентен. При необходимости я установлю для журнала значение NORECOVERY. Пожалуйста, дайте мне знать, если есть лучший способ.   -  person wezten    schedule 18.10.2013
comment
Помогает ли вам этот синтаксис соединения? Ваша проблема очень похожа... но, возможно, с разными версиями EF... stackoverflow.com/a/19448221/150342   -  person Colin    schedule 18.10.2013
comment
@wezten хорошо, так это какой-то инструмент массового импорта или что-то в этом роде - я полагаю, вы не можете пролистывать данные в клиентском приложении?   -  person Stephen Byrne    schedule 18.10.2013
comment
@Colin Не совсем так - временные таблицы не используются.   -  person wezten    schedule 19.10.2013
comment
@StephenByrne Не уверен, что понимаю - продажи считываются из файла. Продукты не могут быть кэшированы, потому что их слишком много.   -  person wezten    schedule 19.10.2013
comment
@wezten Я пытаюсь понять, взаимодействует ли человек с данными после их загрузки, или это автоматизированное решение, и вы пытаетесь сделать это с временными таблицами из соображений производительности и т. д.? Если он загружается в экранную сетку, то почему бы не развернуть данные, т.е. загрузить только подмножество связанных объектов за раз, или есть какая-то причина, по которой все 50 тыс. строк должны иметь загруженные связанные объекты. Или вы могли бы, например, не загружать все 50 тыс. записей и выбирать в памяти отдельный список кодов поставщиков, а затем загружать эти объекты поставщиков за один раз и т. д.   -  person Stephen Byrne    schedule 20.10.2013
comment
@StephenByrne В начале нужны все записи, потому что сначала нужно показать продажи с предупреждениями, а для создания предупреждений нужна запись о продукте. Или вы могли бы, например, не загружать все 50 тыс. записей - вы имеете в виду временную таблицу и SqlBulkCopy? А потом все сначала для поставщиков? Это много кода, а что, если у меня есть третий уровень (что я и делаю)?   -  person wezten    schedule 20.10.2013
comment
@wezten то, что я имел в виду, было чем-то другим, но tbh, поскольку вы достаточно довольны маршрутом временной таблицы, я не хочу превращать это в затянувшийся комментарий :) .... если вы столкнетесь с проблемами производительности с выбранным вами подходом, отправьте сообщение здесь, и тогда мы сможем более подробно остановиться на нем.   -  person Stephen Byrne    schedule 21.10.2013

Ответы (2)


arrow_upward
1
arrow_downward

Я предлагаю вам расширить таблицу фильтров (TempTable в приведенном выше коде), чтобы хранить что-то вроде UserId или SessionId, а также ProductID's:

  • это даст вам всю производительность, которую вы ищете
  • это будет работать для одновременных пользователей

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

person qujck    schedule 18.10.2013

arrow_upward
3
arrow_downward

Мне было любопытно изучить sql, сгенерированный с использованием синтаксиса Join, а не Contains. Вот код для моего теста:

IQueryable<Product> queryable = Uow.ProductRepository.All;
List<int> inMemKeys = new int[] { 2134, 1324543, 5675, 32451, 45735 }.ToList();
string sql1 = queryable.Where(p => inMemKeys.Contains(p.ID)).ToString();

string sql2 = queryable.Join(inMemKeys, t => t.ID, pk => pk, (t, pk) => t).ToString();

Это sql, сгенерированный с использованием Contains (sql1)

SELECT
    [extent1].[id] AS [id],...etc
FROM [dbo].[products] AS [extent1]
WHERE ([extent1].[id] IN (2134, 1324543, 5675, 32451, 45735))

Это sql, сгенерированный с помощью Join:

SELECT
    [extent1].[id] AS [id],...etc
FROM [dbo].[products] AS [extent1]
    INNER JOIN (SELECT
        [unionall3].[c1] AS [c1]
    FROM (SELECT
        [unionall2].[c1] AS [c1]
    FROM (SELECT
        [unionall1].[c1] AS [c1]
    FROM (SELECT
        2134 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable1] UNION ALL SELECT
        1324543 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable2]) AS [unionall1] UNION ALL SELECT
        5675 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable3]) AS [unionall2] UNION ALL SELECT
        32451 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable4]) AS [unionall3] UNION ALL SELECT
        45735 AS [c1]
    FROM (SELECT
        1 AS x) AS [singlerowtable5]) AS [unionall4]
        ON [extent1].[id] = [unionall4].[c1]

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

Хотя это не отвечает на вопрос, изложенный в заголовке, но показывает способ избежать гигантского IN . Хорошо.... теперь это гигантский UNION ALL.... в любом случае... я надеюсь, что этот вклад будет полезен для некоторых

person Colin    schedule 21.10.2013