SQL Server使用UNION代替OR提升查询性能的实例_SQL Server_数据库_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
最新资讯 | 百度新闻 | GOOGLE地图 | RSS订阅 | 更多
热搜：
更多>>
 
您所在的位置： 程序员俱乐部 > 数据库 > SQL Server > SQL Server使用UNION代替OR提升查询性能的实例

SQL Server使用UNION代替OR提升查询性能的实例

 2016/5/12 5:35:57    程序员俱乐部  我要评论(0)
  • 摘要：在SQLServer数据库查询的过程中，通过对SQL语句的优化来提高SQL查询的性能。本文我们主要介绍了使用UNION代替OR提升查询性能的一个实例，首先我们先来看一下优化前的代码：SQL>settimingonSQL>setautotraceonSQL>selectcount(*)rowcount_lhy2fromswgl_ddjbxxt3wheret.fzgs_dm='001085'4and(t
  • 标签：Server 使用 Union SQL 实例

在SQL Server数据库查询的过程中，通过对SQL语句的优化来提高SQL查询的性能。本文我们主要介绍了使用UNION代替OR提升查询性能的一个实例，首先我们先来看一下优化前的代码：

    class="dp-xml">
  1. SQL> set timing on    
  2. SQL> set autotrace on    
  3. SQL> select count(*) rowcount_lhy    
  4. 2    from swgl_ddjbxx t    
  5. 3   where t.fzgs_dm = '001085'    
  6. 4     and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists    
  7. 5          (select y.kh_id    
  8. 6             from khgl_khywdlxx y    
  9. 7            where y.kh_id = t.kh_id    
  10. 8              and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or    
  11. 9          t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e')    
  12. 10     and t.xjbz = '9999'    
  13. 11     and t.FROMNBGL1 = '0';    
  14. SQL> set line  300    
  15. SQL> /    
  16. ROWCOUNT_LHY    
  17. ------------    
  18. 60    
  19. 已用时间:  00: 00: 20.53    
  20. 执行计划    
  21. ----------------------------------------------------------    
  22. Plan hash value: 1217125969    
  23. --------------------------------------------------------------------------------------------------------    
  24. | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |    
  25. --------------------------------------------------------------------------------------------------------    
  26. |   0 | SELECT STATEMENT              |                        |     1 |    86 | 28048   (1)| 00:05:37 |    
  27. |   1 |  SORT AGGREGATE               |                        |     1 |    86 |            |          |    
  28. |*  2 |   FILTER                      |                        |       |       |            |          |    
  29. |*  3 |    TABLE ACCESS FULL          | SWGL_DDJBXX            |  5926 |   497K| 28048   (1)| 00:05:37 |    
  30. |*  4 |    TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX          |     1 |    57 |     5   (0)| 00:00:01 |    
  31. |*  5 |     INDEX RANGE SCAN          | IDX_KHGL_KHYWDLXX_KHID |     1 |       |     3   (0)| 00:00:01 |    
  32. --------------------------------------------------------------------------------------------------------    
  33. Predicate Information (identified by operation id):    
  34. ---------------------------------------------------    
  35. 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR    
  36.    "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR  EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y"    
  37.    WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 3 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')    
  38. 4 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')    
  39. 5 - access("Y"."KH_ID"=:B1)    
  40. 统计信息    
  41. ----------------------------------------------------------    
  42. 0  recursive calls    
  43. 0  db block gets    
  44. 804560  consistent gets    
  45. 71127  physical reads    
  46. 0  redo size    
  47. 516  bytes sent via SQL*Net to client    
  48. 469  bytes received via SQL*Net from client    
  49. 2  SQL*Net roundtrips to/from client    
  50. 0  sorts (memory)    
  51. 0  sorts (disk)    
  52. 1  rows processed   

用UNION代替OR对其进行优化后的代码如下：

  1. SQL> select count(*)    
  2. 2    from (select *    
  3. 3            from swgl_ddjbxx t    
  4. 4           where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e'    
  5. 5             and t.fzgs_dm = '001085'    
  6. 6             and t.xjbz = '9999'    
  7. 7             and t.FROMNBGL1 = '0'    
  8. 8          union    
  9. 9          select *    
  10. 10            from swgl_ddjbxx t    
  11. 11           where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e'    
  12. 12             and t.fzgs_dm = '001085'    
  13. 13             and t.xjbz = '9999'    
  14. 14             and t.FROMNBGL1 = '0'    
  15. 15          union    
  16. 16          select *    
  17. 17            from swgl_ddjbxx t    
  18. 18           where exists    
  19. 19           (select y.kh_id    
  20. 20                    from khgl_khywdlxx y    
  21. 21                   where y.kh_id = t.kh_id    
  22. 22                     and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e')    
  23. 23             and t.fzgs_dm = '001085'    
  24. 24             and t.xjbz = '9999'    
  25. 25             and t.FROMNBGL1 = '0');    
  26. COUNT(*)    
  27. ----------    
  28. 60    
  29. 已用时间:  00: 00: 06.89    
  30. 执行计划    
  31. ----------------------------------------------------------    
  32. Plan hash value: 3846872744    
  33. -----------------------------------------------------------------------------------------------------------------------    
  34. | Id  | Operation                          | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    
  35. -----------------------------------------------------------------------------------------------------------------------    
  36. |   0 | SELECT STATEMENT                   |                          |     1 |       |       | 52263   (1)| 00:10:28 |    
  37. |   1 |  SORT AGGREGATE                    |                          |     1 |       |       |         |             |    
  38. |   2 |   VIEW                             |                          |  5996 |       |       | 52263   (1)| 00:10:28 |    
  39. |   3 |    SORT UNIQUE                     |                          |  5996 |  2238K|  6344K| 52263  (47)| 00:10:28 |    
  40. |   4 |     UNION-ALL                      |                          |       |       |       |         |             |    
  41. |*  5 |      TABLE ACCESS FULL             | SWGL_DDJBXX              |    59 | 19234 |       | 28037   (1)| 00:05:37 |    
  42. |*  6 |      TABLE ACCESS BY INDEX ROWID   | SWGL_DDJBXX              |    10 |  3260 |       |  1209   (1)| 00:00:15 |    
  43. |*  7 |       INDEX RANGE SCAN             | IDX_SWGL_DDJBXX_KPRDM    |  4748 |       |       |    34   (0)| 00:00:01 |    
  44. |*  8 |      TABLE ACCESS BY INDEX ROWID   | SWGL_DDJBXX              |     1 |   326 |       |     5   (0)| 00:00:01 |    
  45. |   9 |       NESTED LOOPS                 |                          |  5927 |  2216K|       | 22527   (1)| 00:04:31 |    
  46. |  10 |        SORT UNIQUE                 |                          | 10165 |   565K|       |  1916   (1)| 00:00:23 |    
  47. |  11 |         TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX            | 10165 |   565K|       |  1916   (1)| 00:00:23 |    
  48. |* 12 |          INDEX RANGE SCAN          | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 |       |       |   111   (0)| 00:00:02 |    
  49. |* 13 |        INDEX RANGE SCAN            | IDX_SWGL_DDJBXX_KHID     |     2 |       |       |     2   (0)| 00:00:01 |    
  50. -----------------------------------------------------------------------------------------------------------------------    
  51. Predicate Information (identified by operation id):    
  52. ---------------------------------------------------    
  53. 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' AND "T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999'    
  54.    AND "T"."FZGS_DM"='001085')    
  55. 6 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')    
  56. 7 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e')    
  57. 8 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')    
  58. 12 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')    
  59. 13 - access("Y"."KH_ID"="T"."KH_ID")    
  60. 统计信息    
  61. ----------------------------------------------------------    
  62. 1  recursive calls    
  63. 0  db block gets    
  64. 128422  consistent gets    
  65. 10308  physical reads    
  66. 0  redo size    
  67. 512  bytes sent via SQL*Net to client    
  68. 469  bytes received via SQL*Net from client    
  69. 2  SQL*Net roundtrips to/from client    
  70. 2  sorts (memory)    
  71. 0  sorts (disk)    
  72. 1  rows processed 

SQL改写之后，执行时间由原来的20秒下降到6秒，逻辑读由804560降低到128422，性能还是有很大提升的，到了这里优化还没完，可以创建一个组合索引进一步优化。

create index idx on swgl_ddjbxx(fzgs_dm,xjbz,FROMNBGL1);

创建索引之后，原始的SQL执行时间，执行计划，统计信息如下：

  1. SQL> select count(*) rowcount_lhy    
  2. 2    from swgl_ddjbxx t    
  3. 3   where t.fzgs_dm = '001085'    
  4. 4     and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists    
  5. 5          (select y.kh_id    
  6. 6             from khgl_khywdlxx y    
  7. 7            where y.kh_id = t.kh_id    
  8. 8              and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or    
  9. 9          t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e')    
  10. 10     and t.xjbz = '9999'    
  11. 11     and t.FROMNBGL1 = '0';    
  12. ROWCOUNT_LHY    
  13. ------------    
  14. 60    
  15. 已用时间:  00: 00: 02.96    
  16. 执行计划    
  17. ----------------------------------------------------------    
  18. Plan hash value: 3049366449    
  19. --------------------------------------------------------------------------------------------------------    
  20. | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |    
  21. --------------------------------------------------------------------------------------------------------    
  22. |   0 | SELECT STATEMENT              |                        |     1 |    86 |   506   (0)| 00:00:07 |    
  23. |   1 |  SORT AGGREGATE               |                        |     1 |    86 |            |          |    
  24. |*  2 |   FILTER                      |                        |       |       |            |          |    
  25. |   3 |    TABLE ACCESS BY INDEX ROWID| SWGL_DDJBXX            |  5926 |   497K|   506   (0)| 00:00:07 |    
  26. |*  4 |     INDEX RANGE SCAN          | IDX                    |  2370 |       |    12   (0)| 00:00:01 |    
  27. |*  5 |    TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX          |     1 |    57 |     5   (0)| 00:00:01 |    
  28. |*  6 |     INDEX RANGE SCAN          | IDX_KHGL_KHYWDLXX_KHID |     1 |       |     3   (0)| 00:00:01 |    
  29. --------------------------------------------------------------------------------------------------------    
  30. Predicate Information (identified by operation id):    
  31. ---------------------------------------------------    
  32. 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR    
  33.    "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR  EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y"    
  34.    WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 4 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')    
  35. 5 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')    
  36. 6 - access("Y"."KH_ID"=:B1)    
  37. 统计信息    
  38. ----------------------------------------------------------    
  39. 1  recursive calls    
  40. 0  db block gets    
  41. 702767  consistent gets    
  42. 0  physical reads    
  43. 0  redo size    
  44. 516  bytes sent via SQL*Net to client    
  45. 469  bytes received via SQL*Net from client    
  46. 2  SQL*Net roundtrips to/from client    
  47. 0  sorts (memory)    
  48. 0  sorts (disk)    
  49. 1  rows processed   

改写的SQL：

  1. SQL> select count(*)    
  2. 2    from (select *    
  3. 3            from swgl_ddjbxx t    
  4. 4           where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e'    
  5. 5             and t.fzgs_dm = '001085'    
  6. 6             and t.xjbz = '9999'    
  7. 7             and t.FROMNBGL1 = '0'    
  8. 8          union    
  9. 9          select *    
  10. 10            from swgl_ddjbxx t    
  11. 11           where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e'    
  12. 12             and t.fzgs_dm = '001085'    
  13. 13             and t.xjbz = '9999'    
  14. 14             and t.FROMNBGL1 = '0'    
  15. 15          union    
  16. 16          select *    
  17. 17            from swgl_ddjbxx t    
  18. 18           where exists    
  19. 19           (select y.kh_id    
  20. 20                    from khgl_khywdlxx y    
  21. 21                   where y.kh_id = t.kh_id    
  22. 22                     and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e')    
  23. 23             and t.fzgs_dm = '001085'    
  24. 24             and t.xjbz = '9999'    
  25. 25             and t.FROMNBGL1 = '0');    
  26. COUNT(*)    
  27. ----------    
  28. 60    
  29. 已用时间:  00: 00: 00.53    
  30. 执行计划    
  31. ----------------------------------------------------------    
  32. Plan hash value: 2947849958    
  33. -------------------------------------------------------------------------------------------------------------------------    
  34. | Id  | Operation                            | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    
  35. -------------------------------------------------------------------------------------------------------------------------    
  36. |   0 | SELECT STATEMENT                     |                          |     1 |       |       |  3469   (1)| 00:00:42 |    
  37. |   1 |  SORT AGGREGATE                      |                          |     1 |       |       |         |             |    
  38. |   2 |   VIEW                               |                          |  5995 |       |       |  3469   (1)| 00:00:42 |    
  39. |   3 |    SORT UNIQUE                       |                          |  5995 |  2238K|  4760K|  3469  (86)| 00:00:42 |    
  40. |   4 |     UNION-ALL                        |                          |       |       |       |         |             |    
  41. |*  5 |      TABLE ACCESS BY INDEX ROWID     | SWGL_DDJBXX              |    59 | 19234 |       |   506   (0)| 00:00:07 |    
  42. |*  6 |       INDEX RANGE SCAN               | IDX                      |  2370 |       |       |    12   (0)| 00:00:01 |    
  43. |   7 |      TABLE ACCESS BY INDEX ROWID     | SWGL_DDJBXX              |    10 |  3260 |       |    50   (0)| 00:00:01 |    
  44. |   8 |       BITMAP CONVERSION TO ROWIDS    |                          |       |       |       |         |             |    
  45. |   9 |        BITMAP AND                    |                          |       |       |       |         |             |    
  46. |  10 |         BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |         |             |    
  47. |* 11 |          INDEX RANGE SCAN            | IDX                      |  2370 |       |       |    12   (0)| 00:00:01 |    
  48. |  12 |         BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |         |             |    
  49. |* 13 |          INDEX RANGE SCAN            | IDX_SWGL_DDJBXX_KPRDM    |  2370 |       |       |    34   (0)| 00:00:01 |    
  50. |* 14 |      HASH JOIN RIGHT SEMI            |                          |  5926 |  2216K|       |  2423   (1)| 00:00:30 |    
  51. |  15 |       TABLE ACCESS BY INDEX ROWID    | KHGL_KHYWDLXX            | 10165 |   565K|       |  1916   (1)| 00:00:23 |    
  52. |* 16 |        INDEX RANGE SCAN              | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 |       |       |   111   (0)| 00:00:02 |    
  53. |  17 |       TABLE ACCESS BY INDEX ROWID    | SWGL_DDJBXX              |  5926 |  1886K|       |   506   (0)| 00:00:07 |    
  54. |* 18 |        INDEX RANGE SCAN              | IDX                      |  2370 |       |       |    12   (0)| 00:00:01 |    
  55. -------------------------------------------------------------------------------------------------------------------------    
  56. Predicate Information (identified by operation id):    
  57. ---------------------------------------------------    
  58. 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e')    
  59. 6 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')    
  60. 11 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')    
  61. filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')    
  62. 13 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e')    
  63. 14 - access("Y"."KH_ID"="T"."KH_ID")    
  64. 16 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')    
  65. 18 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')    
  66. 统计信息    
  67. ---------------------------------------------------------    
  68. 1  recursive calls    
  69. 0  db block gets    
  70. 25628  consistent gets    
  71. 0  physical reads    
  72. 0  redo size    
  73. 512  bytes sent via SQL*Net to client    
  74. 469  bytes received via SQL*Net from client    
  75. 2  SQL*Net roundtrips to/from client    
  76. 1  sorts (memory)    
  77. 0  sorts (disk)    
  78. 1  rows processed   

以上就是SQL Server使用UNION代替OR提升查询性能的实例，本文我们就介绍到这里了，希望本次的介绍能够对您有所收获！

上一篇： SQL Server表中数据按条件批量导出为多个Excel文件 下一篇： SQL Server数据库中FOR XML AUTO的使用详解续
MySQL 复制滞后怎么办？其实方法很简单
大数据量下MySQL插入方法的性能比较
经验分享：MySQL日志维护策略总结
关于MySQL应该学习的6件事
PHP中MySQL操作Buffer用法详解

发表评论
用户名: 匿名

最新文章
谈谈SQL数据库中滥用临时表、排序的解决方案优化（举例：汉字转拼音函数）
谈..
修改Sql Server唯一约束
修改Sql S..

最新标签
  • 内存
  • 远程
  • JS
  • 解析
  • XML
  • hadoop
  • 访问权限
  • 表约束
  • 列约束
  • 缺省约束

    • 今日热点

    推荐文章
    修改Sql Server唯一约束
    修改Sql S..
    实战手记：让百万级数据瞬间导入SQL Server
    实战手记：让百..