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.

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.

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.

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;

En alguna ocasión hemos tenido que hacer una consulta en Oracle sobre una tabla cuyo nombre no recordamos con exactitud y nos hemos tenido que pegar con la documentación del proyecto (si existe, claro) hasta encontrarla. Con el IDE TOAD for Oracle podemos usar el Schema Browser para encontrar rápidamente a través de los filtros de metadatos la tabla en cuestión, sin embargo, si estamos con cualquier otra herramienta que no disponga de un buscador para el diccionario de datos de  la base de datos, la única forma de buscar entre todos los objetos del sistema es utilizando alguna de las vistas de metadatos propias de ORACLE del esquema SYSTEM (SYS), que almacenan toda la información sobre los objetos de la BBDD.

Existe una vista por cada nivel de acceso en función del usuario con el que nos hemos conectado, por ejemplo, las vistas que agrupan toda la información sobre los índices serían DBA_INDEXES, ALL_INDEXES y USER_INDEXES.

• DBA_: contiene todos los objetos de la instancia a la que nos hemos conectado. Es necesario tener los privilegios suficientes.
• ALL_: contiene todos los objetos accesibles por el usuario con el que nos hemos conectado, tanto los propios como sobre los que se tiene acceso (ya sean de otros usuarios o esquemas)
• USER_: sólo contiene los objetos que pertenecen al usuario.

Estas vistas de metadatos nos permiten buscar rápidamente cualquier objeto de la base de datos.

• SYS.DBA_TABLES ó SYS.DBA_ALL_TABLES (Tablas)
• SYS.DBA_VIEWS (Vistas)
• SYS.DBA_INDEXES (Índices)
• SYS.DBA_TABLESPACES (Tablespaces)
• SYS.DBA_USERS (Usuarios)
• SYS.DBA_PROCEDURES (Procedures)
• SYS.DBA_SEQUENCES (Secuencias)
• SYS.DBA_TRIGGERS (Triggers o Disparadores)
• …

Una consulta de ejemplo con la que obtendríamos el nombre completo de una tabla sería:

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM SYS.DBA_TABLES
WHERE TABLE_NAME LIKE ‘%EMPLEADOS%’;

En relación a las tablas DBA_TABLES y DBA_ALL_TABLES, mencionar que la diferencia radica en que la primera sólo contiene información de las tablas, mientras que la segunda incluye también metadatos de los Object Tables, que en Oracle son tipos de datos definidos por el propio usuario (son estructuras o arrays que pueden incorporarse como columnas de una tabla o incluso permiten crear nuevas tablas instanciando estos objetos; para crearlos se utiliza la sentencia CREATE TYPE < nombre > AS OBJECT).

SYS.ALL_TAB_COLS: buscar tablas por sus columnas en Oracle

Otra tabla del system que nos puede ayudar a la hora de buscar información en los metadatos de Oracle es  SYS.DBA_TAB_COLS. Esta tabla contiene las columnas de cada objeto table de nuestra base de datos, de forma que buscando por el campo COLUMN_NAME podremos saber en qué tablas se encuentra (útil cuando sabemos el campo de destino y queremos saber saber de donde viene o por donde pasa).

Por ejemplo, para obtener todas las tablas del sistema que incluyen una columna en concreto podríamos utilizar esta query:

SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM SYS.ALL_TAB_COLS
WHERE COLUMN_NAME = ‘FECHA_ALTA’;

SYS.DBA_TAB_PRIVS: Ver los permisos o privilegios de una tabla en Oracle

La forma más rápida de ver los permisos de una o varias tablas es utilizando la vista SYS.DBA_TAB_PRIVS. Filtrando por el nombre de la tabla recuperaremos una tupla con cada permiso otorgado (select, alter, insert, update, delete, execute, etc.), el rol al que se le ha asignado y el usuario propietario. Aunque la nomenclatura de la vista incluye _TAB, se pueden consultar los permisos de cualquier objeto de la BBDD: tablas, vistas, secuencias, etc.

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM SYS.DBA_TAB_PRIVS
WHERE TABLE_NAME LIKE ‘%EMPLEADOS%’

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 salta 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)

Desde hace años Microsoft ofrece de forma gratuita la descarga de su suite de aplicaciones de desarrollo para el entorno .NET, denominadas Express, ya que no ofrecen completamente todas las herramientas disponibles, para ello tendríamos que adquirir la versión Professional.

Con motivo de un curso sobre .NET he tenido que descargar e instalar Visual Studio 2010 Express para desarrollar aplicaciones de consola y Windows Forms, además,  uno de los módulos aborda la conexión a bases de datos y la gestión de estos  a través de SQL Server 2008 R2. Para todas las operaciones de los datos el tutor utiliza una base de datos de ejemplo que puede descargarse gratuitamente del MSDN: AdventureWorks.

Tras varios intentos, al final he logrado instalar correctamente la BBDD para poder utilizarla en los ejercicios propuestos. Por si en alguna ocasión os encontráis con este problema, os comento brevemente cómo resolverlo en unos pocos pasos.

En primer lugar, tenemos que instalar Microsoft SQL Server 2008 R2. Una vez instalado, es necesario hacer una instalación del gestor de la BBDD en nuestra máquina. Para ello, debemos ejecutar la aplicación SQL Server Installation Center, pulsar sobre la pestaña Installation y finalmente seleccionar la primera opción New Installation or add features to an existing installation. El proceso de instalación es sumamente sencillo, la única complicación con la que nos podemos encontrar es no tener iniciado el servicio SQL Server, que podemos activar desde el panel de control de Windows, o accediendo desde el menú de inicio en Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager.

Una vez terminado, descargamos e instalamos la database de ejemplo AdventureWorks. La instalación descomprime varios modelos de la BBDD en nuestra máquina:

C:\Archivos de programa\Microsoft SQL Server\100\Tools\Samples

La que nos interesa es AdventureWorks OLTP. Entramos en la carpeta que lleva este nombre y editamos el fichero instawdb.sql. Modificamos las líneas 34 y 35 quitando los comentarios y modificando los PATH de nuestra instalación de SQL Server 2008 R2 y la de los ejemplos. Sólo debes indicar la carpeta donde están todos los ejemplos (./samples/), no es necesario que especifiques el directorio de AdventureWorks OLTP, el propio script lo buscará.

:setvar SqlSamplesDatabasePath “C:\Archivos de programa\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\”

:setvar SqlSamplesSourceDataPath “C:\Archivos de programa\Microsoft SQL Server\100\Tools\Samples\”

Una vez modificado, copia en el portapapeles todo el script e inicia SQLCMD. Para ello, puedes hacerlo desde Inicio / Ejecutar escribiendo sqlcmd.exe. Se abrirá una consola donde simplemente tendrás que copiar el código (botón derecho / copiar). Cada instrucción DDL ó DML tiene asociada la instrucción GO, que ejecuta el código anterior, por lo que una vez termine el proceso en la consola, ya tendrás instalada la base de datos lista para operar con sus datos.