sql-server - tablas - primary key sql

Creación de una clave principal en una tabla temporal: ¿cuándo? (9)

Tengo un procedimiento almacenado que está trabajando con una gran cantidad de datos. Tengo esos datos que están siendo insertados en una tabla temporal. El flujo general de eventos es algo así como

CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an identity column. ,Col2 INT NOT NULL, ,Col3 BIGINT, ,Col4 VARCHAR(25) NOT NULL, --Etc... -- --Create primary key here? ) INSERT INTO #TempTable SELECT ... FROM MyTable WHERE ... INSERT INTO #TempTable SELECT ... FROM MyTable2 WHERE ... -- -- ...or create primary key here?

Mi pregunta es ¿cuándo es el mejor momento para crear una clave principal en mi tabla #TempTable? Teoricé que debería crear la restricción / índice de la clave principal después de insertar todos los datos porque el índice debe reorganizarse a medida que se crea la información de la clave principal. Pero me di cuenta de que mi suposición de subrayado podría estar equivocada ...

En caso de que sea relevante, los tipos de datos que utilicé son reales. En la tabla #TempTable , Col4 y Col4 formarán mi clave principal.

Actualización: en mi caso, estoy duplicando la clave principal de las tablas de origen. Sé que los campos que conformarán mi clave principal siempre serán únicos. No tengo ninguna preocupación acerca de una tabla de modificación fallida si agrego la clave principal al final.

Sin embargo, aparte de eso, mi pregunta sigue siendo ¿cuál es más rápido si asumimos que ambos tendrían éxito ?

PD. Lo siento si esto es un duplicado. Es lo suficientemente básico como podría ser, pero no he podido encontrar nada igual.

Aún más importante que las consideraciones de rendimiento, si no está ABSOLUTAMENTE, 100% seguro de que se insertarán valores únicos en la tabla, cree primero la clave principal. De lo contrario, la clave principal no se creará.

Esto le impide insertar datos duplicados / malos.

Cuando agrega PK en la creación de la tabla, la verificación de inserción es O(Tn) (donde Tn es "n-th número triangular", que es 1 + 2 + 3 ... + n ) porque cuando inserta la fila x, se verifica contra filas "x - 1" previamente insertadas

Cuando agrega PK después de insertar todos los valores, el verificador es O(n^2) porque al insertar la fila x, se verifica contra todas las n filas existentes.

El primero es obviamente más rápido ya que O(Tn) es menor que O(n^2)

Ejemplo de PS: si inserta 5 filas es 1 + 2 + 3 + 4 + 5 = 15 operaciones vs 5^2 = 25 operaciones

Esto depende mucho

Si crea el índice de clave principal agrupado después de la carga, la tabla completa se volverá a escribir ya que el índice agrupado no es realmente un índice, es el orden lógico de los datos. Su plan de ejecución en las inserciones dependerá de los índices establecidos cuando se determine el plan, y si el índice agrupado está en su lugar, se ordenará antes de la inserción. Normalmente verá esto en el plan de ejecución.

Si hace que la clave principal sea una restricción simple, será un índice regular (no agrupado) y la tabla simplemente se completará en el orden que determine el optimizador y se actualizará el índice.

Creo que el rendimiento más rápido en general (de este proceso para cargar la tabla temporal) es escribir los datos como un montón y luego aplicar el índice (no agrupado).

Sin embargo, como otros han señalado, la creación del índice podría fallar. Además, la tabla temporal no existe de forma aislada. Presumiblemente hay un mejor índice para leer los datos de él para el siguiente paso. Este índice deberá estar en su lugar o creado. Aquí es donde debe hacer una compensación de velocidad aquí para la confiabilidad (aplique el PK y cualquier otra restricción primero) y la velocidad más adelante (tenga al menos el índice agrupado en su lugar si va a tener uno).

Me preguntaba si podría mejorar un procedimiento almacenado muy "costoso" que implicara un montón de comprobaciones en cada inserción en las tablas y encontré esta respuesta. En el Sproc, varias tablas temporales se abren y hacen referencia entre sí. Agregué la clave principal a la sentencia CREATE TABLE (aunque mis selecciones usan las sentencias WHERE NOT EXISTS para insertar datos y garantizar la singularidad) y mi tiempo de ejecución se redujo MUCHO. Recomiendo encarecidamente utilizar las claves primarias. Siempre, al menos, inténtalo incluso cuando creas que no lo necesitas.

No creo que haga ninguna diferencia significativa en su caso:

o bien paga la multa poco a poco, con cada inserción individual
o pagará una multa mayor después de que se realicen todas las inserciones, pero solo una vez

Cuando lo crea por adelantado antes de que comiencen las inserciones, podría detectar violaciones de PK mientras se insertan los datos, si el valor de PK no es creado por el sistema.

Pero aparte de eso, no hay gran diferencia, en realidad.

Bagazo

No planeaba responder esto, ya que no estoy 100% seguro de mi conocimiento de esto. Pero como no parece que estés recibiendo mucha respuesta ...

Mi entendimiento es que un PK es un índice único y cuando inserta cada registro, su índice se actualiza y optimiza. Entonces ... si agrega los datos primero, luego cree el índice, el índice solo se optimiza una vez.

Entonces, si está seguro de que sus datos están limpios (sin datos de PK duplicados), diría que inserte y luego agregue el PK.

Pero si sus datos pueden tener datos de PK duplicados, diría que primero cree el PK, por lo que se eliminará lo antes posible.

Si agrega la clave principal al crear la tabla, la primera inserción será gratuita (no se requieren comprobaciones). La segunda inserción solo tiene que ver si es diferente de la primera. La tercera inserción tiene que verificar dos filas, y así sucesivamente. Las comprobaciones serán búsquedas de índice, porque hay una restricción única en su lugar.

Si agrega la clave principal después de todas las inserciones, cada fila debe coincidir con cada otra fila. Así que mi conjetura es que agregar una clave principal desde el principio es más barato.

Pero quizás Sql Server tenga una manera realmente inteligente de verificar la singularidad. Así que si quieres estar seguro, mídelo!

Si el modelo de recuperación de su base de datos está configurado como simple o de registro masivo, SELECT ... INTO ... UNION ALL puede ser la solución más rápida. SELECT .. INTO es una operación masiva y las operaciones masivas se registran mínimamente.

p.ej:

-- first, create the table SELECT ... INTO #TempTable FROM MyTable WHERE ... UNION ALL SELECT ... FROM MyTable2 WHERE ... -- now, add a non-clustered primary key: -- this will *not* recreate the table in the background -- it will only create a separate index -- the table will remain stored as a heap ALTER TABLE #TempTable ADD PRIMARY KEY NONCLUSTERED (NonNullableKeyField) -- alternatively: -- this *will* recreate the table in the background -- and reorder the rows according to the primary key -- CLUSTERED key word is optional, primary keys are clustered by default ALTER TABLE #TempTable ADD PRIMARY KEY CLUSTERED (NonNullableKeyField)

De lo contrario, Cade Roux tenía un buen consejo con respecto a: antes o después.

También puede crear la clave principal antes de las inserciones: si la clave principal está en una columna de identidad, las inserciones se realizarán secuencialmente de todos modos y no habrá diferencia.