subqueries - with sql oracle

SQL se une a Vs SQL Subqueries(Performance)?

Question

Deseo saber si tengo una consulta de combinación como esta:

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

y una subconsulta algo como esto -

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

Cuando considero el rendimiento, ¿ cuál de las dos consultas sería más rápida y por qué ?

¿También hay un momento en que debería preferir uno sobre el otro?

Lo siento si esto es demasiado trivial y se me preguntó antes, pero estoy confundido al respecto. Además, sería genial si ustedes pueden sugerirme las herramientas que debería usar para medir el rendimiento de dos consultas. ¡Muchas gracias!

Answer 1

Bueno, creo que es una pregunta "vieja pero oro". ¡La respuesta es, depende!". Las presentaciones son un tema tan delicado que sería demasiado tonto decir: "Nunca uses subconsultas, siempre únete". En los siguientes enlaces, encontrará algunas de las mejores prácticas básicas que he encontrado que son muy útiles: Aquí 1 Aquí 2 Aquí 3

Tengo una mesa con 50000 elementos, el resultado que estaba buscando era 739 elementos.

Mi consulta al principio fue esta:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

y tardó 7,9 segundos en ejecutarse.

Mi consulta al fin es esta:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

y tomó 0.0256s

Buen SQL, bien.

Answer 2

Comience a mirar los planes de ejecución para ver las diferencias en cómo el servidor SQl los interpretará. También puede usar Profiler para ejecutar las consultas varias veces y obtener la diferencia.

No esperaría que estos sean tan horriblemente diferentes, donde puede obtener ganancias de rendimiento reales y grandes al usar uniones en lugar de subconsultas cuando usa subconsultas correlacionadas.

EXISTS es a menudo mejor que cualquiera de estos dos y cuando está hablando de uniones izquierdas donde desea todos los registros que no están en la tabla de unión izquierda, entonces NO EXISTE suele ser una opción mucho mejor.

Answer 3

El rendimiento debe ser el mismo; es mucho más importante tener los índices correctos y la agrupación en clústeres aplicados en sus tablas (existen algunos buenos recursos sobre ese tema).

(Editado para reflejar la pregunta actualizada)

Answer 4

El rendimiento se basa en la cantidad de datos que está ejecutando en ...

Si es menos datos alrededor de 20k. JOIN funciona mejor.

Si los datos son más como 100k + entonces IN funciona mejor.

Si no necesita los datos de la otra tabla, IN es bueno, pero siempre es mejor optar por EXISTS.

Todos estos criterios los he probado y las tablas tienen índices adecuados.

Answer 5

Esperaría que la primera consulta sea más rápida, principalmente porque tienes una equivalencia y un JOIN explícito. En mi experiencia, IN es un operador muy lento, ya que SQL normalmente lo evalúa como una serie de cláusulas WHERE separadas por "O" ( WHERE x=Y OR x=Z OR... ).

Sin embargo, al igual que con TODAS LAS COSAS SQL, su millaje puede variar. La velocidad dependerá mucho de los índices (¿tiene índices en ambas columnas de ID? Eso ayudará mucho ...) entre otras cosas.

La única forma REAL de decir con 100% de certeza que es más rápida es activar el seguimiento del rendimiento (IO Statistics es especialmente útil) y ejecutar ambos. ¡Asegúrate de borrar tu caché entre ejecuciones!

Answer 6

He probado la teoría de HLGEM al comparar los números de ''estadísticas de clientes de uso'', resulta que no existe es tan rápido que se unió a la izquierda al buscar todos los registros que no están en la tabla de la izquierda.

La belleza de SQL es sus muchas formas de escribirlo, y el rendimiento no depende únicamente de unirse o subconsultar, sino del conjunto de resultados que está buscando.

Answer 7

La consulta final incluyó azienda_id en la subconsulta corelated, pero su consulta inicial no incluyó azienda_id en la subconsulta corelated. Entonces la comparación no es lo mismo.

Answer 8

Las dos consultas pueden no ser semánticamente equivalentes. Si un empleado trabaja para más de un departamento (es posible en la empresa para la que trabajo, es cierto, esto implicaría que su tabla no está completamente normalizada), entonces la primera consulta arrojaría filas duplicadas, mientras que la segunda consulta no lo haría. Para que las consultas sean equivalentes en este caso, la palabra clave DISTINCT debería agregarse a la cláusula SELECT , lo que puede tener un impacto en el rendimiento.

Tenga en cuenta que hay una regla general de diseño que establece que una tabla debe modelar una entidad / clase o una relación entre entidades / clases, pero no ambas. Por lo tanto, le sugiero que cree una tercera tabla, digamos OrgChart , para modelar la relación entre los empleados y los departamentos.

Answer 9

Puede usar un Plan de Explicación para obtener una respuesta objetiva.

Para su problema, un filtro Exists probablemente sea el más rápido.