SQL Server Parameter Sniffing: error timeout con DataAdapter.Fill en .NET

SQL Server Parameter Sniffing: error timeout con DataAdapter.Fill en .NET

Si estás invocando un procedimiento almacenado en SQL Server al que le pasas uno o varios parámetros y observas que el tiempo de ejecución es mucho mayor que si ejecutas manualmente la query, probablemente estés ante un caso de Parameter Sniffing. Este tipo de problema sucede cuando el gestor de la BD intenta reutilizar un plan de ejecución anterior recuperándolo de la caché aplicando  los nuevos valores de los parámetros recibidos.

El plan de ejecución de un procedimiento almacenado en SQL Server no se genera al compilarlo, si no que se crea la primera vez que se ejecuta el SP y después se reutiliza en las sucesivas llamadas. Esto puede dar lugar a ejecuciones con una demora de tiempo muy elevada, ya que puede ser que el plan de ejecución de la primera consulta no sea tan óptimo para otras.

La forma de solucionarlo es utilizar variables locales en lugar de los propios parámetros en la query del procedimiento almacenado.  Creamos una nueva variable del mismo tipo y tamaño que cada parámetro, y le asignamos su valor. Por ejemplo:

CREATE PROCEDURE SP_N4GASH

@fecha Date,

@categoria int

AS

DECLARE @nuevaFecha Date

DECLARE @nuevaCategoria int

SET @nuevaFecha =@fecha

SET @nuevaCategoria=@categoria

SELECT […]  FROM […]

WHERE aud_fecha = @nuevaFecha

AND catID = @nuevaCategoria

GO

En mi caso me apareció este error desde una aplicación C# desde la que invocando a un procedimiento almacenado y metiéndolo en un DataTable mediante DataAdaptar.Fill me arrojaba un error de TimeOut. La solución rápida y mala de este error desde la aplicación web es asignar varios segundos al parámetro timeout del SQLCommand, y aunque esto evita que salte el error y se detenga el proceso, no soluciona el problema de la demora de tiempo, donde a veces se presentan diferencias de más de una hora respecto a la solución desde T-SQL.

Sin Comentarios

Error MySQL: LOCK TABLES WRITE al configurar Joomla 1.6

Cómo solucionar el error de MySQL LOCK TABLES WRITE de Joomla 1.6 al intentar configurar categorías, secciones o artículos del CMS.

Con el nuevo plan de actualizaciones el equipo de Joomla se ha comprometido a liberar una nueva versión del CMS cada semestre. Este artículo lo voy a escribir utilizando la versión 1.6, aunque desde hace unos pocos días se ha liberado la versión 1.7. Es muy probable que el error que voy a comentar pueda aparecer tanto en la 1.6, como en la 1.7 y en futuras versiones, ya que es un error sobre la BBDD, no del software de Joomla.

Una vez hemos instalado Joomla, configurado el servidor de BBDD y eliminado la carpeta de Installation podemos empezar a meter contenido en nuestra web, organizarlo por categorías y secciones, instalar componentes y módulos, modificar el theme, etc. La mayoría de estas tareas necesitan hacer operaciones con la base de datos, normalmente añadir objetos nuevos, añadir columnas, insertar registros, borrarlos, etc. En mi caso, a la hora de intentar crear una nueva categoría el sistema me arrojaba el siguiente error:

Save failed with the following error: Access denied for user ‘u_user’@’%’ to database ‘base_de_datos’ SQL=LOCK TABLES `jos_categories` WRITE

El error es fácil de detectar, hay problemas de permisos para el usuario u_user que nos impiden realizar la transacción requerida. En primera instancia tenemos que revisar los GRANTS que tiene el usuario ¿puede borrar, insertar y modificar?. Por si acaso, ejecutamos la siguiente query:

GRANT select, update, insert ON base_de_datos.* to u_user@servidor;

Si el error persiste, entonces la solución definitiva será ejecutar la siguiente query:

GRANT lock tables ON base_de_datos.* to u_user@servidor;

O bien:

UNLOCK TABLES;

