摘要:本文结合笔者实际的工作情况,说明了如何使用with as改写存储过程,从而大幅提高SQL的运行时间。本文首先交代了案例的起因,随后简单介绍了WITH AS的含义,最后基于实际工作,使用了一个测试用例来说明如何使用WITH AS。
1. 案例起因
公司门店应用程序每天都要出一份报表,用来统计所有商品当天的期初库存数量、入库数量、出库数量
及当天的期末库存数量。运行半年以后,这份报表运行越来越慢,到现在,每次运行该报表显示当天数据时需要近20秒的时间。于是开发人员找到我,希望我看看,是不是可以使该报表运行的时间更短。
该报表就是一段SQL语句,主要由三部分组成,第一部分是计算每个商品的期初数量,第二部分是计算每个商品的当天发生(包括入库和出库的)数量,第三部分是计算每个商品的期末数量,也就是当天的余额。每个部分使用UNION ALL连接起来。
我看到该报表,第一个感觉就是这段SQL里的每个部分都要对表进行扫描,明显成本过高。应该可以使用WITH AS进行改写。
2. WITH AS的含义
WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
3. 案例说明
首先介绍该SQL所涉及到的主要的表的结构。该表表名为fin,用来存放每天每个商品的发生数以及该商
品的余额数。其表结构为如下所示(这里我只选取了与我们要讨论的SQL相关的部分表字段)。
SQL> desc fin
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
。。。。。。
DAY DATE
SKU VARCHAR2(8)
INQTY NUMBER(16,6)
OUTQTY NUMBER(16,6)
LASTQTY NUMBER(16,6)
。。。。。。。。
简单解释一下各个字段的含义:
1) DAY:发生的日期。
2) SKU:发生交易的商品代码。
3) INQTY:商品入库数量。
4) OUTQTY:商品出库数量。
5) LASTQTY:商品的余额数量。
该表中含有的记录数量为:
SQL> SELECT count(*) FROM fin;
COUNT(*)
----------
4729319
原来的SQL如下所示(比如查询2003年7月14日这天的记录。当然,我对该SQL做了些修改,去掉了与本文讨论无关的部分,比如显示商品名称之类的部分等):
SELECT
sku,
sum(initqty) as initqty,
sum(inqty) as inqty,sum(outqty) as outqty,
sum(lastqty) as lastqty
FROM (
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty
FROM fin
WHERE day=to_date('20030713','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty
FROM fin
WHERE day>=to_date('20030714','yyyymmdd') and day<=to_date('20030714','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty
FROM fin
WHERE day=to_date('20030714','yyyymmdd')
)
GROUP BY sku;
我们来看该SQL所花费的时间为:
SQL> set timing on
SQL> /
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
SKU INITQTY INQTY OUTQTY LASTQTY
-------- ---------- ---------- ---------- ----------
00106162 0 0 12 60
00106467 0 20 10 60
已选择956行。
已用时间: 00: 00: 19.08
然后,我们来对该SQL进行改写一番,如下所示:
WITH result AS (
SELECT /*+ materialize */ day,sku,inqty,outqty,lastqty
FROM fin
WHERE day>=to_date('20030713','yyyymmdd') AND day<=to_date('20030714','yyyymmdd'))
SELECT
sku,
sum(initqty) as initqty,
sum(inqty) as inqty,
sum(outqty) as outqty,
sum(lastqty) as lastqty
FROM (
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty
FROM result
WHERE day=to_date('20030713','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty
FROM result
WHERE day=to_date('20030714','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty
FROM result
WHERE day=to_date('20030714','yyyymmdd')
)
GROUP BY sku;
我们来看修改后的SQL所花费的时间为:
SQL> set timing on
SQL> /
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
SKU INITQTY INQTY OUTQTY LASTQTY
-------- ---------- ---------- ---------- ----------
00106162 0 0 12 60
00106467 0 20 10 60
已选择956行。
已用时间: 00: 00: 06.06
从这里可以看到,通过WITH AS可以从20秒降低到6秒,几乎提高了65%的性能。
1. 案例起因
公司门店应用程序每天都要出一份报表,用来统计所有商品当天的期初库存数量、入库数量、出库数量
及当天的期末库存数量。运行半年以后,这份报表运行越来越慢,到现在,每次运行该报表显示当天数据时需要近20秒的时间。于是开发人员找到我,希望我看看,是不是可以使该报表运行的时间更短。
该报表就是一段SQL语句,主要由三部分组成,第一部分是计算每个商品的期初数量,第二部分是计算每个商品的当天发生(包括入库和出库的)数量,第三部分是计算每个商品的期末数量,也就是当天的余额。每个部分使用UNION ALL连接起来。
我看到该报表,第一个感觉就是这段SQL里的每个部分都要对表进行扫描,明显成本过高。应该可以使用WITH AS进行改写。
2. WITH AS的含义
WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
3. 案例说明
首先介绍该SQL所涉及到的主要的表的结构。该表表名为fin,用来存放每天每个商品的发生数以及该商
品的余额数。其表结构为如下所示(这里我只选取了与我们要讨论的SQL相关的部分表字段)。
SQL> desc fin
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
。。。。。。
DAY DATE
SKU VARCHAR2(8)
INQTY NUMBER(16,6)
OUTQTY NUMBER(16,6)
LASTQTY NUMBER(16,6)
。。。。。。。。
简单解释一下各个字段的含义:
1) DAY:发生的日期。
2) SKU:发生交易的商品代码。
3) INQTY:商品入库数量。
4) OUTQTY:商品出库数量。
5) LASTQTY:商品的余额数量。
该表中含有的记录数量为:
SQL> SELECT count(*) FROM fin;
COUNT(*)
----------
4729319
原来的SQL如下所示(比如查询2003年7月14日这天的记录。当然,我对该SQL做了些修改,去掉了与本文讨论无关的部分,比如显示商品名称之类的部分等):
SELECT
sku,
sum(initqty) as initqty,
sum(inqty) as inqty,sum(outqty) as outqty,
sum(lastqty) as lastqty
FROM (
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty
FROM fin
WHERE day=to_date('20030713','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty
FROM fin
WHERE day>=to_date('20030714','yyyymmdd') and day<=to_date('20030714','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty
FROM fin
WHERE day=to_date('20030714','yyyymmdd')
)
GROUP BY sku;
我们来看该SQL所花费的时间为:
SQL> set timing on
SQL> /
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
SKU INITQTY INQTY OUTQTY LASTQTY
-------- ---------- ---------- ---------- ----------
00106162 0 0 12 60
00106467 0 20 10 60
已选择956行。
已用时间: 00: 00: 19.08
然后,我们来对该SQL进行改写一番,如下所示:
WITH result AS (
SELECT /*+ materialize */ day,sku,inqty,outqty,lastqty
FROM fin
WHERE day>=to_date('20030713','yyyymmdd') AND day<=to_date('20030714','yyyymmdd'))
SELECT
sku,
sum(initqty) as initqty,
sum(inqty) as inqty,
sum(outqty) as outqty,
sum(lastqty) as lastqty
FROM (
SELECT sku,lastqty as initqty,0 as inqty,0 as outqty,0 as lastqty
FROM result
WHERE day=to_date('20030713','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,inqty,outqty,0 as lastqty
FROM result
WHERE day=to_date('20030714','yyyymmdd')
UNION ALL
SELECT sku,0 as initqty,0 as inqty,0 as outqty,lastqty
FROM result
WHERE day=to_date('20030714','yyyymmdd')
)
GROUP BY sku;
我们来看修改后的SQL所花费的时间为:
SQL> set timing on
SQL> /
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
SKU INITQTY INQTY OUTQTY LASTQTY
-------- ---------- ---------- ---------- ----------
00106162 0 0 12 60
00106467 0 20 10 60
已选择956行。
已用时间: 00: 00: 06.06
从这里可以看到,通过WITH AS可以从20秒降低到6秒,几乎提高了65%的性能。
发表评论
-
查看当前Session SQL
2014-07-08 11:51 1038SELECT (SELECT listagg(b.sql_te ... -
CLOB列 XML信息查看
2014-05-28 10:28 979--使用该SQL查询节点情况 SELECT * FROM ... -
【转】ORACLE 临时表空间使用率过高的原因及解决方案
2012-12-25 15:24 925http://www.cnblogs.com/wonder31 ... -
oracle:获取session的IP地址
2012-10-20 02:38 4193方法1 创建触发器: create orreplace ... -
Oracle ora-01002
2012-08-11 02:43 28743ORA-01002:fetch超出序列 ... -
提高Oracle SQL的执行效率的3个方案
2012-08-08 00:57 945如果你想要提高Oracle SQL ... -
Oracle STA
2012-08-06 11:32 0第一步:创建优化任务 ... -
Oracle SQL小技巧
2012-08-06 11:21 01.两个字段可空的判断相等,用decode判断。 例子:d ... -
自动工作负载库(Automatic Workload Repository,AWR)
2012-07-23 22:45 1352自动工作负载库(Automatic Workload Repo ... -
orace的隔离级别
2012-07-21 01:06 1074隔离级别(isoation eve) 隔离级别定义了事务与 ... -
Oracle SQLID 与 Hash_value 之间的相互转化
2012-07-20 00:55 4666一、什么是SQLID SQLID是根据SQL 文本,经过 ... -
Oracle优化器和索引原
2012-07-13 00:34 1241Oracle优化器和索引原理 ============ ... -
Oracle优化器的RBO和CBO方式
2012-07-13 00:25 2373[/size]Or[size=large][size=smal ... -
Oracle 优化器详解
2012-07-13 00:18 1327一、优化器基本知识 Oracle在执行一个SQL之前,首先 ... -
Ibatis调用Oracle存储过程,以及返回Cursor结果集的问题
2012-07-01 23:46 2071最近开始接触Oracle了,接触的越多越感受到自己的渺小!(o ... -
Oracle表连接操作——Hash Join(哈希连接
2012-05-20 17:05 0连接 http://space.itpub.net/?uid ... -
Oracle hash join
2012-05-20 17:00 872hash join是oracle里面一个 ... -
转--一次HASH JOIN 临时表空间不足的分析和优化思路
2012-05-20 15:36 4473最近遇到一个语句, 只要一执行这个语句就会出现报错临时表空间 ... -
SQL*PLUS SET 变量
2012-05-02 22:46 851SQL*PLUS SET变量 SQL*PLUS维护系 ... -
转---借助内存表处理复杂的oracle查询要求.
2012-03-25 23:23 1379借助内存表处理复杂的oracle查询要求. 在日常业务处理过 ...
相关推荐
在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后...
Although 2 GB of address space may seem like a large amount of memory, application such as SQL Server could leverage more memory if it were available. The boot.ini option /3GB was created for those ...
Differentiate locks, latches, and other SQL Server internal “locking” mechanism such as spinlocks and other synchronization objects. Recommended Reading Chapter 14 “Locking”, Inside SQL ...
如果已在安装过程中使用了默认的命名实例,则将实例指定为“SQLExpress”。 [顶部] 2.3 管理 SQL Server Express 的工具 有关连接到 SQL Server Express 数据库并进行管理的信息,请参阅 Microsoft 知识库文章 ...
Just as the organization of a telephone directory makes it easy for a person to search, SQL Server quickly searches a table with a clustered index. Because a clustered index determines the sequence ...
这样,如果需要编译所有工程条目或者将工程从某个位置或数据库移动到其他位置时,所需工程条目的查找就变得比较简单, <br>To-Do条目——您可以在任何SQL或PL/SQL源文件中使用To-Do条目快速记录该文件中那些需要...
为了提升全文索引过程的效率,Microsoft SQL Server 2005 改进了全文收集机制的体系结构,从而大大增强了性能。 Microsoft Full-Text Engine for SQL Server (MSFTESQL) 服务是一个全文索引和搜索引擎。MSFTESQL ...
这样,如果需要编译所有工程条目或者将工程从某个位置或数据库移动到其他位置时,所需工程条目的查找就变得比较简单, <br>To-Do条目——您可以在任何SQL或PL/SQL源文件中使用To-Do条目快速记录该文件中那些需要...
存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 1.9.2 准备 create table t_user ( username varchar2(20), ...
这样,如果需要编译所有工程条目或者将工程从某个位置或数据库移动到其他位置时,所需工程条目的查找就变得比较简单, <br>To-Do条目——您可以在任何SQL或PL/SQL源文件中使用To-Do条目快速记录该文件中那些需要...
ORDER BY子句中使用的列将是为显示所选择的列,但是实际上并不一定要这样,用非检索的列排序数据是完全合法的。 为了按多个列排序,列名之间用逗号分开。 2、支持按相对列位置进行排序。 输入 SELECT prod_id,...
在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后...
在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db ...
尽量避免使用游标,游标效率很差,如果使用游标就不要在游标的循环中使用表连接操作 注意where语句的写法,应该根据索引的顺序、范围的顺序、范围的大小来确定条件子句的前后顺序,尽量让字段顺序与索引顺序...
elizabeth o'neil is a professor of computer science at the university of massachusetts at boston.she serves as a comsult-ant to sybase iqin concor, massachusetts,and has worked with a number of ...
使用CTE能改善代码可读性,且不损害其性能。 递归CTE是SQL SERVER 2005中重要的增强之一。一般我们在处理树,图和层次结构的问题时需要用到递归查询。 CTE的语法如下 WITH CTE AS ( SELECT EmpId, ReportTo, ...
QSV通过 SQL 查询 CSV 的高性能 CLI 工具安装克隆存储库后,您可以使用cargo install --path .在本地安装二进制文件cargo install --path .特征用途/特点目的是使用 CSV 作为数据源支持所有 SQLite 语法,包括:连接...
如果用SQL下面的CTE递归处理的话,一次性就能把结果给查询出来,而且性能很不错 比用程序处理(数据量很大的情况),临时表性能更好,更方便 代码如下: with area as( select *,id px,cast(id as nvarchar(4000)) ...
Grasp modern SQL techniques to optimize complex SQL queries Identify and simplify overly complex JavaScript code Explore and implement UI design principles that effectively enhance the performance ...