una - como saber version de sql instalada
Si detengo una consulta de larga ejecución, ¿se revierte? (12)
Una consulta que se utiliza para recorrer 17 millones de registros para eliminar duplicados se ha estado ejecutando durante aproximadamente 16 horas y quería saber si la consulta está detenida en este momento si finalizará las instrucciones de eliminación o si se ha eliminado al ejecutar este documento. ¿consulta? De hecho, si lo detengo, ¿finaliza las eliminaciones o retrocede?
He encontrado que cuando hago un
select count(*) from myTable
Que las filas que devuelve (mientras hace esta consulta) son aproximadamente 5 menos que el recuento de la fila inicial. Obviamente, los recursos del servidor son extremadamente pobres, ¿eso significa que este proceso ha tardado 16 horas en encontrar 5 duplicados (cuando en realidad hay miles) y esto podría estar funcionando durante días?
Esta consulta tomó 6 segundos en 2000 filas de datos de prueba, y funciona muy bien en ese conjunto de datos, así que pensé que tomaría 15 horas para el conjunto completo.
¿Algunas ideas?
Debajo está la consulta:
--Declare the looping variable
DECLARE @LoopVar char(10)
DECLARE
--Set private variables that will be used throughout
@long DECIMAL,
@lat DECIMAL,
@phoneNumber char(10),
@businessname varchar(64),
@winner char(10)
SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable)
WHILE @LoopVar is not null
BEGIN
--initialize the private variables (essentially this is a .ctor)
SELECT
@long = null,
@lat = null,
@businessname = null,
@phoneNumber = null,
@winner = null
-- load data from the row declared when setting @LoopVar
SELECT
@long = longitude,
@lat = latitude,
@businessname = BusinessName,
@phoneNumber = Phone
FROM MyTable
WHERE RecordID = @LoopVar
--find the winning row with that data. The winning row means
SELECT top 1 @Winner = RecordID
FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
ORDER BY
CASE WHEN webAddress is not null THEN 1 ELSE 2 END,
CASE WHEN caption1 is not null THEN 1 ELSE 2 END,
CASE WHEN caption2 is not null THEN 1 ELSE 2 END,
RecordID
--delete any losers.
DELETE FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
AND @winner != RecordID
-- prep the next loop value to go ahead and perform the next duplicate query.
SET @LoopVar = (SELECT MIN(RecordID)
FROM MyTable
WHERE @LoopVar < RecordID)
END
Creo que esta consulta sería mucho más eficiente si se volviera a escribir utilizando un algoritmo de pase único con un cursor. Debería ordenar su tabla de cursores por longitud, latitud, BusinessName Y @phoneNumber. Pasaría por las filas de a una por vez. Si una fila tiene la misma longitud, latitud, nombre de empresa y número de teléfono que la fila anterior, elimínela.
Estoy bastante seguro de que es negativo. De lo contrario, ¿cuál sería el punto de las transacciones?
Heredé un sistema que tenía una lógica como la suya implementada en SQL. En nuestro caso, intentábamos vincular filas juntas usando coincidencias difusas que tenían nombres / direcciones similares, etc., y esa lógica se hacía puramente en SQL. En el momento en que lo heredé teníamos aproximadamente 300,000 filas en la tabla y de acuerdo con los tiempos, calculamos que tomaría un AÑO para unirlas todas.
Como un experimento para ver cuánto más rápido podría hacerlo fuera de SQL, escribí un programa para volcar la tabla db en archivos planos, leer los archivos planos en un programa C ++, crear mis propios índices y hacer la lógica difusa allí, luego vuelva a importar los archivos planos en la base de datos. Lo que llevó A AE en SQL llevó unos 30 segundos en la aplicación C ++.
Entonces, mi consejo es que ni siquiera pruebes lo que estás haciendo en SQL. Exportar, procesar, volver a importar.
Si no hace nada explícito acerca de las transacciones, entonces la conexión estará en modo de transacción automática . En este modo, cada declaración de SQL se considera una transacción.
La pregunta es si esto significa que las sentencias de SQL individuales son transacciones y, por lo tanto, se comprometen sobre la marcha, o si el bucle WHILE externo cuenta como una transacción.
No parece haber ninguna discusión sobre esto en la descripción del constructo WHILE en MSDN . Sin embargo, como una instrucción WHILE no puede modificar directamente la base de datos, parecería lógico que no inicie una transacción de confirmación automática.
Su consulta no está envuelta en una transacción, por lo que no revertirá los cambios ya realizados por las declaraciones de eliminación individuales.
Específicamente probé esto en mi propio SQL Server usando la siguiente consulta, y la tabla ApplicationLog estaba vacía a pesar de que cancelé la consulta:
declare @count int
select @count = 5
WHILE @count > 0
BEGIN
print @count
delete from applicationlog;
waitfor time ''20:00'';
select @count = @count -1
END
Sin embargo, es probable que su consulta demore varios días o semanas, mucho más que 15 horas. Su estimación de que puede procesar 2000 registros cada 6 segundos es incorrecta porque cada iteración en su ciclo while tomará mucho más tiempo con 17 millones de filas que con 2000 filas. Entonces, a menos que su consulta tome mucho menos que un segundo para 2000 filas, tomará días para los 17 millones.
Debería hacer una nueva pregunta sobre cómo puede eliminar filas duplicadas de manera eficiente.
no, el servidor SQL no retrotraerá las eliminaciones que ya ha realizado si detiene la ejecución de la consulta. Oracle requiere una confirmación explícita de consultas de acción o los datos se retrotraen, pero no mssql.
con el servidor sql, no retrocederá a menos que esté ejecutando específicamente en el contexto de una transacción y restituya esa transacción, o la conexión se cierre sin que se haya confirmado la transacción. pero no veo un contexto de transacción en su consulta anterior.
También podría intentar reestructurar su consulta para hacer que las eliminaciones sean un poco más eficientes, pero esencialmente si las especificaciones de su caja no están listas para el consumo, entonces puede que esté estancado al esperarlo.
En el futuro, debe crear un índice único en la tabla para evitar tener que pasar por esto de nuevo.
Transacciones implícitas
Si no se ha establecido ''Transacciones implícitas'', cada iteración en su ciclo cometió los cambios.
Es posible que cualquier servidor SQL se configure con ''Transacciones implícitas''. Esta es una configuración de base de datos (por defecto está desactivada). También puede tener transacciones implícitas en las propiedades de una consulta particular dentro de Management Studio (haga clic con el botón derecho en el panel de consulta> opciones), de forma predeterminada en el cliente o en una declaración SET.
SET IMPLICIT_TRANSACTIONS ON;
De cualquier manera, si este fuera el caso, aún necesitaría ejecutar un COMMIT / ROLLBACK explícito independientemente de la interrupción de la ejecución de la consulta.
Referencia de transacciones implícitas:
Creo que debes considerar seriamente tu metodología. Debe comenzar a pensar en conjuntos (aunque para el rendimiento puede necesitar procesamiento por lotes, pero no fila por fila en una tabla de registro de 17 millones).
Primero, ¿todos sus registros tienen duplicados? Sospecho que no, así que lo primero que quieres hacer es limitar tu procesamiento a solo aquellos registros que tienen duplicados. Dado que esta es una tabla grande y es posible que tenga que realizar las eliminaciones en lotes a lo largo del tiempo, dependiendo de qué otro proceso esté sucediendo, primero extraiga los registros que desea tratar en una tabla propia que luego indexará. También puede usar una tabla temporal si va a poder hacer todo esto al mismo tiempo sin detenerlo de otra manera, cree una tabla en su base de datos y colóquela al final.
Algo así como (Tenga en cuenta que no escribí las declaraciones de creación de índices, me imagino que puede buscarlo usted mismo):
SELECT min(m.RecordID), m.longitude, m.latitude, m.businessname, m.phone
into #RecordsToKeep
FROM MyTable m
join
(select longitude, latitude, businessname, phone
from MyTable
group by longitude, latitude, businessname, phone
having count(*) >1) a
on a.longitude = m.longitude and a.latitude = m.latitude and
a.businessname = b.businessname and a.phone = b.phone
group by m.longitude, m.latitude, m.businessname, m.phone
ORDER BY CASE WHEN m.webAddress is not null THEN 1 ELSE 2 END,
CASE WHEN m.caption1 is not null THEN 1 ELSE 2 END,
CASE WHEN m.caption2 is not null THEN 1 ELSE 2 END
while (select count(*) from #RecordsToKeep) > 0
begin
select top 1000 *
into #Batch
from #RecordsToKeep
Delete m
from mytable m
join #Batch b
on b.longitude = m.longitude and b.latitude = m.latitude and
b.businessname = b.businessname and b.phone = b.phone
where r.recordid <> b.recordID
Delete r
from #RecordsToKeep r
join #Batch b on r.recordid = b.recordid
end
Delete m
from mytable m
join #RecordsToKeep r
on r.longitude = m.longitude and r.latitude = m.latitude and
r.businessname = b.businessname and r.phone = b.phone
where r.recordid <> m.recordID
También intente pensar otro método para eliminar filas duplicadas:
delete t1 from table1 as t1 where exists (
select * from table1 as t2 where
t1.column1=t2.column1 and
t1.column2=t2.column2 and
t1.column3=t2.column3 and
--add other colums if any
t1.id>t2.id
)
Supongo que tienes una columna de identificación de enteros en tu tabla.
Si su máquina no tiene hardware muy avanzado, el servidor SQL tardará mucho tiempo en completar ese comando. No sé con certeza cómo se realiza esta operación bajo el capó, pero según mi experiencia, esto podría hacerse de manera más eficiente sacando los registros de la base de datos y en la memoria de un programa que usa una estructura de árbol con una regla de eliminación de duplicados. para la inserción. Intente leer la totalidad de la tabla en trozos (digamos 10000 filas a la vez) en un programa C ++ usando ODBC. Una vez en el programa C ++ utilice std :: map donde key es la clave única y struct es una estructura que contiene el resto de los datos en variables. Pasa el cursor por todos los registros y realiza la inserción en el mapa. La función de inserción de mapas se encargará de eliminar los duplicados. Dado que la búsqueda dentro de un mapa es lg (n), el tiempo es mucho menor para encontrar duplicados que el uso de su ciclo while. A continuación, puede eliminar toda la tabla y volver a agregar las tuplas en la base de datos desde el mapa formando consultas de inserción y ejecutándolas mediante odbc o creando un script de archivo de texto y ejecutándolo en el estudio de administración.
Como un bucle, su consulta tendrá problemas para escalar bien, incluso con los índices apropiados. La consulta debe reescribirse a una sola declaración, según las sugerencias en su pregunta anterior sobre esto.
Si no lo está ejecutando de forma explícita dentro de una transacción, solo retrotraerá la instrucción en ejecución.
Los DELETES que se han realizado hasta este momento no se revertirán.
Como el autor original del código en cuestión , y habiendo emitido la advertencia de que el rendimiento dependerá de los índices, propondría los siguientes elementos para acelerar esto.
RecordId better be PRIMARY KEY. No me refiero a IDENTIDAD, me refiero a PRIMARY KEY. Confirma esto usando sp_help
Se debe usar algún índice para evaluar esta consulta. Averiguar cuál de estas cuatro columnas tiene la menor cantidad de repeticiones e índices que ...
SELECT *
FROM MyTable
WHERE @long = longitude
AND @lat = latitude
AND @businessname = BusinessName
AND @phoneNumber = Phone
Antes y después de agregar este índice, verifique el plan de consulta para ver si se ha agregado la exploración de índice.