Memorias de un DBA

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

Foreign Keys, Buenas o Malas?

leave a comment »

Hola, ha pasado un buen tiempo desde mi último post, y la verdad es que he estado bastante atareado con el trabajo estas últimas semanas, pero ya volví, y decidí darle un poco de tiempo a este pequeño rincón que he creado para poder compartir el conocimiento en base de datos. Ahora quisiera hablar sobre algo que vi en estos últimos días y la verdad es algo polémico, porque la gente en la comunidad de SQL Server no está 100% de acuerdo con el tema, y a lo que me refiero es a las relaciones entre las tablas de las bases de datos.

Las relaciones entre las tablas de la base de datos son implementadas a través de constraints, los cuales aseguran la integridad de la información contenida en las tablas. Desde mi punto de vista los constraints de base de datos son el último cordón de seguridad para nuestra información. Un sistema puede tener muchísimas reglas de negocio y de integridad de los datos en el lado de la aplicación, y eso está bien, pero no hay que olvidar que las bases de datos siempre están expuestas a bugs dentro de la aplicación o ataques externos o internos dentro de la organización los cuales pueden introducir información maliciosa o incorrecta en las tablas sin necesidad de pasar por las capas aplicación, en este caso la base de datos queda completamente expuesta a inconsistencia de datos.

Ahora los detractores de las relaciones entre las tablas indican que al colocar este constraint en las tablas tiene un precio en la performance de las operaciones de insert, delete y update. Esto es cierto, pero si los constraints de Foreign Key han sido bien definidos, esto no tiene porque ser un problema debido a que una de las mejores prácticas al momento de crear un constraint de Foreign Key es Indexarlo. Esto no lo hace el motor de base de datos automáticamente, esto debe ser hecho por el arquitecto de base de datos o el administrador de base de datos. Si una relación esta indexada, se reduce bastante el impacto en las operaciones de insert, delete y update e incluso se logra reducir la posibilidad de obtener un deadlock en la base de datos; además de esto es muy beneficioso para las operaciones de lectura contra las tablas ya que si las relaciones están indexadas, el optimizador de consultas de la base de datos podrá obtener mejores planes de ejecución dando como resultado respuestas mas rápidas.

A continuación veremos un ejemplo simple que demostrara el efecto que puede tener el hecho de tener o no relaciones en las bases de datos. Para este ejemplo usaremos la base de datos AdventureWorksR2 la cual puede ser descargada desde aquí. Primero revisaremos el plan de ejecución que se genera con la siguiente consulta:

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Se puede observar que de las tres tablas que intervienen en la consulta solo dos aparecen en el plan de ejecución. Esto se debe a que sql server confia en la información que se encuentra en las tablas debido a los foreign keys que hay entre ellas. Para comprobarlo vamos a crear las mismas tres tablas pero sin relaciones para ver el comportamiento del motor de base de datos.

SELECT *
INTO dbo.ProductCategory
FROM Production.ProductCategory

SELECT *
INTO dbo.ProductSubcategory
FROM Production.ProductSubcategory

SELECT *
INTO dbo.Product
FROM Production.Product

Ahora ejecutaremos la misma consulta pero con las nuevas tablas sin relaciones.
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Ahora vemos que el plan de ejecución es diferente, esto se debe a que como no hay relaciones entre las tablas SQL debe acceder a todas las tablas para poder obtener la información requerida. Pero algunos dirán que esas tablas no tienen indices y que los índices ayudarían. Entonces crearemos los índices sugeridos.

ALTER TABLE dbo.ProductCategory
ADD CONSTRAINT PK_ProductCategory_tmp
PRIMARY KEY (ProductCategoryID)
GO

ALTER TABLE dbo.ProductSubcategory
ADD CONSTRAINT PK_ProductSubcategory_tmp
PRIMARY KEY (ProductSubcategoryID)
GO

ALTER TABLE dbo.Product
ADD CONSTRAINT PK_Product_tmp
PRIMARY KEY (ProductID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_ProductSubcategory_01
ON dbo.ProductCategory (ProductCategoryID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_Product_01
ON dbo.Product (ProductSubcategoryID)
GO

Ahora volvemos a ejecutar la misma consulta, pero ademas ahora mediremos las operaciones de IO.

SET STATISTICS IO ON
GO
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID


Vemos que el plan de ejecución ha variado un poco. Ahora veamos las estadísticas de IO

Table 'Product'. Scan count 37, logical reads 664, physical reads 0
Table
'ProductCategory'. Scan count 0, logical reads 74, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads 0

Ahora mediremos las estadisticas de IO que resultan de la consulta a las tablas con relaciones.

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table
'Product'. Scan count 1, logical reads 15, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads

Como se puede observar claramente la consulta con las tablas que tienen relaciones es mucho más eficiente que la consulta a las tablas sin relaciones, debido a que el optimizador de consultas de SQL Server tiene más recursos de los cuales valerse para poder obtener planes de ejecución mas óptimos. El numero de lecturas baja considerablemente casi un 98%. Entonces mi opinión personal las relaciones entre las tablas es bastante beneficioso para la base de datos tanto desde el punto de vista de seguridad como de performance.

Written by dbamemories

septiembre 19, 2011 a 9:52 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: