Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

Foreign Keys Duplicadas

leave a comment »

Hola, hace ya algún tiempo que no posteo nada, y bueno la verdad es que he estado en una transición bastante amplia en mi vida profesional y me aleje un poco de mi blog, pero ahora pienso retomarlo con fuerza.

Bueno el tema que me trae a este nuevo post son los constraints duplicados especialmente los foreign keys. Hace un tiempo recibí un servidor productivo de base de datos del cual los usuarios tenían quejas constantes sobre lentitud. Una de las tareas que realice sobre el servidor fue la revisión de las claves foráneas de las diferentes bases de datos, y encontré una gran cantidad de estas, la gran mayoría estaban duplicadas, triplicadas y hasta cuadriplicadas.

Haciendo consultas a los desarrolladores, me comentaron que ellos usaban el ERwin para poder hacer la creación de objetos de base de datos, y simplemente ejecutaban los scripts que este generaba, entonces comencé a entender el problema, ahora ya puedia plantear una solución al mismo. Pero antes quisiera mencionar que por cada constraint de clave foránea que tiene la tabla, cuando se ejecute alguna operación de modificación de datos, el motor validara los datos contra todos los constraints de la tabla generando lecturas en las tablas referenciadas, entonces si tenemos muchos contraints duplicados, el motor de base de datos hara muchas lecturas innecesarias para validar dichos constraints. A continuación se muestra un ejemplo de esto:

CREATE DATABASE DUPFKS
GO

USE DUPFKS
GO

IF (OBJECT_ID(‘dbo.Hijo’)>0)
DROP TABLE dbo.Hijo
GO
IF (OBJECT_ID(‘dbo.Padre’)>0)
DROP TABLE dbo.Padre
GO
— Creamos la tabla padre
CREATE TABLE dbo.Padre
(col1 INT CONSTRAINT PK_Padre PRIMARY KEY)
GO
— Creamos la tabla hijo sin NINGUN constraint duplicado
CREATE TABLE dbo.Hijo
(col1 INT NOT NULL
,
col2 INT NOT NULL)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT PK_Hijo
PRIMARY KEY (col1,col2)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_1
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO
— Llenamos la tabla padre con algunos registros
INSERT INTO Padre
SELECT OBJECT_ID
FROM sys.objects

— Indicamos que se midan las estadisticas de IO
SET STATISTICS IO ON

INSERT INTO Hijo VALUES (4,1)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

INSERT INTO Hijo VALUES (4,2)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Ingresamos el primer constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_2
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,3)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se duplicaron

— Ingresamos el segundo constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_3
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,4)
/*
Table ‘Padre’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se triplicaron

— Para comprobar la teoria deshabilitaremos el ultimo constraint duplicado creado
ALTER TABLE dbo.Hijo
NOCHECK CONSTRAINT FK_Hijo_col1_3
GO

— Realizamos la inserción
INSERT INTO Hijo VALUES (4,5)
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Podemos ver que las lecturas contra la tabla padre ya no validaron
— el ultimo constraint duplicado ya que este fue eliminado.

Para solucionar este problema se ha realizado el siguiente script el cual permite identificar los constraints duplicados además de proveer la sentencia necesaria para eliminarlos.

;WITH ForeignKeys1 AS
(
SELECT fk.OBJECT_ID
,OBJECT_NAME(fk.parent_object_id) AS table_name
,fk.name AS foreign_key_name
,fk.create_date
,(SELECT CAST(parent_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(parent_column_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_column_id AS VARCHAR(50)) AS [data()]
FROM sys.foreign_key_columns fkc
WHERE fk.OBJECT_ID = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR XML PATH('')) foreign_key
FROM sys.foreign_keys fk
WHERE is_disabled = 0
)
,
ForeignKeys2 AS
(
SELECT OBJECT_ID, table_name, foreign_key_name, foreign_key
,COUNT(1) OVER (PARTITION BY foreign_key) AS NbrDuplicated
,ROW_NUMBER() OVER (PARTITION BY foreign_key ORDER BY foreign_key_name) AS Rownum
FROM ForeignKeys1
)

— Sentencia para listar los constraints duplicados
SELECT table_name AS [Table Name]
,foreign_key_name AS [Foreign Key]
,‘ALTER TABLE ‘ + QUOTENAME(table_name,‘]’) + ‘ DROP CONSTRAINT ‘ +
QUOTENAME(foreign_key_name,‘]’) AS [Drop Sentence]
FROM ForeignKeys2
WHERE NbrDuplicated > 1
ORDER BY table_name, foreign_key_name

Este script ha sido modificado del original encontrado en la siguiente URL: http://www.jasonstrate.com/2009/01/find-duplicate-foreign-keys/#

Espero haya sido de utilidad este post y sirva para mantener más sanas sus bases de datos. Hasta la próxima.

Written by dbamemories

julio 18, 2012 a 9:29 pm

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: