关于SQL执行效率的问题

[TurboWay]

 /*场景需求：某行业要求查询 原厂货号 不以特定字母开头的 商品销售数据*/
 /*已知条件：fact_sale 有千万条数据,Dim_Product 有60万条数据(符合需求筛选出来的商品有7万条)*/
 /*问题：三种查询语句哪种执行效率比较高，简单说下理由*/
 
/*test1*/      
 SELECT a.Datekey AS '日期' ,
        b.OldProductCode AS '原厂货号' ,
        b.Color AS '颜色' ,
        SUM(ISNULL([SaleQty], 0)) AS '销售量' ,
        SUM(ISNULL([SaleAmt], 0)) AS '销售额' ,
        SUM(ISNULL([SaleDPAmt], 0)) AS '销售吊牌额'
 FROM   fact_sale AS a
        LEFT JOIN dbo.Dim_Product AS b ON a.ProductID = b.ProductID
 WHERE  Datekey >= 20160101
        AND b.OldProductCode NOT LIKE 'X%'
        AND b.OldProductCode NOT LIKE 'T%'
        AND b.OldProductCode NOT LIKE 'D%'
        AND b.OldProductCode NOT LIKE 'Z%'
        AND b.OldProductCode NOT LIKE 'O%'
 GROUP BY a.Datekey ,
        b.OldProductCode ,
        b.Color
/*test2*/     
 SELECT a.Datekey AS '日期' ,
        b.OldProductCode AS '原厂货号' ,
        b.Color AS '颜色' ,
        SUM(ISNULL([SaleQty], 0)) AS '销售量' ,
        SUM(ISNULL([SaleAmt], 0)) AS '销售额' ,
        SUM(ISNULL([SaleDPAmt], 0)) AS '销售吊牌额'
 FROM   fact_sale AS a
        INNER  JOIN ( SELECT    OldProductCode ,
                                productid ,
                                color
                      FROM      dbo.Dim_Product
                      WHERE     OldProductCode NOT LIKE 'X%'
                                AND OldProductCode NOT LIKE 'T%'
                                AND OldProductCode NOT LIKE 'D%'
                                AND OldProductCode NOT LIKE 'Z%'
                                AND OldProductCode NOT LIKE 'O%'
                    ) AS b ON a.ProductID = b.ProductID
 WHERE  Datekey >= 20160101
 GROUP BY a.Datekey ,
        b.OldProductCode ,
        b.Color ;       
/*test3*/     
 WITH   product
          AS ( SELECT   OldProductCode ,
                        productid ,
                        color
               FROM     dbo.Dim_Product
               WHERE    OldProductCode NOT LIKE 'X%'
                        AND OldProductCode NOT LIKE 'T%'
                        AND OldProductCode NOT LIKE 'D%'
                        AND OldProductCode NOT LIKE 'Z%'
                        AND OldProductCode NOT LIKE 'O%'
             )
    SELECT  a.Datekey AS '日期' ,
            b.OldProductCode AS '原厂货号' ,
            b.Color AS '颜色' ,
            SUM(ISNULL([SaleQty], 0)) AS '销售量' ,
            SUM(ISNULL([SaleAmt], 0)) AS '销售额' ,
            SUM(ISNULL([SaleDPAmt], 0)) AS '销售吊牌额'
    FROM    fact_sale AS a
            INNER  JOIN product AS b ON a.ProductID = b.ProductID
    WHERE   Datekey >= 20160101
    GROUP BY a.Datekey ,
            b.OldProductCode ,
            b.Color 

[--小F--]

直接看执行计划。。。 光给语句，没有什么发言权

[--小F--]

不过第三种方法先过滤了数据得到结果集 然后再JOIN 或许效率会高一些

[TurboWay]

引用 1 楼 fredrickhu 的回复:

直接看执行计划。。。 光给语句，没有什么发言权

看过了 三个的执行计划都是一模一样的

[吉普赛的歌]

--1. 加计算列
ALTER TABLE Dim_Product ADD notLikeXTDZO AS CASE WHEN 
	OldProductCode NOT LIKE 'X%' AND
	OldProductCode NOT LIKE 'T%' AND 
	OldProductCode NOT LIKE 'D%' AND
	OldProductCode NOT LIKE 'Z%' AND
	OldProductCode NOT LIKE '(%' THEN 1 ELSE 0 END 

