sql-server - sirve - with as() sql

¿Es buena la recursión en SQL Server?

Question

Tengo una tabla en el servidor SQL que tiene la estructura de árbol normal de Item_ID, Item_ParentID. Supongamos que quiero iterar y obtener todos los NIÑOS de un Item_ID en particular (en cualquier nivel).

Recursion parece un candidato intuitivo para este problema y puedo escribir una función de SQL Server para hacer esto.

¿Afectará esto el rendimiento si mi tabla tiene muchos registros? ¿Cómo evito la recursión y simplemente consulto la tabla? ¿Alguna sugerencia?

Answer 1

¿Estás usando SQL 2005?

De ser así, puede usar Expresiones de tabla comunes para esto. Algo en esta línea:

;
with CTE (Some, Columns, ItemId, ParentId) as 
(
    select Some, Columns, ItemId, ParentId
    from myTable 
    where ItemId = @itemID
    union all
    select a.Some, a.Columns, a.ItemId, a.ParentId
    from myTable as a
    inner join CTE as b on a.ParentId = b.ItemId
    where a.ItemId <> b.ItemId
)
select * from CTE

Answer 2

Tal vez un poco más de detalle esté en orden.

Si tiene una relación de detalle maestro como usted lo describe, ¿no obtendrá un JOIN simple lo que necesita?

Como en:

SELECT
  SOME_FIELDS
FROM
  MASTER_TABLE MT
 ,CHILD_TABLE CT
WHERE CT.PARENT_ID = MT.ITEM_ID

Answer 3

No debería necesitar recursión para niños ; solo está mirando el nivel directamente debajo (es decir, select * from T where ParentId = @parent ) - solo necesita recurrencia para todos los descendientes .

En SQL2005 puede obtener los descendientes con:

with AllDescendants (ItemId, ItemText) as (
    select t.ItemId, t.ItemText 
        from [TableName] t
    where t.ItemId = @ancestorId
    union
    select sub.ItemId, sub.ItemText 
        from [TableName] sub
            inner join [TableName] tree
            on tree.ItemId = sub.ParentItemId
)

Answer 4

No necesita recursividad en absoluto ... Tenga en cuenta que cambié las columnas a ItemID e ItemParentID para facilitar la escritura ...

DECLARE @intLevel INT SET @intLevel = 1

INSERT INTO TempTable(ItemID, ItemParentID, Level) SELECT ItemID, ItemParentID, @intLevel WHERE ItemParentID IS NULL

WHILE @intLevel < @TargetLevel BEGIN SET @intLevel = @intLevel + 1 INSERT INTO TempTable(ItemID, ItemParentID, Level) SELECt ItemID, ItemParentID, @intLevel WHERE ItemParentID IN (SELECT ItemID FROM TempTable WHERE Level = @intLevel-1) -- If no rows are inserted then there are no children IF @@ROWCOUNT = 0 BREAK END

SELECt ItemID FROM TempTable WHERE Level = @TargetLevel

Answer 5

Como respuesta general, es posible hacer algunas cosas bastante sofisticadas en SQL Server que normalmente necesitan recursión, simplemente mediante el uso de un algoritmo iterativo. Logré hacer un analizador XHTML en Transact SQL que funcionó sorprendentemente bien. El pretificador de código que escribí se realizó en un procedimiento almacenado. No es elegante, es más bien como ver búfalos haciendo Ballet. pero funciona .

Answer 6

El problema al que se enfrentará con la recursividad y el rendimiento es cuántas veces tendrá que recurrir para devolver los resultados. Cada llamada recursiva es otra llamada separada que deberá unirse en los resultados totales.

En SQL 2k5 puede usar una expresión de tabla común para manejar esta recursión:

WITH Managers AS 
( 
--initialization 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers  

u otra solución es aplanar la jerarquía en otra tabla

Employee_Managers
ManagerId (PK, FK en la tabla de empleados)
EmployeeId (PK, FK en la tabla de empleados)

Todos los parentescos de relación padre-hijo se almacenarán en esta tabla, por lo que si el Gerente 1 administra al Gerente 2 administra al empleado 3, la tabla se vería así:

ManagerId EmployeeId
1         2
1         3
2         1

Esto permite que la jerarquía sea consultada fácilmente:

select * from employee_managers em 
inner join employee e on e.employeeid = em.employeeid and em.managerid = 42

Lo cual devolvería a todos los empleados que tienen gerente 42. El aspecto positivo será un mayor rendimiento, pero la desventaja será mantener la jerarquía

Answer 7

Joe Celko tiene un libro (<- enlace a Amazon) específicamente sobre estructuras de árbol en bases de datos SQL. Si bien necesitaría recurrencia para su modelo y definitivamente habría un potencial de problemas de rendimiento allí, existen formas alternativas de modelar una estructura de árbol según lo que implique su problema específico, lo que podría evitar la recursión y brindar un mejor rendimiento.

Answer 8

Con el nuevo MS SQL 2005 puede usar la palabra clave WITH

Mira esta pregunta y particularmente esta respuesta .

Con Oracle, podría usar la palabra clave CONNECT BY para generar consultas jerárquicas ( sintaxis ).

AFAIK con MySQL deberás usar la recursión.

Alternativamente, siempre puedes construir una tabla de caché para tus registros de relaciones padre-> hijo