Проблемы с графиком: подключиться с помощью NOCYCLE до замены на сервере SQL?

вопрос:

У меня есть следующий (ориентированный) граф:

И эта таблица:

CREATE TABLE [dbo].[T_Hops](
    [UID] [uniqueidentifier] NULL,
    [From] [nvarchar](1000) NULL,
    [To] [nvarchar](1000) NULL,
    [Distance] [decimal](18, 5) NULL
) ON [PRIMARY]

GO

И это содержание:

      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'A'              ,'E'              ,10.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'E'              ,'D'              ,20.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'A'              ,'B'              ,5.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'B'              ,'C'              ,10.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'C'              ,'D'              ,5.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'A'              ,'F'              ,2.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'F'              ,'G'              ,6.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'G'              ,'H'              ,3.00000              );   
      INSERT INTO [dbo].[T_Hops]             ([UID]             ,[From]             ,[To]             ,[Distance])       VALUES             (newid()              ,'H'              ,'D'              ,1.00000              );   

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

WITH AllRoutes 
(
     [UID]
    ,[FROM]
    ,[To]
    ,[Distance]
    ,[Path]
    ,[Hops]
)
AS
(
    SELECT 
         [UID]
        ,[FROM]
        ,[To]
        ,[Distance]
        ,CAST(([dbo].[T_Hops].[FROM] + [dbo].[T_Hops].[To]) AS varchar(MAX)) AS [Path]
        ,1 AS [Hops]
      FROM [dbo].[T_Hops]
      WHERE [FROM] = 'A'

    UNION ALL


    SELECT 
         [dbo].[T_Hops].[UID]
        --,[dbo].[T_Hops].[FROM]
        ,Parent.[FROM]
        ,[dbo].[T_Hops].[To]
        ,CAST((Parent.[Distance] + [dbo].[T_Hops].[Distance]) AS [decimal](18, 5)) AS distance
        ,CAST((Parent.[Path] + '/' + [dbo].[T_Hops].[FROM] + [dbo].[T_Hops].[To]) AS varchar(MAX)) AS [Path]
        ,(Parent.[Hops] + 1) AS [Hops]
     FROM [dbo].[T_Hops]

    INNER JOIN AllRoutes AS Parent 
            ON Parent.[To] = [dbo].[T_Hops].[FROM] 

)

SELECT TOP 100 PERCENT * FROM AllRoutes


/*
WHERE [FROM] = 'A' 
AND [To] = 'D'
AND CHARINDEX('F', [Path]) != 0 -- via F
ORDER BY Hops, Distance ASC
*/

GO

Теперь я хочу создать неориентированный граф, для этого я могу, например, также получить путь от D к A

Я начинаю с самого простого изменения и просто указываю обратное направление для HD.

INSERT INTO [dbo].[T_Hops]
           ([UID]
           ,[From]
           ,[To]
           ,[Distance])
     VALUES
           (newid() --<UID, uniqueidentifier,>
           ,'D' --<From, nvarchar(1000),>
           ,'H' --<To, nvarchar(1000),>
           ,1 --<Distance, decimal(18,5),>
           )
GO

Теперь, как и ожидалось, мой запрос выдает исключение:

Бесконечная рекурсия / максимальный уровень рекурсии ( 100) превышено

Потому что количество возможных соединений теперь бесконечно.

Теперь в Oracle вы делаете то же самое с «соединением по предшествующему» вместо дерева. И если возможна проблема цикла (бесконечная рекурсия), вы просто добавляете NOCYCLE для CONNECT BY PRIOR, сделав его «CONNECT BY NOCYCLE PRIOR»

Теперь в MS-SQL я исправил это поведение, добавив:

AND Parent.[Path] NOT LIKE '%' + [dbo].[T_Hops].[FROM] + '/%'

во внутреннее предложение соединения, по сути имитируя NOCYCLE.

Однако как LIKE в основном strstr (или хуже strcasestr), и, таким образом, максимально медленнее, чем проверка массива родительских элементов, Я очень беспокоюсь о производительности.

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

У кого-нибудь еще есть лучший (= более быстрый) метод замены NOCYCLE в MS SQL?

Или это тот момент, когда у меня просто нет другого выбора, кроме как переключиться на Oracle (для того, чтобы сделать это с приемлемой скоростью)?

Примечание: Любые временные таблицы (большой объем данных) будут работать медленнее, потому что временные таблицы будут перенесены на жесткий диск когда не хватает ОЗУ (абсолютная уверенность).

То же самое касается любого решения, использующего функции и функции с табличным значением.