importar - sql server import and export wizard access

Usando "IN" en una cláusula WHERE donde el número de elementos en el conjunto es muy grande

Question

Tengo una situación en la que necesito hacer una actualización en un gran conjunto de filas que solo puedo identificar por su ID (dado que los registros de destino son seleccionados por el usuario y no tienen nada en común más que el conjunto de registros que el usuario quería modificar). La misma propiedad se está actualizando en todos estos registros, así que me gustaría hacer una sola llamada ACTUALIZACIÓN.

¿Es una mala práctica o hay una forma mejor de hacer esta actualización que usar "WHERE IN (1,2,3,4, ..... 10000)" en la instrucción UPDATE?

¿Tendría más sentido utilizar estados de actualización individuales para cada registro y pegarlos en una sola transacción? En este momento estoy trabajando con SQL Server y Access pero, de ser posible, me gustaría escuchar soluciones más amplias de mejores prácticas en cualquier tipo de base de datos relacional.

Answer 1

¿Cómo se genera la cláusula IN?

Si hay otra instrucción SELECT que genere esos valores, simplemente puede conectar eso en la ACTUALIZACIÓN de la siguiente manera:

UPDATE TARGET_TABLE T
SET
SOME_VALUE = ''Whatever''
WHERE T.ID_NUMBER IN(
SELECT ID_NUMBER --this SELECT generates your ID #s.
FROM SOURCE_TABLE
WHERE SOME_CONDITIONS
)

En algunos RDBMses, obtendrá un mejor rendimiento utilizando la sintaxis EXISTS, que se vería así:

UPDATE TARGET_TABLE T
SET
SOME_VALUE = ''Whatever''
WHERE EXISTS (
SELECT ID_NUMBER --this SELECT generates your ID #s.
FROM SOURCE_TABLE S
WHERE SOME_CONDITIONS
AND S.ID_NUMBER = T.ID_NUMBER
)

Answer 2

En Oracle hay un límite de valores que puede poner en una cláusula IN. Así que es mejor usar un OR, x = 1 o x = 2 ... esos no son limitados, hasta donde yo sé.

Answer 3

En general, hay varias cosas que considerar.

1. El enunciado de análisis de caché en el DB. Cada afirmación, con un número diferente de elementos en la cláusula IN, debe analizarse por separado. ESTÁS usando variables encuadernadas en lugar de literales, ¿verdad?
2. Algunas bases de datos tienen un límite en la cantidad de elementos en la cláusula IN. Para Oracle es 1000.
3. Al actualizar, bloquea los registros. Si tiene varias instrucciones de actualización separadas, puede tener bloqueos sin salida. Esto significa que debe tener cuidado con el orden en que publica sus actualizaciones.
4. La latencia de ida y vuelta a la base de datos puede ser alta, incluso para una declaración muy rápida. Esto significa que a menudo es mejor manipular muchos registros a la vez para ahorrar tiempo de viaje.

Recientemente cambiamos nuestro sistema para limitar el tamaño de las cláusulas internas y siempre utilizamos variables vinculadas porque esto redujo el número de sentencias SQL diferentes y, por lo tanto, mejoró el rendimiento. Básicamente generamos nuestras sentencias SQL y ejecutamos múltiples sentencias si la cláusula in excede un cierto tamaño. No hacemos esto para las actualizaciones, así que no hemos tenido que preocuparnos por el bloqueo. Vas a.

El uso de una tabla temporal puede no mejorar el rendimiento porque debe completar la tabla temporal con los ID. Las pruebas de experimentación y rendimiento pueden indicarle la respuesta aquí.

Una sola cláusula IN es muy fácil de entender y mantener. Esto es probablemente de lo que deberías preocuparte primero. Si observa que el rendimiento de las consultas es deficiente, puede intentar una estrategia diferente y ver si ayuda, pero no optimizar de forma prematura. La cláusula IN es semánticamente correcta, así que déjala en paz si no está rota.

Answer 4

