SQL优化一例

      今天周会上有同事谈到某一个系统中查询报错了，错误信息为“数据提供程序或其他服务返回E_FAIL状态。”，同事反应就是在查某一个月的数据时才会出现，领导叫我协助解决。

      数据库是SQLSERVER 2008 R2的，我先在网上查了这个错误信息，但并没找到满意答案，但手工执行查询语句发现居然要45秒才能执行完，再一看SQL语句，同一张表居然重复查了5次，而这张表有10多万条记录，马上觉得这SQL有问题，根据我的ORACLE经验，这肯定可以用decode进行行转列解决的，在sqlserver中应当是case when，于是进行了优化，执行时间变成了0.11秒，程序运行也不报错了。解决方法就是把重复查同一张表的部分使用了case when进行行转列，这样原来那张表就只需要查一次了。

     优化前SQL

select a.tollyear,a.tollmonth,a.tollformoid,y.status,d.seatno,c.name as customername,
coalesce(g.cost,0) as ws,(coalesce(e.cost,0)) as glhj, (coalesce(f.cost,0)) as zjhj,
(coalesce(i.cost,0)+coalesce(k.cost,0)) as wehj, (coalesce(e.zlj,0)) as glzlj,(coalesce(f.zlj,0)) as zjzlj,
(coalesce(i.zlj,0)+coalesce(k.zlj,0)) as wezlj,coalesce(g.zlj,0) as wszlj
from sf_tollform a inner join customer c on a.customeroid=c.customeroid
inner join storefront d on a.storefrontoid=d.storefrontoid
inner join contract y on a.contractoid=y.contractoid
left join sf_tollformitem e on a.tollformoid=e.tollformoid and e.feeitemoid=1 and e.status=0
left join sf_tollformitem f on a.tollformoid=f.tollformoid and f.feeitemoid=2 and f.status=0
left join sf_tollformitem i on a.tollformoid=i.tollformoid and i.feeitemoid=5 and i.status=0
left join sf_tollformitem g on a.tollformoid=g.tollformoid and g.feeitemoid=3 and g.status=0
left join sf_tollformitem k on a.tollformoid=k.tollformoid and k.feeitemoid=11 and k.status=0
where (coalesce(e.cost,0)+coalesce(f.cost,0)+coalesce(g.cost,0)+coalesce(i.cost,0)+coalesce(k.cost,0)+
coalesce(e.tq,0)+coalesce(f.tq,0)+coalesce(g.tq,0)+coalesce(i.tq,0)+coalesce(k.tq,0)) > 0
and a.tollyear=2013 and a.tollmonth=1
order by d.seatno
 

优化后SQL

select a.tollyear,a.tollmonth,a.tollformoid,y.status,d.seatno,c.name as customername,
e.ws,e.glhj,e.zjhj,e.wehj,e.glzlj,e.zjzlj,e.wezlj,e.wszlj
from sf_tollform a inner join customer c on a.customeroid=c.customeroid
inner join storefront d on a.storefrontoid=d.storefrontoid
inner join contract y on a.contractoid=y.contractoid
left join
(select m.tollformoid,m.tollyear,m.tollmonth,sum(n.wsh) ws,sum(n.glh) glhj,sum(n.zjh) zjhj,sum(n.sh+n.dh) wehj,
sum(n.glzlj) glzlj,sum(n.zjzlj) zjzlj,sum(n.szlj+n.dzlj) wezlj,sum(n.wszlj) wszlj,SUM(n.tq) tq,sum(n.glh+n.zjh+n.wsh+n.sh+n.dh+n.glzlj+n.zjzlj+n.wszlj+n.szlj+n.dzlj+n.tq) zhy
from sf_tollform m,
(select tollformoid,isnull(tq,0) tq,feeitemoid,zlj,
(case feeitemoid when 1 then isnull(cost,0) else 0 end) as glh ,
(case feeitemoid when 2 then isnull(cost,0) else 0 end) as zjh ,
(case feeitemoid when 3 then isnull(cost,0) else 0 end) as wsh ,
(case feeitemoid when 5 then isnull(cost,0) else 0 end) as sh ,
(case feeitemoid when 11 then isnull(cost,0) else 0 end) as dh,
(case feeitemoid when 1 then isnull(zlj,0) else 0 end) as glzlj ,
(case feeitemoid when 2 then isnull(zlj,0) else 0 end) as zjzlj ,
(case feeitemoid when 3 then isnull(zlj,0) else 0 end) as wszlj ,
(case feeitemoid when 5 then isnull(zlj,0) else 0 end) as szlj ,
(case feeitemoid when 11 then isnull(zlj,0) else 0 end) as dzlj
from  sf_tollformitem where STATUS=0) n
where n.tollformoid= m.tollformoid
group by m.tollformoid,m.tollyear,m.tollmonth) e on e.TOLLFORMOID=a.TOLLFORMOID
where e.TOLLYEAR=a.TOLLYEAR and e.TOLLMONTH = a.TOLLMONTH and e.zhy > 0 and a.tollyear=2013 and a.tollmonth=1
order by d.seatno