Generacion de inserts con Sql Management Studio

Generacion de inserts Sql Management Studio

¿Qué queremos hacer?

Lo que intentamos hacer es crearnos los comandos de insert de los registros de una tabla determinada.

¿Cómo lo hacemos?

Decidí escribir esta simple entrada porque es una funcionalidad tan útil como oculta. Sabiendo incluso que existe la posibilidad de hacerlo no es fácil encontrar donde. Así que vamos paso por paso.

1.Abrir y autenticarse al Sql Management Studio

2.Acceder a la carpeta que muestra las bases de datos

3.Ir por menú contextual a la opción de menú Generate Script como indica la siguiente imagen

4.Next a la pantalla de bienvenida del wizard

5.Selecciona la base de donde necesitas generar los scripts y obviamente luego next

6. Aca viene lo importante, la siguiente pantalla muestra una serie de opciones de configuración que no vamos a ver en detalle en este post y ademas la mayoría se puede deducir simplemente analizando el nombre. Ahora hay una propiedad que nos interesa especialmente, es la propiedad Script Data , la misma deberá ser true si queremos generar los insert.

7.Luego el wizard nos ofrece los tipos de objetos a generar, en mi humilde base de datos, creada para esta entrada solo tengo tablas, selecciono entonces esa opción.

8.El wizard esta ahora interesado en saber cual o cuales tablas quiero generar. Indico entonces la que me interesan

9.Siguiente paso nos pregunta donde queremos dejar el script, básicamente si queremos generar un archivo, si queremos poner el script en el portapapeles o si lo dejamos en una query window de nuestro sql management studio

10.Ahora sumarizado de las opciones tomadas durante la ejecución del wizard y finish para la generación del efectiva del script

11.Finalmente el script generado donde se pueden ver los comandos de script perfectamente generados.

Espero haya sido de utilidad.

SqlServer, plan de consulta y optimización (Tuning) (3)

SqlServer, plan de consulta y optimización (Tuning) (3)

El problema

En la entrada pasada vimos al sql server meter la pata groseramente al intentar estimar la cantidad de registros a recibir en una consulta con datos correlacionados.
Quedo en evidencia el problema y se dijo que dado lo grosero del error dificilmente el motor pudiera escoger un plan optimo de consulta.
El problema radica en que el sql server no entiende que existe relación entre dos columnas, hay múltiples ejemplos de datos correlacionados, edad y altura, peso y tamaño, calidad y precio etc.
La situación es que el sql server calcula la probabilidad de que se cumpla una condición múltiple basicamente como el producto de ambas, esto es correcto y es lo que aprendimos en probabilidad y estadista, la probabilidad de que ocurran dos eventos es el producto de la probabilidad de que ocurra cada uno de ellos.
Lo que también nos enseñaron es que los experimentos deben ser independiente y no deben afectarse mutuamente cosa que aquí no pasa. Difícilmente encontremos una persona con cintura estrecha, que mida menos de 1.6 metros y que pese mas de 100 kg, las tres variables están correlacionadas.
Podemos encontrar ejemplo donde el motor descarta un indice por errores de estimación en columnas correlacionadas, no es tan fácil encontrar ejemplos donde el motor escoja una estrategia de join equivocada.

Hagamos un experimento con la siguiente consulta :



declare @marca char(20)
declare @Modelo char(20)
declare @Motor char(20)
declare @Origen char(20)
declare @Ranking int


set @marca='VW'
set @Modelo='GOL'
set @Motor='VW 1.6'
set @Origen='BRASIL'
set @Ranking=1


select *
from Vehiculo as v1 inner JOIN Vehiculo as v2
On v1.IdVehiculo=v2.IdVehiculo  inner  JOIN Vehiculo as v3
On v2.IdVehiculo=v3.IdVehiculo inner join Vehiculo as v4
On v3.IdVehiculo=v4.IdVehiculo
Where v1.Marca=@marca and
v1.Modelo=@Modelo and
v1.Motor=@Motor and
v1.Origen='BRASIL' and
v1.Ranking=1

La ejecución de la consulta otorga la siguiente información,

