sql server - the - ¿Cuáles son las diferencias entre las transformaciones Merge Join y Lookup en SSIS?

merge join ssis (7)

Hola, soy nuevo en los paquetes de SSIS y estoy escribiendo un paquete y leyendo sobre ellos al mismo tiempo.

Necesito convertir un DTS en un paquete SSIS y necesito unirme a dos fuentes de diferentes bases de datos y me pregunto ¿cuál es el mejor enfoque, usar una búsqueda o una fusión?

En la superficie, parecen muy similares. El ''Merge Join'' requiere que los datos sean ordenados de antemano mientras que ''Lookup'' no requiere esto. Cualquier consejo sería muy útil. Gracias.

La búsqueda es similar a la combinación izquierda en el componente Merge Join. Merge puede hacer otros tipos de uniones, pero si esto es lo que quiere, la diferencia es principalmente en rendimiento y conveniencia.

Sus características de rendimiento pueden ser muy diferentes dependiendo de la cantidad relativa de datos a buscar (entrada al componente de búsqueda) y la cantidad de datos a los que se hace referencia (caché de búsqueda o tamaño de fuente de datos de búsqueda).

Por ejemplo, si solo necesita buscar 10 filas, pero el conjunto de datos al que se hace referencia es de 10 millones de filas: la búsqueda mediante el uso de memoria parcial o sin memoria caché será más rápida, ya que solo obtendrá 10 registros en lugar de 10 millones. Si necesita buscar 10 millones de filas, y el conjunto de datos al que se hace referencia es de 10 filas, completamente almacenado en caché. La búsqueda es probablemente más rápida (a menos que esas 10 millones de filas ya estén ordenadas de todos modos y puede probar Merge Join). Si ambos conjuntos de datos son grandes (especialmente si hay más RAM disponible) o el más grande está ordenado, la combinación podría ser una mejor opción.

Merge Join le permite unirse a múltiples columnas basadas en uno o más criterios, mientras que una búsqueda es más limitada, ya que solo obtiene uno o más valores en función de alguna información de columna coincidente: la consulta de búsqueda se ejecutará para cada uno valor en su fuente de datos (aunque SSIS guardará en caché la fuente de datos si puede).

Realmente depende de lo que contengan sus dos fuentes de datos y cómo desea que su fuente final se encargue de la fusión. ¿Podría proporcionar más detalles sobre los esquemas en su paquete DTS?

Otra cosa a considerar es el rendimiento. Si se usa incorrectamente, cada uno podría ser más lento que el otro, pero de nuevo, dependerá de la cantidad de datos que tenga y sus esquemas de fuente de datos.

Sé que esta es una vieja pregunta, pero un punto crítico que creo que no fue cubierto por las respuestas dadas es que debido a que la unión de fusión está fusionando dos flujos de datos, puede combinar datos de cualquier fuente. Mientras que con la búsqueda, una fuente de datos debe mantenerse en un OLE DB.

Sugeriré una tercera alternativa para considerar. Su OLE DBSource podría contener una consulta en lugar de una tabla y usted podría hacer la unión allí. Esto no es bueno en todas las situaciones, pero cuando puede usarlo, entonces no tiene que ordenar de antemano.

Una combinación de fusión está diseñada para producir resultados similares a cómo funcionan las JOIN en SQL. El componente de búsqueda no funciona como un JOIN de SQL. Aquí hay un ejemplo donde los resultados serían diferentes.

Si tiene una relación de uno a varios entre la entrada 1 (p. Ej., Facturas) y la entrada 2 (p. Ej., Facturas de factura), quiere que los resultados de la combinación de estas dos entradas incluyan una o más filas para una sola factura .

Con un Merge Join obtendrás el resultado deseado. Con una búsqueda, donde la entrada 2 es la fuente de búsqueda, la salida será una fila por factura, sin importar cuántas filas existan en la entrada 2. No recuerdo qué fila de la entrada 2 vendrían los datos, pero I '' Estoy bastante seguro de que recibirá una advertencia de datos duplicados, al menos.

