周更新”,甚至改为规模更小的“每日更新”,或许是个有效方案。
总结:几千个语句,借助游标(cursor)不断循环,很慢。换成几个语句,处理同样的数据,
还是较慢。换成一个语句,解决上述问题,最好。
动作丰富的SQL
语句
Action-Packed
SQL
Statcomnts
SQL 不是过程xìng语言(procedural language),尽管也可以将过程逻辑(procedural logic)用于SQL,
但必须小心。混淆声明xìng处理(declarative processing)和过程逻辑,最常见的例子出现在需要
从数据库中提取数据、然后处理数据、然后再chā入到数据库时。在一个程序(或程序中的一个
函数)接收到特定输入值后,如下情况太常见了:用输入值从数据库中检索到一个或多个另外
的数据值,然后,借助循环或条件逻辑(通常是if ... then ... else)将一些语句组织起来,对数
据库进行cāo作。大多数情况下,造成上述错误做法的原因有三:根深蒂固的坏习惯、SQL知识
的缺乏、盲从功能需求规格说明。其实,许多复杂cāo作往往可由一条SQL 语句完成。因此,
如果用户提供了一些数据值,尽量不要将cāo作分解为多条提取中间结果的语句。
避免在SQL 中引入“过程逻辑(procedural logic)”的主要原因有二。
数据库访问,总会跨多个软件层,甚至包括网络访问。
即使没有网络访问,也会涉及进程间通讯;额外的存取访问意味着更多的函数调用、更大的带
宽,以及更长的等待时间。一旦这些调用要重复多次,其对xìng能的影响就非常可观了。
在SQL中引入过程逻辑,意味着xìng能和维护问题应由你的程序承担。
大多数据库系统都提供了成熟的算法,来处理join等cāo作,来优化查询以获得更高的效率。基于
开销的优化器(cost-based optimizer,CBO)是很复杂的软件,它早已不像刚推出时那样没什么
用了,而在大部分情况下都是非常出色的成熟产品了,优秀的CBO 查询优化的效率极高。然而,
CBO 所能改变的只有SQL 语句。如果在一条单独的SQL语句中完成尽可能多的cāo作,那么xìng
能优化可以还由DBMS 核心负责,你的程序可以充分利用DBMS的所有升级。也就是说,未
来大部分维护工作从程序间接转移给了DBMS 供货商。
当然,“避免在SQL 中引入过程逻辑”规则也有例外。有时过程逻辑确实能加快处理速度,庞
大的SQL语句未必总是高效。然而,过程逻辑及其之后的处理相同数据的语句,可以编写到一
个单独的SQL 语句中,CBO 就是这么做的,从而获得最高效的执行方式。
总结:尽可能多地把事情jiāo给数据库优化器来处理。
充分利用每次数据库访问
Profitable
Database
Accesses
如果计划逛好几家商店,你会首先决定在每家店买哪些东西。从这一刻起,就要计划按何种顺
序购物才能少走冤枉路。每逛一家店,计划东西购买完毕,才逛下一家。这是常识,但其中蕴
含的道理许多数据库应用却不懂得。
要从一个表中提取多段信息时,采用多次数据库访问的做法非常糟糕,即使多段信息看似“无关”
(但事实上往往并非如此)。例如,如果需要多个字段的数据,千万不要逐个字段地提取,而应
一次cāo作全部完成。
很不幸,面向对象(OO)的最佳实践提倡为每个属xìng定义一个get方法。不要把OO 方法与关
系数据库处理混为一谈。混淆关系和面向对象的概念,以及将表等同于类、字段等同于属xìng,
都是致命的错误。
总结:在合理范围内,利用每次数据库访问完成尽量多的工作。
接近DBMS
核心
Closeness
to
the
DBMS
Kernel
代码的执行越接近DBMS 核心,则执行速度越快。数据库真正强大之处就在于此,例如,有些
数据库管理产品支持扩展,你可以用C等较底层的语言为它编写新功能。用含有指针cāo作的底
层语言有个缺点,即一旦指针处理出错会影响内存。仅影响到一个用户已很糟糕,何况数据库
服务器(就像“服务器”名字所指的一样)出了问题会影响众多“用户”——服务器内存出了问题,
所有使用这些数据的无辜的应用程序都会受影响。因此,DBMS 核心采取了负责任的做法,在
沙箱(sandbox)环境中执行程序代码,这样,即使出了问题也不会影响到数据。例如,Oracle 在
外部函数(external function)和它自身之间实现了一套复杂的通信机制,此机制在某些方面很
像控制数据库连结的方法,以管理两个(或多个)服务器上的数据库实例之间的通信。到底采
用PL/SQL 存储过程还是外部C 函数,应综合比较后决定。如果精心编写外部C 函数获得的
好处超过了建立外部环境和上下文切换(context-switching)的成本,就应采用外部函数。但需
要处理一个大数据量的表的每一行时,不要使用外部函数。这需要平衡考虑,解决问题时应完
全了解备选策略的后果。
如要使用函数,始终应首选DBMS自带的函数。这不仅仅是为了避免无谓的重复劳动,还因为
自带函数在执行时比任何第三方开发的代码更接近数据库核心,相应地其效率也会高出许多。
下面这个简单例子是用Oracle SQL编写的,显示了使用Oracle 函数所获得的效率。假设手工
输入的文本数据可能包含多个相邻的“空格”,我们需要一个函数将多个空格
替换为一个空格。如果不采用Oracle Database 10g 开始提供的正规表达式(regular expression),
函数代码将会是这样:
create or replace function squeeze1(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := '';
c_char char(1);
n_len number := length(p_string);
i binary_integer := 1;
j binary_integer;
begin
while (i <= n_len)
上述代码中的'X' 在内层循环中被串接到字符串上,以避免超出字符串长度的测试。
还有别的方法消除多个空格,可以使用Oracle 提供的字符串函数。以下为替代方案:
loop
c_char := substr(p_string, i, 1);
v_string := v_string || c_char;
if (c_char = ' ')
then
j := i + 1;
while (substr(p_string || 'X', j, 1) = ' ')
loop
j := j + 1;
end loop;
i := j;
else
i := i + 1;
end if;
end loop;
return v_string;
end;
/
create or replace function squeeze2(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
i binary_integer := 1;
begin
i := instr(v_string, ' ');
while (i > 0)
loop
v_string := substr(v_string, 1, i)
|| ltrim(substr(v_string, i + 1));
i := instr(v_string, ' ');
end loop;
return v_string;
end;
/
还有第三种方法:
用一个简单的例子对上述三种方法进行测试,每个函数都能正确工作,且没有明显的xìng能差异:
create or replace function squeeze3(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
len1 number;
len2 number;