not - when in sql
SQL WHERE ID IN(id1, id2,..., idn) (8)
Necesito escribir una consulta para recuperar una gran lista de identificadores.
Admitimos muchos backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL ...) así que tengo que escribir un SQL estándar.
El tamaño del conjunto de identificación podría ser grande, la consulta se generaría mediante programación. Entonces, ¿cuál es el mejor enfoque?
1) Escribir una consulta usando IN
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
Mi pregunta aquí es ¿Qué pasa si n es muy grande? Además, ¿qué pasa con el rendimiento?
2) Escribir una consulta usando OR
SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn
Creo que este enfoque no tiene n límite, pero ¿qué pasa con el rendimiento si n es muy grande?
3) Escribir una solución programática:
foreach (id in myIdList)
{
item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
myObjectList.Add(item);
}
Experimentamos algunos problemas con este enfoque cuando se consulta el servidor de la base de datos a través de la red. Normalmente es mejor hacer una consulta que recupere todos los resultados, mejor esa cantidad de consultas pequeñas. Puede ser que esté equivocado.
¿Cuál sería la solución correcta para este problema?
Creo que te refieres a SqlServer pero en Oracle tienes un límite estricto de cuántos elementos de IN puedes especificar: 1000.
En la mayoría de los sistemas de bases de datos, IN (val1, val2, …) y una serie de OR están optimizados para el mismo plan.
La tercera forma sería importar la lista de valores en una tabla temporal y unirla, que es más eficiente en la mayoría de los sistemas, si hay muchos valores.
Es posible que desee leer estos artículos:
Hacer el comando SELECT * FROM MyTable donde id in () en una tabla Azure SQL con 500 millones de registros resultó en un tiempo de espera de> 7min.
Al hacerlo, en su lugar, devolvió los resultados de inmediato:
select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id
Usa un join
La muestra 3 sería la que peor funcionaría de todas porque está golpeando la base de datos innumerables veces sin motivo aparente.
Cargar los datos en una tabla temporal y luego unirlos sería, con mucho, el más rápido. Después de eso, la RI debería funcionar un poco más rápido que el grupo de RUP.
La opción 1 es la única solución buena.
¿Por qué?
La opción 2 hace lo mismo, pero repites el nombre de la columna muchas veces; Además, el motor SQL no sabe de inmediato que desea verificar si el valor es uno de los valores en una lista fija. Sin embargo, un buen motor SQL podría optimizarlo para tener el mismo rendimiento que con
IN. Todavía hay el problema de legibilidad aunque ...La opción 3 es simplemente horrible en cuanto a rendimiento. Envía una consulta cada ciclo y martilla la base de datos con pequeñas consultas. También evita que utilice optimizaciones para "el valor es uno de los que figuran en una lista dada"
Lo que Ed Guiness sugirió es realmente un refuerzo de rendimiento, tuve una consulta como esta
select * from table where id in (id1,id2.........long list)
lo que hice :
DECLARE @temp table(
ID int
)
insert into @temp
select * from dbo.fnSplitter(''#idlist#'')
Luego el interior se unió a la temperatura con la tabla principal:
select * from table inner join temp on temp.id = table.id
Y el rendimiento mejoró drásticamente.
Un enfoque alternativo podría ser usar otra tabla para contener valores de identificación. Esta otra tabla puede unirse internamente en su TABLA para restringir las filas devueltas. Esto tendrá la gran ventaja de que no necesitará SQL dinámico (problemático en el mejor de los casos), y no tendrá una cláusula IN infinitamente larga.
Podría truncar esta otra tabla, insertar su gran cantidad de filas y luego quizás crear un índice para ayudar al rendimiento de la unión. También le permitirá separar la acumulación de estas filas de la recuperación de datos, lo que quizás le brinde más opciones para ajustar el rendimiento.
Actualización : aunque podría utilizar una tabla temporal, no quise dar a entender que debe o incluso debería hacerlo. Una tabla permanente utilizada para datos temporales es una solución común con méritos más allá de lo descrito aquí.
La primera opción es definitivamente la mejor opción.
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
Sin embargo, teniendo en cuenta que la lista de identificadores es muy grande , digamos millones, debes considerar los tamaños de los fragmentos como a continuación:
- Divida su lista de ID en trozos de número fijo, digamos 100
- El tamaño del trozo debe decidirse en función del tamaño de la memoria de su servidor
- Supongamos que tiene 10000 Id., Tendrá 10000/100 = 100 trozos
- Procese un fragmento a la vez, lo que resulta en 100 llamadas a bases de datos para seleccionar
¿Por qué deberías dividirte en pedazos?
Nunca recibirá la excepción de desbordamiento de memoria, que es muy común en escenarios como el suyo. Tendrá un número optimizado de llamadas a la base de datos, lo que redundará en un mejor rendimiento.
Siempre me ha funcionado como un encanto. Espero que funcione para mis compañeros desarrolladores también :)