Otra alternativa es almacenar esos números en una tabla temporal y usarlos en una combinación para hacer la actualización. Si puede ejecutar una sola declaración de actualización es definitivamente mejor que ejecutar una declaración por registro.

Answer 5

Si estuvieras en Oracle, te recomendaría usar funciones de tabla, similar a la publicación de Marc Gravell.

-- first create a user-defined collection type, a table of numbers
create or replace type tbl_foo as table of number;
declare
temp_foo tbl_foo;
begin
-- this could be passed in as a parameter, for simplicity I am hardcoding it
temp_foo := tbl_foo(7369, 7788);
-- here I use a table function to treat my temp_foo variable as a table,
-- and I join it to the emp table as an alternative to a massive "IN" clause
select e.*
from emp e,
table(temp_foo) foo
where e.empno = foo.column_value;
end;

Answer 6

Sin saber qué cantidad de identificaciones "muy grandes" podría haber, me atrevería a adivinar. ;-) Dado que está utilizando Access como base de datos, la cantidad de ID no puede ser tan alta. Suponiendo que estamos hablando de menos de, digamos 10,000 números y deberíamos saber las limitaciones de los contenedores para contener los ID (¿qué idioma se usa para el front-end?) Me quedaría con una declaración UPDATE, si eso es más legible y más fácil de realizar el mantenimiento más tarde. De lo contrario, los dividiría en múltiples declaraciones utilizando una lógica inteligente. Algo así como dividir la declaración en varias declaraciones con una, diez, cien, mil ... ID por declaración. Entonces, dejaría que el optimizador de DB ejecute la declaración lo más eficientemente posible. Probablemente haría una ''explicación'' en la consulta / consultas para asegurarme de que no pasa nada tonto. Pero en mi experiencia, con frecuencia es correcto dejar este tipo de optimización al administrador de la base de datos. Lo que más tiempo lleva es generalmente la conexión real a la base de datos, por lo que si puede ejecutar todas las consultas dentro de la misma conexión, normalmente no hay problemas. Asegúrese de enviar todas las instrucciones UPDATE antes de comenzar a buscar y esperar a que vuelvan los conjuntos de resultados. :-)

Answer 7

Yo siempre usaría

WHERE id IN (1,2,3,4,.....10000)

a menos que su cláusula in sea estúpidamente grande, lo que realmente no debería ocurrir a partir de la entrada del usuario.

editar: por ejemplo, Rails hace esto mucho detrás de la escena

Definitivamente no sería mejor hacer declaraciones de actualización separadas en una sola transacción.

Answer 8

Yo usaría una tabla-variable / temp-table; inserta los valores en esto, y únete a él. Entonces puede usar el mismo conjunto varias veces. Esto funciona especialmente bien si está (por ejemplo) transmitiendo un CSV de ID como varchar. Como un ejemplo de SQL Server:

DECLARE @ids TABLE (id int NOT NULL)
INSERT @ids
SELECT value
FROM dbo.SplitCsv(@arg) // need to define separately
UPDATE t
SET t. // etc
FROM [TABLE] t
INNER JOIN @ids #i ON #i.id = t.id

Answer 9

No sé el tipo de valores en su lista IN. Si son la mayoría de los valores de 1 a 10,000, es posible que pueda procesarlos para obtener algo como:

WHERE MyID BETWEEN 1 AND 10000 AND MyID NOT IN (3,7,4656,987)

O bien, si la lista NOT IN aún sería larga, procesando la lista y generando un montón de declaraciones BETWEEN:

WHERE MyID BETWEEN 1 AND 343 AND MyID BETWEEN 344 AND 400 ...

Etcétera.

Por último, no tiene que preocuparse por cómo Jet procesará una cláusula IN si utiliza una consulta de paso a través. No puede hacer eso en el código, pero podría tener un QueryDef guardado que se define como un paso a paso y alterar la cláusula WHERE en el código en tiempo de ejecución para usar su lista IN. Luego todo se pasa a SQL Server, y SQL Server decidirá mejor cómo procesarlo.