SQL优化之OR

阅读目录

• 案例语句
• 分析手段
• 如何优化
• 招聘信息

今天分析OR的优化方式,主要是案例进行剖析,单纯的说这个优化有多明显我认为是完全没有意义的,任何东西,任何道理,没有数据说话,总是会让人质疑罢了

 

回到顶部

案例语句

某大型房地产公司，巡检日期2013-04-23，问题语句

SELECT  SUM(num) AS Num ,
        HZLevel
FROM    ( SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年内合作' AS HZLevel
          FROM      ( SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
                                                             OR b.bfProviderGUID = a.Providerguid
                                                           )
                      WHERE     DATEDIFF(year, b.SignDate, GETDATE()) <= 3
                    ) a
          UNION ALL
          SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年前合作' AS HZLevel
          FROM      ( SELECT    Providerguid ,
                                MAX(SignDate) AS m
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
                                                             OR a.Providerguid = b.bfProviderGUID
                                                           )
                      GROUP BY  Providerguid
                    ) a
          WHERE     DATEDIFF(year, m, GETDATE()) > 3
          UNION ALL
          SELECT    ISNULL(SUM(1), 0) AS num ,
                    '无合作' AS HZLevel
          FROM      ( SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid
                      WHERE     Providerguid NOT IN (
                                SELECT DISTINCT
                                        YfProviderGUID
                                FROM    cb_Contract
                                WHERE   YfProviderGUID IS NOT NULL
                                UNION
                                SELECT DISTINCT
                                        bfProviderGUID
                                FROM    cb_Contract
                                WHERE   bfProviderGUID IS NOT NULL )
                    ) a
        ) a
GROUP BY HZLevel
UNION ALL
SELECT  ISNULL(SUM(1), 0) AS num ,
        '合计' AS HZLevel
FROM    ( SELECT DISTINCT
                    providerguid ,
                    hzlevel
          FROM      vp_Provider2UnitGrid
        ) a
ORDER BY HZLevel DESC

SQL Server 分析和编译时间: 
   CPU 时间 = 187 毫秒，占用时间 = 247 毫秒。

(4 行受影响)
表 'Worktable'。扫描计数 8，逻辑读取 823264 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'cb_Contract'。扫描计数 18，逻辑读取 15552 次，物理读取 2 次，预读 1738 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'p_Provider'。扫描计数 16，逻辑读取 548 次，物理读取 2 次，预读 111 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
   CPU 时间 = 29717 毫秒，占用时间 = 8419 毫秒。
警告: 聚合或其他 SET 操作消除了空值。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 

执行时间为8.4S，IO也是非常高的,类似这种语句如何下手?其实方法很简单,优化的效果也是非常好的

分析手段

因为语句中有UNION ALL,所以不用开执行计划,直接把每个UNION ALL起来的语句分别执行,找出最慢的语句即可

SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年内合作' AS HZLevel
          FROM      ( SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
                                                             OR b.bfProviderGUID = a.Providerguid
                                                           )
                      WHERE     DATEDIFF(year, b.SignDate, GETDATE()) <= 3
                    ) a

    SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年前合作' AS HZLevel
          FROM      ( SELECT    Providerguid ,
                                MAX(SignDate) AS m
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
                                                             OR a.Providerguid = b.bfProviderGUID
                                                           )
                      GROUP BY  Providerguid
                    ) a
          WHERE     DATEDIFF(year, m, GETDATE()) > 3

通过分段去执行查找发现,上面的2个语句非常慢,那么还是从局部入手改变语序,以达到优化整段SQL的目的,如何确定问题点在哪?这其实非常关键,也是重点要讲的

1,where条件不符合SARG，这样导致无法用到索引

2,使用了视图，导致取数不是最优的，看是不是能按基表取数

3,OR的影响,OR和NOT IN一样,也会导致索引用不上,其实最可恶的是,OR还可能导致执行计划不是最优

那么影响点在哪里?　　请看下面

SELECT    ISNULL(SUM(1), 0) AS num ,
                    '无合作' AS HZLevel
          FROM      ( SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid
                      WHERE     Providerguid NOT IN (
                                SELECT DISTINCT
                                        YfProviderGUID
                                FROM    cb_Contract
                                WHERE   YfProviderGUID IS NOT NULL
                                UNION
                                SELECT DISTINCT
                                        bfProviderGUID
                                FROM    cb_Contract
                                WHERE   bfProviderGUID IS NOT NULL )
                    ) a

刚刚列出了有问题的SQL,没有这段,说明这段SQL是没问题的,确实也是如此,这段SQL也用到了同样的视图, where条件也不符合SARG，但是它的速度确非常快

那么我有理由最先怀疑，问题点就出在OR这里，那么我把OR这个条件注释掉，再来看整个SQL语句，发现速度变快了，那么只需要优化OR即可

回到顶部

如何优化

SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年内合作' AS HZLevel
          FROM      ( SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
                                                             --OR b.bfProviderGUID = a.Providerguid
                                                           )
                      WHERE     DATEDIFF(year, b.SignDate, GETDATE()) <= 3
                      UNION 
                      SELECT DISTINCT
                                Providerguid
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_Contract b ON (  b.bfProviderGUID = a.Providerguid
                                                           )
                      WHERE     DATEDIFF(year, b.SignDate, GETDATE()) <= 3
                      
                    ) a
                   
          UNION ALL
          SELECT    ISNULL(SUM(1), 0) AS num ,
                    '三年前合作' AS HZLevel
          FROM      ( SELECT    Providerguid ,
                                MAX(SignDate) AS m
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
                                                             --OR a.Providerguid = b.bfProviderGUID
                                                           )
                      GROUP BY  Providerguid
                      UNION 
                       SELECT    Providerguid ,
                                MAX(SignDate) AS m
                      FROM      vp_Provider2UnitGrid a
                                LEFT JOIN cb_contract b ON (  a.Providerguid = b.bfProviderGUID
                                                           )
                      GROUP BY  Providerguid
                    ) a

(2 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'p_Provider'。扫描计数 4，逻辑读取 224 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'cb_Contract'。扫描计数 4，逻辑读取 6088 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 63 毫秒，占用时间 = 85 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 

只需要把OR改成UNION即可，简单看一下，优化前后的对比结果，可谓天壤之别

回到顶部

招聘信息

1. 工作地点：武汉 

2. 工作经验2年以上

3. asp.net开发, 基础夯实。

公司名称:武汉明源动力软件有限公司  

给我发邮件:236773862@qq.com 

如果，您认为阅读这篇博客让您有些收获，不妨点击一下右下角的【推荐】按钮。
如果，您希望更容易地发现我的新博客，不妨点击一下右下角的【关注 JustZr】。
因为，我的写作热情也离不开您的肯定支持。

感谢您的阅读，如果您对我的博客所讲述的内容有兴趣，请继续关注我的后续博客 。