with Recompile

Desde hacia días detectaba un procedimiento con un extraño comportamiento, el rendimiento de la consulta era inferior a la ejecución de la consulta directamente. ¿Como puede ser esto?

Como siempre en estos casos recurrí a los foros de internet, rápidamente obtuve una respuesta "parameter sniffing".

Resulta que una de las subconsultas del procedimiento se obtenía de una función que devolvía una tabla, y en este caso no se calcula correctamente el plan de ejecución. Asi que si no se recompila el procedimiento el rendimiento es deficiente, para subsanar este problema se puede añadir la clausula with recompile, obligando a sql-server a preparar cada vez un nuevo plan de ejecución.

El uso de la clausula es como sigue:

create proc dbo.prueba
@id int
with recompile
as
select * from tabla where id = @id

Quitar hora a la fecha en tsql

Función para eliminar la parte de hora en una fecha.

ALTER FUNCTION [dbo].[f_fechaCadena] (@fecha datetime)
returns varchar(15)
as
begin
declare @fechaStr varchar(15)
set @fechastr = CONVERT(VARCHAR,@FECHA,103)
return @fechaStr
end

Convert con la clausula 103, elimina la parte de hora de una fecha en sql-server.

With para tablas temporales

With consulta1 (id, codigo) as (select id,codigo from tabla where campo2 = 'x')

select * from consulta1

resultados:
ID CODIGO
--- ----------
1 32323
2 CODIGO 2

Una cosa curiosa, es que antes de esta instrucción la instruccion previa debe terminar con ';'.

Parametros multiples con tsql (xml)

Como varias filas a sql-server para que hacer inserciones multiples, o transaccionales en un mismo procedimiento.. pues con xml.

BEGIN

DECLARE @HDOC INT -- Puntero a la estructura xml

DECLARE @XML xml -- Xml en 2005, para 2000 seria una cadena de caracteres

SET @XML = 'xml' -- el xml de ejemplo

EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT, @XML -- preparamos el documento xml para procesarlo

SELECT * FROM OPENXML(@HDOC,'/ArrayOfInt/int',3) WITH (xint int 'text()')
-- lo procesamos fijarse en la clausula with donde se especifica la estructura de la tabla de destino

EXEC SP_XML_REMOVEDOCUMENT @HDOCEND -- Liberamos de memoria la preparación del documento

CASE EN TSQL

Case se utiliza para devolver un valor condicionalmente dependiendo de otro valor o un calculo.

Por ejemplo imajinaros que queremos que en una consulta aparezca un semaforo dependiendo de una variable.
declare @campo1 int

set @campo1 = 2
select case @campo1 when 1 then 'verde' when 2 then 'rojo' else 'naranja' end

Uso de cursores

Hace tiempo leí en un artículo una frase referente a cursores que me quedo grabada. "Todo lo que se pueda hacer con cursores se puede hacer con sql" y la verdad es ha sido mi guión a la hora de desarrollar nuevos procedimientos.

He de comentar que realmente he comprobado dicha frase, y es más también puedo decir que la diferencia entre usar cursores y es mucha, Sobre todo en cuanto a tiempo de proceso hablamos.

Pero menos rollo y vamos al tema:

• Primero lo declaramos, le daremos un nombre (para el ejemplo lo llamares cr1) y prepararemos la select que usaremos para seleccionar la información que queremos. DECLARE CR1 CURSOR LOCAL FOR SELECT CAMPO1,CAMPO2 FROM TABLA
• Fijaos que aunque usamos DECLARE, el nombre de la variable no lleva la arroba (@).
• Deberemos declarar las variables que usaremos para almacenar el valor de los campos. DECLARE @CAMPO1, @CAMPO2 INT
• Se declararan tantas variables como campos tengamos en la select.
• Ya podemos abrir el cursor OPEN CR1
• Y Movemos el cursor al primer registro, los valores los almacenaremos en las variables declaradas y el orden de los campos debe ser el mismo que en la select.
• FETCH NEXT FROM RS INTO @CAMPO1, @CAMPO2
• Ya podemos empezar el bucle de recorrido del cursor:

WHILE @@FETCH_STATUS = 0

BEGIN

PROCESOS ----

FETCH NEXT FROM CR1 INTO @CAMPO1, @CAMPO2

END

• No se debe olvidar de cerrar el cursor (CLOSE CR1) y liberar de la memoria el cursor (DEALLOCATE CR1).

El tema de cursores se puede complicar más, por ejemplo el @@fetch_status puede tener diferentes valores y el cursor se puede mover adelante y atrás. Pero esto lo explicare en otro artículo. ;)

Matrices en tsql

Como siempre las dudas surgen trabajando y se me planteo la necesidad de implementar una tabla temporal, tsql incorpora dos tipos de tablas. Una que es especifica del procedimiento en curso y otra que se genera en base de datos y se puede compartir en los diferentes procedimientos de los que dispongamos.

La utilización de cualquiera de estas dos opciones, dependerá de la necesidades del desarrollador.

No soy nada partidario de hacer tablas temporales físicas, así que de momento no os lo explicare ;) ya se... es que tengo mis manías.

Así que os explicare las tablas temporales, cuyo ciclo de vida solo corresponde a el procedimiento en curso.

1. Primero declararemos la tabla, se declara igual que otras variables pero definiendo los campos de dicha tabla y se hará de la siguiente forma DECLARE @PRUEBA TABLA (CAMPO1 INT, CAMPO2 VARCHAR(20))
2. Se usa exactamente igual que cualquier tabla fisica de base de datos, podemos hacer inserts, updates o deletes ej: INSERT INTO @PRUEBA (CAMPO1, CAMPO2) VALUES(1,'CAMPO') o SELECT * FROM @PRUEBA
3. No es necesario destruir la tabla, como he comentado el ciclo de vida solo corresponde al procedimiento en curso.

Bases de tsql

Llevo varios ya muchos años trabajando en tsql y plsql, y en este caso como en muchos otros comparar no tiene sentido. Si Oracle y Sql Server son dos bases de datos, pero su usabilidad es muy diferente, jamas implementaríamos un oracle en un aplicativo de cinco usuarios y tampoco lo haríamos con Sql para una gran cantidad de usuarios/datos.

Así mismo tsql es un gran desconocido, desconozco el porque pero no es habitual que se usen procedimientos en tsql, los desarrolladores que usan sql server harcodean las consultas dentro del código fuente (con el riesgo de seguridad que esto implica). Mientras que trabajar de la misma forma en oracle es casi imposible de ver.

Ya solo por pertenecer a Microsoft, sql server recibe criticas y comentarios discriminatorios. Entiendo la manía que en el mundo del desarrollo, se tiene a Microsoft pero bueno es lo que hay, el 99% de los ordenadores del mundo llevan instalado su sistema operativo, así que tan malo no sera ( al menos a nivel comercial). En mis años de experiencia con sql-server he quedado muy satisfecho con los resultados obtenidos y puedo decir que permite desarrollar software de una forma muy rápida y eficiente pese a quien pese.

En este blog iré insertando funciones y trucos aprendidos en mis años de trabajo , que espero os sean de utilidad, así mismo espero opiniones y comentarios así como aportaciones.