sql – 加入100个表

假设我有一个主表,它有100个列引用(作为外键)到大约100个表(包含主键).

整个信息包需要加入这100个表.而且,加入这么多表格绝对是一个性能问题.希望,我们可以期望任何用户想要在包含条件(在查询的WHERE部分)中的查询中,从不超过约5-7个表(在100个中)中请求一堆数据.约3-4张桌子(其中100张).不同的查询具有用于产生查询的“SELECT”部分的表的不同组合,并将条件置于“WHERE”中.但是,再次,每个SELECT将需要大约5-7个表,并且每个WHERE将需要约3-4个表(肯定地,用于生成SELECT的表的列表可能与用于将条件置于WHERE中的表的列表重叠).

我可以使用底层代码编写一个VIEW加入所有这些100个表.然后我可以将上述SQL查询写入此VIEW.但是在这种情况下,这是一个很大的问题,我如何指示sql Server(尽管在代码中显式指示连接所有这些100个表)只有11个表应该被连接(11个表足以连接以产生SELECT结果并考虑到WHERE条件).

另一种方法可能是创建一个转换以下“假”代码的“特征”

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

从语法的角度来看,即使允许使用“TheFakeTable-mechanism”与实际表和结构的任何混合组合也不是问题.这里的真正问题是如何在技术上实现这个“功能”.我可以创建一个将“假”代码作为输入并产生“真实”代码的函数.但是这并不方便,因为它需要使用动态sql工具evrywhere这个“TheFakeTable-mechanism”出现的位置.一个幻想地解决方案是扩展我的Management Studio中的sql语言的语法,以允许编写这样的假代码,然后在发送到服务器之前自动将该代码转换为真实的代码.

我的问题是：

> SQI服务器是否可以指示shomehow(或天才enouh)仅在上述VIEW中加入11个表而不是100个表;
>如果我决定创建这个“TheFakeTable-mechanism”功能,这个功能的技术实现将是最好的形式？

感谢大家每一个评论！

PS
100个表的结构源于我在这里问过的以下问题：
Normalizing an extremely big table

解决方法

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 SCHEMABINDING 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);

执行计划是：

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

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

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

原文链接：https://www.f2er.com/mssql/75154.html

sql – 加入100个表

解决方法

猜你在找的MsSQL相关文章