El comando LOCK en MySQL permite bloquear una tabla con permisos de sólo lectura. Si a la sentencia le añadimos al final un WRITE, dejándola así, LOCK TABLES tabla WRITE; , estaremos otorgando al usuario que la ha bloqueado el permiso para leer y escribir sobre ella, impidiéndoselo al resto de usuarios. Para eliminar cualquier bloqueo de tablas se debe utilizar el comando UNLOCK TABLES;.

El GRANT LOCK permite a un usuario acceder a las tablas bloqueadas indicadas en la sentencia, siempre y cuando el usuario también tenga el permiso de lectura SELECT sobre éstas, por eso, al ejecutar esta query en el error mencionado de Joomla, lo que estaremos permitiendo es la ejecución de cualquier DML sobre la tabla jos_categories al usuario u_user. Para más información sobre el comando LOCK TABLES  de MySQL puedes ver la documentación oficial.

Sin Comentarios

Diferencias entre una Vista y una Vista Materializada en Oracle


Una Vista es un objeto de Oracle que al consultarlo ejecuta por detrás una query y nos devuelve el resultado (donde la query puede acceder a tablas, otras vistas, utilizar funciones o procedimientos, etc.). Cada vez que accedemos a la vista, la query se ejecuta y nos devuelve la información que en ese momento exista en el origen.

Por contra, una Vista Materializada (Materialized Views) es otro tipo de objeto de Oracle que aunque técnicamente es lo mismo que una Vista (ejecuta una consulta sql), lo que hace es almacenar físicamente en caché el resultado de ejecutar una query en un determinado momento, de forma que cada vez que consultemos la Vista Materializada lo que vamos a recuperar es lo que había en el origen en el momento en el que se creó o se refrescó la VM.

Vista materializada y vista convencional en Bases de Datos Oracle

La sintaxis para crear una Vista materializada es:

