Эффективное перекрестное применение с интегрированной табличной функцией CLR

В SQL Server у меня есть табличная функция, основанная на интеграции CLR, GetArchiveImages. Я называю это примерно так:

SELECT ...
FROM Items
CROSS APPLY GetArchiveImages(Items.ID) AS archiveimages
WHERE ...

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

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

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

Существует ли эффективный способ группового объединения таблиц с результатами хранимой процедуры или функции, а не построчно?


sql-server cross-apply sqlclr
person Bryce Wagner    schedule 15.01.2013    source источник
comment
возможно, WITH подойдет вам. Он создает временную таблицу на основе оператора выбора, к которому вы сможете присоединиться при выборе. Некоторая информация. Будет ли это то, что вы ищете?   -  person Tikkes    schedule 15.01.2013
comment
Я знаком с Common Table Expressions и не вижу, как они могут быть полезны в этом случае.   -  person Bryce Wagner    schedule 15.01.2013

Ответы (2)


arrow_upward
3
arrow_downward

Поскольку результат GetArchiveImages зависит от Items.ID, SQL Server должен вызывать функцию для каждого элемента, иначе вы не получите правильных результатов.

Единственная функция, которую SQL Server может «разбить», — это встроенная табличная функция T-SQL. Поэтому, если вы сможете переписать CLR как ITVF, вы получите более высокую производительность.

По моему опыту, вызов функции CLR не так уж и велик. Гораздо более вероятно, что у вас возникли проблемы где-то еще в запросе. Например, SQL Server не знает, сколько строк будет возвращено этой функцией, и просто предполагает, что будет одна (для каждого вызова). Это может привести к ошибочным решениям в других местах в процессе оптимизации.

ОБНОВИТЬ:

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

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

person Sebastian Meine    schedule 15.01.2013
comment
Это не SQL Server, у которого есть накладные расходы, это инициализация моей функции CLR, она выходит и читает несколько файлов в файловой системе каждый раз, когда она вызывается, но мне нужно было бы открыть их только один раз, если бы я мог быть вызван для каждого запроса вместо построчного. - person Bryce Wagner; 15.01.2013
comment
Думаю, я должен признать, что нет простого способа сделать это, и я не могу напрямую делать то, что хочу, в рамках ограничений SQL Server. Я думаю, это потребует более творческого решения. - person Bryce Wagner; 15.01.2013
comment
Спасибо за подсказку о хранении коллекции только для чтения. Это не решает ситуацию CROSS APPLY в моем вопросе, но полезно для хранения очень редко меняющейся строки, которую я не хочу каждый раз запрашивать из базы данных. - person Bryce Wagner; 07.06.2013

arrow_upward
2
arrow_downward

Если функцию GetArchiveImages() не нужно использовать в нескольких запросах или, по крайней мере, не использовать за пределами аналогичных запросов, вы можете поменять местами внешний и внутренний аспекты этого: выполните основной SELECT fields FROM [Items] WHERE ... в SQLCLR TVF. И сделать это потоковым TVF.

Необходимая базовая структура будет следующей:

  1. Определите переменную типа SqlDataRecord, чтобы она содержала все поля, которые вы хотите вернуть из [Items], а также другие поля, возвращаемые текущей функцией GetArchiveImages().

  2. Прочитайте «несколько файлов в файловой системе» (взято из первого комментария к ответу @Sebastian Meine)

  3. Откройте SqlConnection, используя "Trusted_Connection = true; Enlist = false;" в качестве ConnectionString.

  4. Выполнить основной SELECT fields FROM [Items] {optional WHERE}. Если на данный момент возможно сузить некоторые строки, заполните WHERE. Вы даже можете передавать значения в функцию для передачи в предложение WHERE.

  5. Цикл через SqlDataRecord:

    1. Fill out the SqlDataRecord variable for this row
    2. Получить связанные элементы, которые текущая функция GetArchiveImages() получает на основе [Items].[ItemID]
    3. позвони yield return;

  6. Закройте SqlConnection

  7. Утилизируйте SqlDataReader, SqlCommand и SqlConnection.

  8. Закройте все файлы, открытые на шаге 2 (если их нельзя закрыть ранее в процессе).

person Solomon Rutzky    schedule 31.01.2015