La estrategia de join del motor es "nested loop", el motor piensa que va a recibir pocos registros y por tanto escoge esta estrategia, seguramente la mas conveniente con conjuntos de 1500 registros (Los que estima recibir).
La realidad demuestra que se reciben 200000, y el join es por IdVehiculo, indice cluster, por tanto los conjuntos están ordenados.
Se ve marcado en rojo la enorme diferencia que existe entre lo que el sql espera recibir y lo que verdaderamente recibe


La solución ( MERGE JOIN )

Existe un mecanismo de join llamado MERGE_JOIN es un mecanismo que es eficiente solo con conjuntos ordenados de determinado tamaño. Si forzáramos MERGE JOIN en conjuntos no ordenados el sql los tendría que ordenar previamente y generalmente esto no da buenos resultados.
No es este el caso donde ex profeso hemos hecho el join por el indice cluster.
Vemos también que no es la opción elegida por nuestro Sql Server, a pesar de que los conjuntos están ordenados la cantidad de registros que espera obtener no a merita el uso de Merge join.
Forzamos entonces al sql a utilizar MERGE_JOIN a sabiendas de que el motor tiene enormes problemas para predecir la cantidad de registros que cumplen la condición.


declare @marca char(20)
declare @Modelo char(20)
declare @Motor char(20)
declare @Origen char(20)
declare @Ranking int


set @marca='VW'
set @Modelo='GOL'
set @Motor='VW 1.6'
set @Origen='BRASIL'
set @Ranking=1


select *
from Vehiculo as v1 inner MERGE JOIN Vehiculo as v2
On v1.IdVehiculo=v2.IdVehiculo  inner MERGE  JOIN Vehiculo as v3
On v2.IdVehiculo=v3.IdVehiculo inner MERGE join Vehiculo as v4
On v3.IdVehiculo=v4.IdVehiculo
Where v1.Marca=@marca and
v1.Modelo=@Modelo and
v1.Motor=@Motor and
v1.Origen='BRASIL' and
v1.Ranking=1

Observemos y comparemos el rendimiento en uno y otro caso

Mas allá de esta medición puntual en varias pruebas observe que el tiempo de ejecución se reduce a la mitad, en fin los datos correlacionados hicieron desastres con las estimaciones y destruyeron los intentos del motor por obtener un buen plan de consulta.

Consideraciones finales

Para cerrar, mucho cuidado, el problema se da bajo las condiciones actuales de la tabla, pero atención  mas registros, un cambio en la distribución y correlación de los datos, o un cambio en la estructura invalidaría lo expuesto, por eso precaución al forzar el plan de consulta

SqlServer, plan de consulta y optimización (Tuning) (2)

SqlServer, plan de consulta y optimización (Tuning) (2)

Datos correlacionados

En la entrada anterior hablamos sobre cuando puede fallar el optimizador, que podría hacer fallar el path suboptimo pensado por el motor.
Se me ha hecho muy fácil engañar algunos motores y hacerles meter la pata, con sql server se complica un poco mas y las situaciones por donde podemos hacer "pinchar" al optimizador son pocas. Una de ellas es tener datos correlacionados.
Para el ejemplo de la entrega vamos a manejarnos con una tabla de vehiculos con campos marca, modelo y motor, la tabla seria la siguiente:

Tabla