Entonces, cada componente tiene su propio rol en SSIS.

hay 2 diferencias:

Clasificación:
- una unión combinada requiere que ambas entradas se clasifiquen de la misma manera
- búsqueda no requiere ninguna entrada para ser ordenada.
Carga de consulta de base de datos:
- una combinación de fusión no se refiere a la base de datos, solo los 2 flujos de entrada (aunque los datos de referencia generalmente están en la forma de ''seleccionar * del orden de la tabla mediante la combinación de critera'')
- searchup emitirá 1 consulta para cada valor (distinto, si almacena en caché) que se le pide que se una. Esto rápidamente se vuelve más caro que la selección anterior.

Esto lleva a: si no es un esfuerzo producir una lista ordenada, y desea más de aproximadamente el 1% de las filas (las selecciones de una sola fila son ~ 100 veces el costo de la misma fila durante la transmisión) (no desea ordenar una mesa de 10 millones de filas en la memoria ...), luego combinar unirse es el camino a seguir.

Si solo espera una pequeña cantidad de coincidencias (se buscan valores distintos, cuando el almacenamiento en caché está habilitado), la búsqueda es mejor.

Para mí, la compensación entre los dos viene entre 10k y 100k filas que necesitan ser buscadas.

El que es más rápido dependerá de

el número total de filas a procesar (si la tabla es residente en memoria, una clase de datos para combinar es barata)
la cantidad de búsquedas duplicadas esperadas (gastos generales altos por fila de búsqueda)
si puede seleccionar datos ordenados (nota, los ordenamientos de texto son influenciados por la intercalación de código, así que tenga cuidado de que lo que sql considere ordenado sea también lo que ssis considere ordenado)
qué porcentaje de toda la mesa buscará. (la fusión requerirá seleccionar cada fila, la búsqueda es mejor si solo tiene algunas filas en un lado)
el ancho de una fila (filas por página puede influir fuertemente en el costo de hacer búsquedas individuales frente a un escaneo) (filas estrechas -> más preferencia para fusionar)
el orden de los datos en el disco (fácil de producir salida clasificada, prefiera fusionar, si puede organizar las búsquedas en orden de disco físico, las búsquedas son menos costosas debido a menos errores de caché)
latencia de red entre el servidor ssis y el destino (latencia más grande -> prefiera fusión)
cuánto esfuerzo de codificación desea gastar (la fusión es un poco más compleja de escribir)
la intercalación de los datos de entrada: fusión de SSIS tiene ideas extrañas sobre la clasificación de cadenas de texto que contienen caracteres no alfanuméricos, pero no son nvarchar. (Esto va a la clasificación, y obtener sql para emitir un tipo que sis feliz de fusionar es difícil)

La captura de pantalla n. ° 1 muestra algunos puntos para distinguir entre la Merge Join transformation y la Lookup transformation .

En cuanto a la búsqueda:

Si desea encontrar filas que coincidan en la fuente 2 en función de la entrada de la fuente 1, y si sabe que solo habrá una coincidencia para cada fila de entrada, le sugiero que utilice la operación de búsqueda. Un ejemplo sería su tabla OrderDetails y desea encontrar el Order Id y el Customer Number concuerden, y luego Buscar es una mejor opción.

Con respecto a Merge Join:

Si desea realizar uniones como buscar todas las direcciones (Casa, Trabajo, Otro) de la tabla de Address para un Cliente dado en la tabla Customer , entonces tiene que ir con Fusionar combinación porque el cliente puede tener una o más direcciones asociadas a ellas.

Un ejemplo para comparar:

Aquí hay un escenario para demostrar las diferencias de rendimiento entre Merge Join y Lookup . Los datos utilizados aquí son uno a uno, que es el único escenario común entre ellos para comparar.

