Альтернатива использованию временной таблицы/временных переменных или CTE внутри представления в SQL Server.

У меня есть сценарий, в котором мне нужно создать представление с кучей UNIONS различных операторов select.

SELECT DISTINCT ISNULL(ID,'ID') as Id, 
ISNULL(FIRST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
  UNION
SELECT DISTINCT ISNULL(EMP_ID,'ID') as Id, 
ISNULL(LAST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
  UNION
SELECT DISTINCT ISNULL(LICENSE,'ID') as Id, 
ISNULL(COMPANY,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
.
.
.
.
.
10 such selects

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

Create View [test].[emp].[MainView]
AS
select * into #tempTable from [test].[emp].[OrgView]
SELECT DISTINCT ISNULL(ID,'ID') as Id, 
ISNULL(FIRST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable
  UNION
SELECT DISTINCT ISNULL(EMP_ID,'ID') as Id, 
ISNULL(LAST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable
  UNION
SELECT DISTINCT ISNULL(LICENSE,'ID') as Id, 
ISNULL(COMPANY,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable

Невозможно использовать общее табличное выражение (CTE) с вышеуказанным запросом, так как он будет доступен только для одного поиска, а не для остальных 9.

Как лучше всего это сделать на сервере Sql?


sql sql-server sql-tuning
person Naxi    schedule 05.08.2020    source источник
comment
Если вы поместите много UNION в свое представление, это все равно будет только один вызов базы данных. Механизм базы данных выработает для вас наилучший план выполнения. Но почему вы спрашиваете? Похоже на проблему XY.   -  person Wouter    schedule 05.08.2020
comment
Правильно - представление состоит из одного оператора sql. Если вы уменьшите свой пример и опубликуете скрипт/скрипку воспроизведения и предоставите желаемый результат из ваших демонстрационных данных, кто-то может дать полезные предложения. Надеюсь, вы понимаете разницу между UNION и UNION ALL.   -  person SMor    schedule 05.08.2020
comment
Кажется, мало смысла в добавлении 'ID', 'unknown:Unknown', 'unknown' к набору результатов для строк, в которых отсутствуют значения. Это только усложняет работу движка — рассмотрите возможность исключения этих значений из WHERE (удаление ISNULL) и добавление их обратно с новыми запросами, если это необходимо (т. е. инвертирование WHERE). Оптимизатор может распознать, когда подзапросы имеют условия, которые логически исключают друг друга, но он не может оптимизировать DISTINCT над выражениями. Это, вероятно, также позволит вам использовать UNION ALL, а не UNION.   -  person Jeroen Mostert    schedule 05.08.2020

Ответы (2)


arrow_upward
1
arrow_downward

Я думаю, вы хотите CROSS APPLY вместо UNION. Я немного не понимаю, где вы хотите поместить логику, но вот идея:

SELECT DISTINCT v.Id, 
       (COALESCE(FIRST_NAME, 'unknown') + ':' + 'Unknown') AS label,
       COALESCE(VALUE,'unknown') AS [value]
FROM #tempTable CROSS APPLY
     (VALUES (COALESCE(ID, 'ID')),
             (COALESCE(EMP_ID, 'ID')),
             (COALESCE(LICENCE_ID, 'ID')),
             . . .
     ) v(id)
person Gordon Linoff    schedule 05.08.2020

arrow_upward
1
arrow_downward

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

CREATE VIEW [test].[emp].[MainView]
AS
SELECT .... FROM [test].[emp].[OrgView]
UNION
SELECT .... FROM [test].[emp].[OrgView]
....

Другое дело эффективность, и я не могу это комментировать, так как полная логика не указана в ваших вопросах. Однако, если все предложения JOINs и WHERE во всех операторах SELECT похожи/совместимы друг с другом, то вы можете объединить их с CROSS APPLY, как предложил @gordon-linoff, но я вижу, что вы упоминаете, что один из запросов использует поиск не совместно с другими запросами, так что это может быть или не быть возможным (в вашем сообщении отсутствуют подробности).

person K4M    schedule 08.08.2020