SQL CTE в представлении по сравнению с таблицей темпов в хранимой процедуре

Пожалуйста, потерпите меня - я знаю, что это сложно.

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

Это привело меня к созданию этого общего запроса табличного выражения внутри представления, к которому я затем ПРИСОЕДИНЯЮСЬ с рядом других внутри хранимой процедуры, чтобы получить нужные мне результаты:

WITH TempTable AS (
    SELECT  l.BuildingID, l.ApartmentID, l.LeaseID, l.ApplicantID,
                ROW_NUMBER() OVER (PARTITION BY l.ApartmentID ORDER BY s.Importance DESC, MovedOut, MovedIN DESC, LLSigned DESC, Approved DESC, Applied DESC) AS 'RowNumber'
    FROM    dbo.NPleaseapplicant AS l INNER JOIN
            dbo.NPappstatus AS s ON l.BuildingID = s.BuildingID AND l.AppStatus = s.Code

)

SELECT  BuildingID, ApartmentID, LeaseID, ApplicantID
FROM    TempTable
WHERE   RowNumber = 1

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

В качестве теста я создал временную таблицу внутри хранимой процедуры вместо использования View и получил гораздо более высокую производительность:

CREATE TABLE #Relevant (
    BuildingID int,
    ApartmentID int,
    LeaseID int,
    ApplicantID int,
    RowNumber int
)

INSERT INTO #Relevant (BuildingID, ApartmentID, LeaseID, ApplicantID, RowNumber)
SELECT  l.BuildingID, l.ApartmentID, l.LeaseID, l.ApplicantID,
            ROW_NUMBER() OVER (PARTITION BY l.ApartmentID ORDER BY s.Importance DESC, MovedOut, MovedIN DESC, LLSigned DESC, Approved DESC, Applied DESC) AS 'RowNumber'
FROM    dbo.NPleaseapplicant AS l INNER JOIN
        dbo.NPappstatus AS s ON l.BuildingID = s.BuildingID AND l.AppStatus = s.Code
WHERE   (l.BuildingID = @BuildingID)

DROP TABLE #Relevant

На первый взгляд меня это не устраивает. Я слышал, что временные таблицы, как известно, плохо сказываются на производительности. Проблема в том, что я могу лучше ограничить запрос в таблице Temp с предложением WHERE, которое я не могу в представлении. С более чем 10 000 договоров аренды на 16 зданий в таблице, возможность фильтрации с помощью WHERE может отбросить строки, затронутые от 90% до 95%.

Принимая во внимание все это, есть ли что-нибудь вопиющее, чего мне здесь не хватает? Я делаю что-то не так с View, что может вызвать ужасную производительность, или это просто вопрос меньшего набора результатов в таблице Temp, превышающего неограниченный набор результатов в CTE?

РЕДАКТИРОВАТЬ: Я должен добавить, что эта бизнес-логика выбора «наиболее подходящей аренды» является ключевой для многих отчетов в системе. Вот почему он изначально был помещен в View. Представление дает нам возможность «писать один раз, использовать много», тогда как временная таблица в хранимой процедуре должна быть воссоздана для каждой другой хранимой процедуры в системе. Уродливый.

РЕДАКТИРОВАТЬ №2: Могу ли я использовать табличную функцию вместо представления? Позволит ли это мне ограничить количество затронутых строк заранее и по-прежнему использовать полученный набор данных в JOIN с другими таблицами? Если он работает и имеет приличную производительность, это позволит мне хранить бизнес-логику в одном месте (функции) вместо того, чтобы дублировать ее в десятках хранимых процедур.


sql sql-server performance common-table-expression temp-tables
person Axeva    schedule 30.01.2011    source источник
comment
Какая версия SQL Server, пожалуйста? ROW_NUMBER лучше в 2008+   -  person gbn    schedule 30.01.2011
comment
1. Как выбрать данные из представления (с фильтром по идентификатору здания или без него)? 2. Опубликуйте структуру таблиц и индексы. 3. Опубликуйте план выполнения обоих запросов (плохой и хороший)   -  person oryol    schedule 30.01.2011
comment
К сожалению, это SQL Server 2005. Управляется хостинговой компанией, поэтому я не могу контролировать обновления.   -  person Axeva    schedule 30.01.2011
comment
Данные выбираются из представления с фильтром по BuildingID @oryol. Графические версии плана выполнения огромны. Есть ли лучший способ показать их?   -  person Axeva    schedule 30.01.2011
comment
Выполните set showplan_text на и ваши запросы после него. Также добавьте в пост скрипты для создания структуры БД (таблиц и индексов)   -  person oryol    schedule 30.01.2011

Ответы (2)


arrow_upward
4
arrow_downward

Просто чтобы поклониться этому, вот что я в итоге сделал:

Вместо того, чтобы использовать представление для объединения всех возможных строк из 2 или 3 таблиц, я создал функцию на основе таблиц, которая выполняет тот же базовый запрос. В качестве одного из параметров я передаю идентификатор здания и использую его в предложении WHERE, например:

SELECT  l.BuildingID, l.ApartmentID, l.LeaseID, l.ApplicantID,
            ROW_NUMBER() OVER (PARTITION BY l.ApartmentID ORDER BY s.Importance DESC, MovedOut, MovedIN DESC, LLSigned DESC, Approved DESC, Applied DESC) AS 'RowNumber'
FROM    dbo.NPleaseapplicant AS l INNER JOIN
        dbo.NPappstatus AS s ON l.BuildingID = s.BuildingID AND l.AppStatus = s.Code
WHERE  (l.BuildingID = @BuildingID)

В результате это резко уменьшает количество требуемых объединений и значительно ускоряет запрос.

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

person Axeva    schedule 08.02.2011

arrow_upward
0
arrow_downward

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

SELECT  BuildingID, ApartmentID, LeaseID, ApplicantID
FROM
(
SELECT  l.BuildingID, l.ApartmentID, l.LeaseID, l.ApplicantID,
                ROW_NUMBER() OVER (PARTITION BY l.ApartmentID ORDER BY s.Importance DESC, MovedOut, MovedIN DESC, LLSigned DESC, Approved DESC, Applied DESC) AS 'RowNumber'
    FROM    dbo.NPleaseapplicant AS l INNER JOIN
            dbo.NPappstatus AS s ON l.BuildingID = s.BuildingID AND l.AppStatus = s.Code

)subquery
WHERE   RowNumber = 1

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

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

person stevenrcfox    schedule 20.02.2013