Tengo tres tablas llamadas dbo.ItemPriceInfo , dbo.ItemDiscountInfo y dbo.ItemAmount . Crear scripts para estas tablas se proporcionan en la sección de scripts SQL.
Las tablas dbo.ItemPriceInfo y dbo.ItemDiscountInfo tienen 13,349,729 filas. Ambas tablas tienen el ItemNumber como la columna común. ItemPriceInfo tiene información de precio y ItemDiscountInfo tiene información de descuento. La captura de pantalla n. ° 2 muestra el recuento de filas en cada una de estas tablas. La captura de pantalla n. ° 3 muestra las 6 filas superiores para dar una idea sobre los datos presentes en las tablas.
Creé dos paquetes SSIS para comparar el rendimiento de las transformaciones Merge Join y Lookup. Ambos paquetes deben tomar la información de las tablas dbo.ItemPriceInfo y dbo.ItemDiscountInfo , calcular la cantidad total y guardarla en la tabla dbo.ItemAmount .
El primer paquete usó la transformación Merge Join y, dentro, utilizó INNER JOIN para combinar los datos. Las capturas de pantalla n. ° 4 y n. ° 5 muestran la ejecución del paquete de muestra y la duración de la ejecución. Tomó 05 minutos 14 segundos 719 milisegundos para ejecutar el paquete basado en la transformación Merge Join.
El segundo paquete utilizó la transformación de Lookup con caché completo (que es la configuración predeterminada). las creenshots # 6 y # 7 muestran la ejecución del paquete de muestra y la duración de la ejecución. Tomó 11 minutos 03 segundos 610 milisegundos para ejecutar el paquete basado en la transformación de búsqueda. Puede encontrar el mensaje de advertencia Información: The buffer manager has allocated nnnnn bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed. Aquí hay un link que habla sobre cómo calcular el tamaño de la caché de búsqueda. Durante la ejecución de este paquete, aunque la tarea de flujo de datos se completó más rápido, la limpieza de la interconexión tomó mucho tiempo.
Esto no significa que la transformación de búsqueda es mala. Es solo que debe ser usado sabiamente. Lo uso con bastante frecuencia en mis proyectos, pero una vez más no trato con más de 10 millones de filas para la búsqueda diaria. Por lo general, mis trabajos manejan entre 2 y 3 millones de filas y para eso el rendimiento es realmente bueno. Hasta 10 millones de filas, ambas tuvieron el mismo rendimiento. La mayor parte del tiempo lo que noté es que el cuello de botella resulta ser el componente de destino en lugar de las transformaciones. Puedes superar eso teniendo múltiples destinos. Here hay un ejemplo que muestra la implementación de múltiples destinos.
La captura de pantalla n. ° 8 muestra el recuento de registros en las tres tablas. La captura de pantalla n. ° 9 muestra los 6 registros principales en cada una de las tablas.

Espero que ayude.

Scripts SQL:

CREATE TABLE [dbo].[ItemAmount]( [Id] [int] IDENTITY(1,1) NOT NULL, [ItemNumber] [nvarchar](30) NOT NULL, [Price] [numeric](18, 2) NOT NULL, [Discount] [numeric](18, 2) NOT NULL, [CalculatedAmount] [numeric](18, 2) NOT NULL, CONSTRAINT [PK_ItemAmount] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemDiscountInfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [ItemNumber] [nvarchar](30) NOT NULL, [Discount] [numeric](18, 2) NOT NULL, CONSTRAINT [PK_ItemDiscountInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemPriceInfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [ItemNumber] [nvarchar](30) NOT NULL, [Price] [numeric](18, 2) NOT NULL, CONSTRAINT [PK_ItemPriceInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO

Captura de pantalla n. ° 1:

Captura de pantalla n. ° 2:

Captura de pantalla n. ° 3:

Captura de pantalla n. ° 4:

Captura de pantalla n. ° 5:

Captura de pantalla n.º 6:

Captura de pantalla n. ° 7:

Captura de pantalla n. ° 8:

Captura de pantalla n. ° 9: