在生成报表时,客户感觉太慢,于是,各处检查,看可否提示效率。界面上的都改进了,提升不大。如是在SQL 语句上下功夫。 .:!*/s z]mNAPRWM:
语句(3) vWPsqHn5
c;5k+so
declare @operatorName nvarchar(50) R9c+o|~
/W\pVHO%^
set @operatorName = '%' eCq|4IGq
DGHuZ?(f,
select distinct item.* from item inner join customer_item 163U!o
Df<`Z&g<
on item.itemcode = customer_item.itemCode MBK}|>NP
f~ "\I
inner join customer_operator on customer_item.customerCode = customer_operator.customerCode !<Ti>)1,
@lY{1jy&l
inner join operator on customer_operator.operatorId = operator.operatorId QEGz$QHv
3 8E~'as
where operator.operatorName like @operatorName lf8,[>t$
H 4[Hh!sm
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0 q?boj0Ef
yE H:)F
and customer_operator.operatorId = customer_operator.operatorId lqEU)z&8J
Xc:Q>9x'j
所用时间和结果都为74行,时间0:00:01。 x#HX5T
zb}HC v\
将语句(1)中的去掉该条件后成为语句(4): @VE=7h{
Fm-vz1c
declare @operatorName nvarchar(50) k8EhAKqgA
Y;'KS#B
set @operatorName = '%' JmUg#H&
\nM.6&L`#H
select distinct item.* from item , customer_item , customer_operator ,operator =l!"%e|H
?%!-t,< P
where item.itemcode = customer_item.itemCode RwZ^{
{kSgF%$y
and customer_item.customerCode = customer_operator.customerCode F1H m7x=
p(](
--and customer_operator.operatorId = customer_operator.operatorId kr%4Ahy%
/za4I-H T
and operator.operatorName like @operatorName 10Z~169=
E%sJ2U
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0 d,__Y*
)2)gJBY
时间和结果为74行,时间0:00:01。终于发现了些他们的差异。 O<14"Cd,
mk,YA-
结论 1<h\w~D+S
Q1hp3J^
尽量使用Join 而不是Where来列出关联条件,特别是多个表联合的时候。 N9roo?,U
,Ot 0]d
原因是: /)`B nld
lV YE()h
(1)在效率上,Where可能具有和Inner join一样的效率。但基本可以肯定的(通过SQLServer帮助和其它资料,以及本测试)是Join的效率不比Where差。 " G)T 3Y^
x[UED^B
(2)使用Join可以帮助检查语句中的无效或者误写的关联条件