--2. 加索引
CREATE INDEX IX_fact_sale_ProductID ON fact_sale (ProductID)
CREATE INDEX IX_Dim_Product_ProductID ON Dim_Product (ProductID) INCLUDE(OldProductCode,Color)
CREATE INDEX IX_fact_sale_1 ON fact_sale (Datekey) INCLUDE(SaleQty,SaleAmt,SaleDPAmt)

--3. 语句
SELECT a.Datekey AS '日期' ,
    b.OldProductCode AS '原厂货号' ,
    b.Color AS '颜色' ,
    SUM(ISNULL([SaleQty], 0)) AS '销售量' ,
    SUM(ISNULL([SaleAmt], 0)) AS '销售额' ,
    SUM(ISNULL([SaleDPAmt], 0)) AS '销售吊牌额'
FROM   fact_sale AS a
    LEFT JOIN dbo.Dim_Product AS b ON a.ProductID = b.ProductID AND b.notLikeXTDZO=1
WHERE  Datekey >= 20160101
GROUP BY a.Datekey ,
    b.OldProductCode ,
    b.Color

[吉普赛的歌]

不管执行计划是什么样的， 你都应该截图出来给大家看看。 上面是我估计的优化， 你也可以照着做看有没有改善

[TurboWay]

引用 5 楼 yenange 的回复:

不管执行计划是什么样的， 你都应该截图出来给大家看看。 上面是我估计的优化， 你也可以照着做看有没有改善

嗯嗯，对的，应该贴出来，我等下贴。然后索引这块，dim_product是维表，ID是有设主键的。至于fact_sale，由于是频繁增删改的业务数据事实表，索引不考虑，维护索引的开销太大。

[TurboWay]

查询1,2,3分别对应，test1,2,3 三种SQL写法

[TurboWay]

引用 6 楼 TurboWay 的回复:

[quote=引用 5 楼 yenange 的回复:] 不管执行计划是什么样的， 你都应该截图出来给大家看看。 上面是我估计的优化， 你也可以照着做看有没有改善

嗯嗯，对的，应该贴出来，我等下贴。然后索引这块，dim_product是维表，ID是有设主键的。至于fact_sale，由于是频繁增删改的业务数据事实表，索引不考虑，维护索引的开销太大。[/quote]所以 我问的是 哪种写法效率会好一点，，或者都一样？？因为优化基本没戏，语句就那样，两张表又不能改

[吉普赛的歌]

那我加计算列的你可以参照加吧

[kingtiy]

光从语句上面看,后面两应该会好一点,因为先缩小了join的结果集. 另外,事实表可以在Datekey 列上面建立聚集索引,这样查询的效率会大增.也不影响你的数据导入. 之于查询计划三个都一样,我估计是sqlserver自身对计划做了优化. 还有就是维度表可以OldProductCode列上面建索引. 你上面这些个查询,可以先把那几种情况的key查询出来,可以利用到索引. 然后再对维度表进行排除上面查出来的key 这样效率我猜测应该会比你直接用not like 效果好. 楼主不防试下我说的上面两点针对表的索引及not like 的处理

[TurboWay]

引用 10 楼 kingtiy 的回复:

光从语句上面看,后面两应该会好一点,因为先缩小了join的结果集. 另外,事实表可以在Datekey 列上面建立聚集索引,这样查询的效率会大增.也不影响你的数据导入. 之于查询计划三个都一样,我估计是sqlserver自身对计划做了优化. 还有就是维度表可以OldProductCode列上面建索引. 你上面这些个查询,可以先把那几种情况的key查询出来,可以利用到索引. 然后再对维度表进行排除上面查出来的key 这样效率我猜测应该会比你直接用not like 效果好. 楼主不防试下我说的上面两点针对表的索引及not like 的处理

嗯，3Q。话说，三个计划都一样，是不是可以理解为，执行效率也是一样的，这几种写法，在sqlserver看来都是一样的？

[薛定谔的DBA]

执行计划一样，效率是一样的，因为内部处理过程都是一样。比较好和标准的写法，第一种规范些。这种情况只能加索引了。