递归CTE增加了时间

Question

我刚刚编写了这段代码，My_View表大约有9000行，CTE表大约有14000行。CTE的第一次迭代持续了大约0.5秒(手写代码(，但对于递归，它持续了大约5分钟。主要问题应该是递归代码，但不应该。

代码的目标是：具有以下数据：

｛ID｝〔主要ID〕〔次要ID〕

所有的主ID都以C开头…而辅助ID以K开头…问题是一些辅助ID是到主ID的链接，如下所示：

{ID} [C010] [K011]
{ID} [C020] [C010]
{ID} [C020] [K020]
So what I want is it to finish like:
{ID} [C010] [K011]
{ID} [C020] [K011]
{ID} [C020] [K020]
{ID} = {[Cod_ 1], [First year], [First month]}
WITH CTE AS (   SELECT DISTINCT [Cod_ 1], [First year], [First month], [Primary ID], [Secondary ID]     FROM My_View    WHERE [Secondary ID] LIKE 'K%'
UNION ALL
SELECT m1.[Cod_ 1], m1.[First year], m1.[First month], m1.[Primary ID], [m2.Secondary ID]   FROM My_View m1 INNER JOIN CTE m2 ON m1.[Cod_ 1] = m2.[Cod_ 1] AND m1.[First year] = m2.[First year] AND m1.[First month] = m2.[First month] AND m1.[Secondary ID] = m2.[Primary ID]
)
SELECT DISTINCT *
FROM CTE
ORDER BY [Cod_ 1], [Primary ID], [Secondary ID]

Answer 1

我认为您需要添加类似的WHERE条件

WHERE m1.[Primary ID] NOT LIKE 'K%'

为了避免递归深度错误或任何类似的情况都可能使其变慢。

因此，这可能会对您有所帮助：

WITH CTE
AS (
SELECT DISTINCT [Cod_ 1]
,[First year]
,[First month]
,[Primary ID]
,[Secondary ID]
FROM My_View
WHERE [Secondary ID] LIKE 'K%'

UNION ALL

SELECT m1.[Cod_ 1]
,m1.[First year]
,m1.[First month]
,m1.[Primary ID]
,[m2.Secondary ID]
FROM My_View m1
INNER JOIN CTE m2 ON m1.[Cod_ 1] = m2.[Cod_ 1]
AND m1.[First year] = m2.[First year]
AND m1.[First month] = m2.[First month]
AND m1.[Secondary ID] = m2.[Primary ID]
WHERE m1.[Primary ID] NOT LIKE 'K%'
)
SELECT DISTINCT *
FROM CTE
ORDER BY [Cod_ 1]
,[Primary ID]
,[Secondary ID]