CREATE TABLE [dbo].[Vehiculo](
[IdVehiculo] [int] IDENTITY(1,1) NOT NULL,
[Dominio] [char](20) NOT NULL,
[Observacion] [text] NOT NULL,
[Marca] [char](20) NOT NULL,
[Modelo] [char](20) NOT NULL,
[Motor] [char](20) NOT NULL,
 CONSTRAINT [PK_Vehiculo] PRIMARY KEY CLUSTERED
(
[IdVehiculo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

En la misma insertamos aproximadamente 100000 VW GOL POWER con motor VW 1.6, insertamos también alrededor de 50000 de otros vehículos, de otras marcas y modelos 

¿Porque es difícil que se equivoque el sql server?


El sql server maneja estadísticas para tablas con mas de 500 registros con una buena precisión, el sql server sabe en muchos casos, y por ejemplo para nuestra tabla de vehículo  cuantos FIAT, cuando VW o cuantos FORD hay, sabe también cuantos GOL POWER hay y cuantos PALIO (Las estadisticas las fue generando a medida que se ejecutaron selects con condiciones de selección aplicadas a los campos).
Si pidiéramos al sqlserver que nos retorne todos los vehículos PALIO el mismo sabría con buena precisión cuantos hay, escogería una estrategia de búsqueda adecuada.

Lo que no sabe y no puede saber es la correlación que existe entre, por ejemplo, el modelo GOL POWER y la marca VW, hay una relación estricta entre marca y modelo y sí alguien podría decir,

-El problema es que la base esta mal diseñada, deberías tener tabla de modelos y tabla de marcas, ambas relacionadas

Si es cierto para este caso, la realidad es que las bases con las que nos toca trabajar no siempre están bien diseñadas y ademas hay casos donde la relación es muy estrecha pero no definitiva.
Por ejemplo si preguntáramos Franceses que hablan Francés obtendríamos a casi todos los Franceses, pero no necesariamente a todos.
En cualquier caso si pidiéramos al SqlServer que nos retorne todos los VW que son GOL POWER es probable que lo pongamos en un aprieto a la hora de estimar la cantidad de registros que retornaría la consulta, una mala estimación de este tipo desencadena una serie de malas decisiones a la hora de elegir la estrategia de join, la estrategia de búsqueda etc.

En las ultimas versiones de sql server (2008 o superior) existe la posibilidad de explicarle al motor la situación mediante indices especiales, es una opción valida pero algo oscura.
Me da la sensación que esos indices tan específicos pueden generar problemas entre implementaciones o durante el ciclo de vida de la base de datos si no se tiene control de su existencia y de su carácter especifico.

Miren todos como se equivoca el sql server



En la siguiente imagen se puede ver la pésima estimación que hace el sqlserver .

Si examinamos el plan de consulta el mismo espera recibir algo así como 1125 filas, veamos que fue lo que efectivamente recibimos luego de ejecutar la consulta...

La consulta devolvió 100001 registros, la diferencia es importante en una tabla con 150000 registros, y como se menciono antes ya no se puede esperar mucho del plan de consulta después de semejante error.

En la próxima entrada vamos a ver como este error degenera en la perdida de rendimiento de la consulta.

SqlServer, plan de consulta y optimización (Tuning) (1)

SqlServer, plan de consulta y optimización (Tuning) (1)

De mi parte, siempre di la derecha al plan de consulta que el motor escogía, si el rendimiento no es el esperado sera porque diseñe mal la base o porque me faltan indices o directamente porque el software esta trabajando de manera incorrecta. Tengo desarrollos completos, algunos de tiempo real con bastante exigencia al motor donde la cantidad de planes de consulta forzados en todo los módulos del software es 0.

Hace mucho años trabajando para una compañía bastante grande observaba horrorizado como se había instalado la cultura de crear la consulta e indicar porque indice ir.
Meses después tuve mi revancha cuando nos mandaron a todos a hacer un curso de tuning de sql, y si no fue lo primero, lo segundo que dijo el instructor fue “No fuercen el plan de consulta, es muy probable que no tengan el conocimiento y la cantidad de información necesaria para hacerlo”
Los mismo dice microsoft en su msdn, traducción mediante “Típicamente sqlserver encuentra el mejor plan posible, no lo cambie salvo que sea un experto programador o experto administrador de base de datos”.

Probablemente todos nos sentimos expertos programadores, sea así o no, con eso no alcanza. Ademas de la experiencia se necesita conocimiento del modelo, de los indices y de la distribución y naturaleza de la información, en la mayoría de los casos conocer todo esto es poco probable y casualmente el sqlServer (Correctamente configurado) en sus estadísticas tiene esta información con un nivel de confiabilidad superior al 80%.

Entonces si no hay posibilidades de mejora y el motor es perfecto ¿Por que existen los hints?
Bueno de hecho el motor sí se equivoca, poco, pero se equivoca con especial énfasis en las siguientes situaciones:

1. Datos correlacionados
2. Poda por heuristica
3. Bug optimizador
4. Estadísticas no actualizadas

¿Entonces modifico o no el plan de consulta?

Entiendo que modificar el plan de consulta es la solución mas tentadora a un problema de rendimiento, generalmente parece ser también la solución menos probable hasta puede ofrecer mejoras marginales en muchos casos.
En posteriores entradas voy a intentar desgranar situaciones donde malos diseños, bugs del optimizador, datos correlacionados, podas o malas estadísticas puedan hacer que el sql se equivoque groseramente.