CREATE MATERIALIZED VIEW nueva_vista_materializada
[TABLESPACE nuestro_tablespace]
[BUILD {IMMEDIATE | DEFERRED}]
[REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicial] NEXT intervalo_tiempo } |
{COMPLETE | FAST | FORCE | NEVER} ]
[{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT tabla1.campo_a, tabla2.campo_b
FROM tabla1 , tabla2
WHERE tabla1.campo_a = tabla2.campo_a
AND …

Donde podemos especificar de qué forma queremos que se recuperen los datos: de forma inmediata o en otro momento:

  • BUILD IMMEDIATE: El resultado se almacena al ejecutar la query
  • BUILD DEFERRED: Sólo se crea la definición, el resultado se almacenará más adelante. Para ello podemos utilizar cuando queramos llenarla la función REFRESH del paquete de Oracle DBMS_MVIEW.

También podemos especificar cada cuánto tiempo o cuándo queremos que se actualicen los datos de la vista:

  • REFRESH ON COMMIT: los datos se actualizan cada vez que se haga COMMIT sobre los objetos fuente.
  • REFRESH ON DEMAND: sólo se actualizarán cuando se haga manualmente a través de las funciones REFRESH de Oracle (más adelante se explica cuáles son y cómo invocarlas).
  • REFRESH [START WITH fecha_inicial] NEXT intervalo_tiempo: con esta sentencia podemos establecer una actualización periódica de la vista. La fecha_inicial puede definirse como sysdate y el intervalo_tiempo cada cuanto tiempo queremos realizar la tarea (podemos incrementar numéricamente el sysdate)

Y especificar si permitimos a Oracle o no reescribir las queries cuando en una consulta se intente acceder a las tablas origen, de forma que por detrás el duende de Oracle ataque directamente a las vistas materializadas, cuyo acceso y recuperación de datos es más rápido, sin que el usuario necesite hacer nada. Esto sólo lo hará el Optimizador de Oracle si la consulta lo permite (que las tablas a las que va a buscar la información coinciden con las tablas origen de la vista materializada).

  • ENABLE QUERY REWRITE: Oracle puede reescribir las queries
  • DISABLE QUERY REWRITE: lo contrario

La ventaja de este tipo de objeto la encontramos cuando la query asociada es muy compleja, tiene numerosos joins y se utiliza con frecuencia, ya que nos permite mejorar el rendimiento de la SQL al tenerla almacenada en memoria y ejecutarse una sóla vez. Sin embargo, el principal hándicap es que nos vemos obligados a mantenerla actualizada de forma regular para no perder calidad en los datos. Para ello podemos definir en el script de creación de la Vista Materializada el tipo de actualización que tendrá al ejecutar los métodos propios de Oracle para refrescarla. Existen los siguientes tipos de actualización para una Vista materializada

  • FAST: Es la opción más recomendada por su rendimiento. Es una actualización incremental, es decir, sólo actualiza los registros que hayan cambiado. Para que esta opción funcione correctamente necesitamos generar estadísticas de la vista para que Oracle esté al tanto de los cambios. La forma de hacerlo es creando un log de la vista materializada sobre la PK de cada tabla origen.

    CREATE MATERIALIZED VIEW LOG ON tabla_origen
    WITH PRIMARY KEY
    INCLUDING NEW VALUES;

  • COMPLETE: regenera por completo el resultado al borrar y volver a ejecutar de nuevo la query.
  • FORCE: Es el valor por defecto. En caso de que se pueda, se ejecutará el FAST; si no, el COMPLETE.
  • NEVER: nunca se refresca la vista.

Si modificamos el origen y queremos que éste se refleje en la Vista materializada, tenemos que invocar a una función del paquete DBMS_MVIEW de Oracle que se encarga de actualizar los datos. Tenemos dos a nuestra disposición:

  • DBMS_MVIEW.REFRESH: con este método pasamos por parámetro la Vista Materializada para que se actualice:

    begin

    DBMS_MVIEW.REFRESH(‘TABLA_EMPLEADOS’);

    end;

  • DBMS_MVIEW.REFRESH_DEPENDENT: mientras que con este lo que le pasamos por parámetro son todas las tablas origen de las que depende la vista:

    begin

    DBMS_MVIEW.REFRESH_DEPENDENT(‘TABLA_ORIGEN_1’, ‘TABLA_ORIGEN_2’, ‘TABLA_ORIGEN_3’);

    end;

6 Comentarios

Cómo solucionar el error de MySQL #1005 – Can’t create table (errno: 121)


Ayer me apareció este error al tratar de ejecutar un DDL en una base de datos MySQL. El script estaba compuesto por varias sentencias de creación de tablas con primary keys, foreign keys e índices.

Este error aparece cuando estamos duplicando información de algún objeto de la base de datos en una sentencia CREATE TABLE . Una de las razones por la que se produce puede ser que la tabla ya exista en el diccionario de datos interno de la BBDD por algún proceso anterior y no se haya limpiado correctamente. Otro motivo, que es el que a mí me estaba provocando el error, es un conflicto de nombres entre foreign keys, ya que los nombres de éstas también deben de ser únicos en la BBDD, al igual que los nombres de las tablas, índices, vistas, etc.

La solución para mi caso, por tanto, es personalizar el nombre de cada Foreign Key. El ejemplo expuesto a continuación lo he tomado de mi script. Tenemos una tabla de hechos donde almacenamos la información de tiendas (t_tiendas) y dos tablas auxiliares donde vamos a guardar opiniones de cada tienda e imágenes de éstas (t_tiendas_img y t_tiendas_opn).  Estas dos tablas tienen como foreign key el id_tienda de la fact table y el error que cometí fue denominar a ambas con el mismo nombre. Para corregirlo, simplemente asignamos un nombre distinto a cada foreign key (id_tienda_img e id_tienda_opn).

CREATE  TABLE IF NOT EXISTS `t_tiendas_opn` (
`id_tienda` INT NOT NULL ,
`txt_opinion` MEDIUMTEXT NOT NULL ,
`valoracion` INT NOT NULL ,
PRIMARY KEY (`id_tienda`) ,
CONSTRAINT `id_tienda_opn`
FOREIGN KEY (`id_tienda` )
REFERENCES `t_tiendas` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)

CREATE  TABLE IF NOT EXISTS `t_tiendas_img` (
`id_tienda` INT NOT NULL ,
`path_img` VARCHAR(120) NOT NULL ,
PRIMARY KEY (`id_tienda`) ,
CONSTRAINT `id_tienda_img`
FOREIGN KEY (`id_tienda` )
REFERENCES `t_tiendas` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)

20 Comentarios