id	summary	reporter	owner	description	type	status	priority	milestone	component	version	resolution	keywords	cc	due_date
73	排行榜sql语句优化	yuanhuoqing	yuanhuoqing	"== 现象 ==
  对dba提供过来的耗资源的sql进行分析发现类似下面sql重复率比较高
{{{
select *
from (
select f.name as variance_name, a.*,(a.last_count_order-a.count_order) as rise_diff ,
b.name as t_name,b.parent_id as t_parent_id,b.forum_dir,b.forum_id as t_forum_id,
b.pub_url as t_pub_url,b.pub_dir ,d.price as area_price , 
nvl(e.min_price,0) as min_price, nvl(e.max_price,0) as max_price
from PDL_PRODUCT a, PDL_PRODUCT_TYPE b , (
SELECT * 
FROM PP_PRICE
WHERE PRICE BETWEEN 1000 AND 4500 AND AREA_ID = 100) d,eml_price e ,pdl_product_variance f
where a.status!=0 and a.review_status<>4 and a.order_type<2 and a.concept is null 
and a.type_id=b.id and b.status=1 and b.type=3 and b.parent_id=20937 and a.type_id= 47071 
and a.id=d.product_id and e.product_id(+)= a.id and a.variance_id=f.id(+) order by a.last_count_order asc)
where rownum <= 10
}}}
     

== 方案 ==
   这个sql都是查全国指导价价格区间的排行榜，根据崇绮的建议，全国指导价没必要联pp_price表查询了，直接用PDL_PRODUCT的price可以了，并对价格等没有参数化条件参数化，优化的sql如下：
{{{
select *
from (
select f.name as variance_name, a.*,(a.last_count_order-a.count_order) as rise_diff ,
b.name as t_name,b.parent_id as t_parent_id,b.forum_dir,b.forum_id as t_forum_id,
b.pub_url as t_pub_url,b.pub_dir ,a.price as area_price , 
nvl(e.min_price,0) as min_price, nvl(e.max_price,0) as max_price
from PDL_PRODUCT a, PDL_PRODUCT_TYPE b ,eml_price e ,pdl_product_variance f
where a.status!=0 and a.review_status<>4 and a.order_type<2 and a.concept is null 
and a.price  BETWEEN 1000 AND 45000
and a.type_id=b.id and b.status=1 and b.type=3 and b.parent_id=20937 and a.type_id= 47071 
and e.product_id(+)= a.id and a.variance_id=f.id(+) order by a.last_count_order asc)
where rownum <= 10
}}}

    根据dba对这两条sql的分析，优化后的sql可以提高14%左右的性能，
     因此修改对应的程序，把查全国报价价格区间的排行榜的程序作相应的调整。

== 结果 ==
   在修改程序过程中发现现有程序都是把按价格区间、品牌专区、属性来排行等不同业务的sql拼装混在一起，无法抽取出来。只能在原来恶心的sql上继续拼装。拼装sql是无法避免的，建议不要把很多业务的sql拼装混在一起，也就是每个方法负责单一的业务职责，这样方便后续的优化和代码的可维护性比较强。

== 继续优化 ==
原来有排行榜的冗余表，后来没有继续用，使用排行榜冗余表会快很多。
"	优化	closed	major		产品库	产品库3.0	fixed	数据库优化，sql优化		06/12/2011