SQL Server - Iteración lenta y .log crece

Buenas tardes, he de realizar una query iterativa que inserte datos que no estén aun dentro de la misma tabla que consulto, donde tengo un número de lote y del lote anterior del que procede.

La tabla inicial, como ejemplo, sería:

Lote Lote Parent
---------------------------------
B A
C B
E C
E D
F C
F E

Lo que quiero realizar con la query es que me inserte dentro de esta misma tabla, todos los lotes que se han tenido que usar hasta llegar a ese lote, sin insertar duplicados.

Lot Lot Parent
B A
C A
C B
E A
E B
E C
E D
F A
F B
F C
F D
F E

He realizado esta query, sin embargo, dado que es una tabla con muchos registros me va muy lenta, además el archivo .log de la base de datos y los archivos temporales me crecen a valores de decenas de gb.

DECLARE @Count int
SET @Count=0


WHILE @Count<(SELECT COUNT(*) FROM TABLA_Lotes)
BEGIN
SET @Count=(SELECT COUNT(*) FROM TABLA_Lotes)
INSERT INTO TABLA_Lotes
SELECT Par.Lote, Par.PLote FROM(
SELECT A.[Lote] AS Lote, B.[Lote Parent] AS PLote FROM
TABLA_Lotes A
INNER JOIN TABLA_Lotes B
ON A.[Lote Parent]=B.[Lote]) AS Par
WHERE NOT EXISTS (SELECT * FROM TABLA_Lotes AS Tb WHERE Par.[Lote]=tb.[Lote] AND Par.PLote=Tb.[Lote Parent])
END


¿alguna solución? ¿Tengo algo erróneo?

Muchas gracias.

¿Hay INDICES?

¿Porque crece demasiado mi archivo log?

R= El log crecera, 1 porque tienes la base en FULL, 2 dependiendo del numero de transacciones efectuadas

Pero no quiero que crezca.

R= Manda tu base a SIMPLE o has un CHECKPOINT de vez en vez durante tus procesos

Gracias,

1. Si cree Indice:

CREATE TABLE TABLA_LOTES(
[Lote] nvarchar(30) NOT NULL,
[Lote Parent] nvarchar(30) NULL
) ON [PRIMARY]

CREATE INDEX indice_lotes
ON TABLA_LOTES(
[Lote],
[Lote Parent]
);
GO

2. La tengo en FULL, la cambio.

¿La query está optimizada, o hay alguna manera de que su rendimiento sea mayor? No logro que finalice. (esta horas ejecutandose) y finalmente me sale este mensaje:

Msg 1105, Level 17, State 2, Line 9
Could not allocate space for object 'dbo.SORT temporary run storage: 140737683849216' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 9
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

Para tener más capacidad he llevado la bd tempdb a un disco duro temporal (en una máquina virtual de azure) para intentar que no se produzca esta falta de espacio.

Muchas gracias.

¿Que version de SQL Server manejas?
¿Donde esta intalada la base TEMPDB? (en C: o tiene sus discos especiales)
La base TEMPDB debe crecer en MEGAS, ¿O esta creciendo en porcentaje?
¿De cuantos registros estamos hablando?
¿Ya le pasate tu QUERY al servicio de TUNING ADVISOR?

SQL Server 2014

En D: que es un disco temporal de la máquina virtual, una vez se reinicia la máquina todo lo que esta él se borra.

No consigo ver las esta base de datos dentro de SSMS, para saber si crece por megas o por porcentaje.

Estamos hablando de muchos registros, rondando la centena de millón, incluso más.

Si, pase tuning advisor y me indicó todo correcto, creo que lo he hecho bien, ya que no conocía esta herramienta.

Muchas gracias

"No consigo ver las esta base de datos dentro de SSMS, para saber si crece por megas o por porcentaje."

¿Porque?, deberia estar en la carpeta de BASES DEL SISTEMA.

¿Seguro que el TUNING ADVISOR no te recomendo indices o estadisticos?

En tu query:


¿En que momento cambia la variable @Count?