sql – 加入100个表

SELECT field1,field2,field3 FROM TheFakeTable WHERE field1=12 and field4=5

SELECT T1.field1,T2.field2,T3.field3 FROM TheRealMainTable 
join T1 on ....
join T2 on ....
join T3 on ....
join T4 on ....
WHERE T1.field1=12 and T4.field4=5

sql Server优化器确实包含删除冗余连接的逻辑,但是有限制,连接必须是 provably redundant.总而言之,连接可以有四个效果：

>它可以添加额外的列(从连接的表)
>它可以添加额外的行(连接的表可能多次匹配源行)
>它可以删除行(连接的表可能没有匹配)
>它可以引入NULL(对于RIGHT或FULL JOIN)

要成功删除冗余连接,查询(或视图)必须考虑所有四种可能性.当这样做完成时,效果可以令人惊讶.例如：

USE AdventureWorks2012;
GO
create view dbo.ComplexView
AS
    SELECT
        pc.ProductCategoryID,pc.Name AS CatName,ps.ProductSubcategoryID,ps.Name AS SubCatName,p.productID,p.Name AS ProductName,p.Color,p.ListPrice,p.ReorderPoint,pm.Name AS ModelName,pm.ModifiedDate
    FROM Production.ProductCategory AS pc
    FULL JOIN Production.ProductSubcategory AS ps ON
        ps.ProductCategoryID = pc.ProductCategoryID
    FULL JOIN Production.Product AS p ON
        p.productSubcategoryID = ps.ProductSubcategoryID
    FULL JOIN Production.ProductModel AS pm ON
        pm.ProductModelID = p.productModelID

优化器可以成功简化以下查询：

SELECT
    c.ProductID,c.ProductName
FROM dbo.ComplexView AS c
WHERE
    c.ProductName LIKE N'G%';

至：

Rob Farley在original MVP Deep Dives book上深入研究了这些想法,sqlBits上还有一个recording of him presenTing on the topic.

主要的限制是外部关键关系must be based on a single key有助于简化过程,而针对这种观点的查询的编译时间可能变得相当长,特别是随着联接数量的增加.写一个100表视图可能是一个很大的挑战,它使所有的语义完全正确.我会倾向于找到一个替代解决方案,也许使用dynamic SQl.

也就是说,您的非规范化表格的特殊质量可能意味着视图组合非常简单,只需强制执行FOREIGN KEY非NULL引用列,以及适当的UNIQUE约束,使此解决方案能够按期望运行,而不需要100物理联接操作符在计划中.

例

使用十个表而不是一百个：

-- Referenced tables
create table dbo.Ref01 (col01 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref02 (col02 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref03 (col03 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref04 (col04 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref05 (col05 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref06 (col06 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref07 (col07 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref08 (col08 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref09 (col09 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);
create table dbo.Ref10 (col10 Tinyint PRIMary KEY,item varchar(50) NOT NULL UNIQUE);

父表定义(带页压缩)：

create table dbo.normalized
(
    pk      Integer IDENTITY NOT NULL,col01   Tinyint nOT NULL REFERENCES dbo.Ref01,col02   Tinyint nOT NULL REFERENCES dbo.Ref02,col03   Tinyint nOT NULL REFERENCES dbo.Ref03,col04   Tinyint nOT NULL REFERENCES dbo.Ref04,col05   Tinyint nOT NULL REFERENCES dbo.Ref05,col06   Tinyint nOT NULL REFERENCES dbo.Ref06,col07   Tinyint nOT NULL REFERENCES dbo.Ref07,col08   Tinyint nOT NULL REFERENCES dbo.Ref08,col09   Tinyint nOT NULL REFERENCES dbo.Ref09,col10   Tinyint nOT NULL REFERENCES dbo.Ref10,CONSTraiNT PK_normalized
        PRIMary KEY CLUSTERED (pk)
        WITH (DATA_COMPRESSION = PAGE)
);

风景：

create view dbo.Denormalized
WITH scheR_415_11845@ABINDING AS
SELECT
    item01 = r01.item,item02 = r02.item,item03 = r03.item,item04 = r04.item,item05 = r05.item,item06 = r06.item,item07 = r07.item,item08 = r08.item,item09 = r09.item,item10 = r10.item
FROM dbo.normalized AS n
JOIN dbo.Ref01 AS r01 ON r01.col01 = n.col01
JOIN dbo.Ref02 AS r02 ON r02.col02 = n.col02
JOIN dbo.Ref03 AS r03 ON r03.col03 = n.col03
JOIN dbo.Ref04 AS r04 ON r04.col04 = n.col04
JOIN dbo.Ref05 AS r05 ON r05.col05 = n.col05
JOIN dbo.Ref06 AS r06 ON r06.col06 = n.col06
JOIN dbo.Ref07 AS r07 ON r07.col07 = n.col07
JOIN dbo.Ref08 AS r08 ON r08.col08 = n.col08
JOIN dbo.Ref09 AS r09 ON r09.col09 = n.col09
JOIN dbo.Ref10 AS r10 ON r10.col10 = n.col10;

破解统计数据使优化器认为表格非常大：

updatE STATISTICS dbo.normalized WITH ROWCOUNT = 100000000,PageCount = 5000000;

用户查询示例

SELECT
    d.item06,d.item07
FROM dbo.Denormalized AS d
WHERE
    d.item08 = 'Banana'
    AND d.item01 = 'Green';

给我们这个执行计划：

归一化表的扫描看起来不好,但是在存储引擎的扫描期间都应用了Bloom过滤器位图(因此,不能匹配的行甚至不能与查询处理器进行比较).这可能足以在您的情况下提供可接受的性能,并且肯定比扫描原始表格及其溢出的列更好.

如果您能够在某个阶段升级到sql Server 2012 Enterprise,则可以使用另一个选项：在归一化表上创建一个列存储索引：

CREATE NONCLUSTERED columNSTORE INDEX cs 
ON dbo.normalized (col01,col02,col03,col04,col05,col06,col07,col08,col09,col10);

执行计划是：

这可能看起来更糟糕,但列存储提供了非常的压缩,整个执行计划以批处理模式运行,并为所有贡献列提供过滤器.如果服务器有足够的线程和内存可用,这个替代方案真的可以飞.

最终,我不知道这种正常化是考虑表的数量和获得执行计划不佳或需要过多编译时间的机会的正确方法.我可能会首先纠正非规范化表的模式(适当的数据类型等),可能应用数据压缩…通常的事情.

如果数据真的属于星型模式,则可能需要更多的设计工作,而不仅仅是将重复的数据元素分解成单独的表.