Можно ли заставить SQL Server использовать план, который я хочу оптимизировать рекурсивный запрос cte

Question

У меня есть запрос в представлении с использованием рекурсивного cte для большого дерева, который хорошо работает при запросе с жестко закодированным числом, но не с параметром. Можно ли заставить SQL Server использовать план, который я хочу оптимизировать, этот рекурсивный запрос cte? Любые идеи были бы хорошы.

Вот представление с рекурсивным CTE - оно извлекает все узлы с данным объектом:

CREATE VIEW adams_test_view AS
WITH eq_mi_cte(miId, eqId, miName, miCode) AS
    (SELECT ent.id, ent.id, ent.name, ent.code
    FROM entity ent
    UNION ALL
    SELECT e.id, eq_mi_cte.eqid, e.name, e.code
    FROM entity e
    INNER JOIN eq_mi_cte ON e.pid = eq_mi_cte.miid)
SELECT * FROM eq_mi_cte

Похоже, что запрос к представлению с параметром запрашивает весь вид, а затем фильтрует его, который никогда не заканчивается, так как дерево слишком большое - мы получаем максимальную ошибку рекурсии:

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt

Вот план (мои извинения за имена индексов):

  |--Filter(WHERE:([Recr1009]=[@TopLevelEnt]))
       |--Index Spool(WITH STACK)
            |--Concatenation
                 |--Compute Scalar(DEFINE:([Expr1012]=(0)))
                 |    |--Index Scan(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [ent]))
                 |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                           |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                           |    |--Table Spool(WITH STACK)
                           |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

Теперь, когда я делаю тот же запрос, используя жестко закодированное значение, он возвращает нормально:

запрос:

SELECT * FROM adams_test_view
WHERE eqId = 187317

план:

  |--Index Spool(WITH STACK)
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1012]=(0)))
            |    |--Clustered Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[PK__Entity__2E1BDC42] AS [ent]), SEEK:([ent].[Id]=(187317)) ORDERED FORWARD)
            |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                      |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                      |    |--Table Spool(WITH STACK)
                      |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

Я попытался сделать реорганизацию сущности ПК, и работает sp_updatestats но не имеет значения.

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

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt
OPTION (OPTIMIZE FOR (@TopLevelEnt = 187317))

Я запускаю это в базе данных SQL Server 2005 Express из SQL Server Management Studio Express 2008 R2

Любые намеки или взлом будут оценены.

Answer 1

То, что вы видите, нормально, потому что представление сначала возвращает все, а затем смотрит на переменную и фильтрует соответственно. Когда он жестко запрограммирован, он выбирает другой план, поскольку с самого начала знает, какой фильтр применить к нему.

Вы пытались сделать то же самое с помощью встроенной функции табличного значения с фильтром в верхнем выделении cte?