sql 日期索引

最近给一个煤炭企业运销系统维护，数据库用的是sqlserver。听用户说有个模块打开需要3分钟，启用sqlserver跟踪发现确实时间很长，所以找出相关sql

select * from hyxgb 
left join (
	 select
	 mzb.mzmc,dxtzd.mzbh,kbb.dwmc,mzb.dwbh,
	 convert(varchar,dhzcb.zcrq,23)as zcrq,khxxb.khmc as dz,
	 sum(jz)as ds,dhzcb.hybh
	 from dhzcb
	 left join dxtzd on dhzcb.dxdh=dxtzd.id
	 left join mzb on dxtzd.mzbh=mzb.mzbh
	 left join kbb on mzb.dwbh=kbb.dwbh
	 left join khxxb on dxtzd.khbh=khxxb.khid 
	 where hybh is not null and hybh !=''
	 group by
	 mzb.mzmc,dxtzd.mzbh,kbb.dwmc,mzb.dwbh,
	 convert(varchar,dhzcb.zcrq,23),
	 dhzcb.hybh,khxxb.khmc
	 union all
	 select
	 mzb.mzmc,gdzcb.mzbh,kbb.dwmc,mzb.dwbh,
	 convert(varchar,gdzcb.zcrq,23),tljhb.dz,
	 sum(jfzl)ds,gdzcb.hybh
	 from gdzcb
	 left join mzb on gdzcb.mzbh=mzb.mzbh
	 left join kbb on mzb.dwbh=kbb.dwbh
	 left join qccsb on gdzcb.qccbh=qccsb.qccbh
	 left join tljhb on qccsb.jhbh=tljhb.jhbh
	 where hybh is not null and hybh !=''
	 group by
	 mzb.mzmc,gdzcb.mzbh,kbb.dwmc,mzb.dwbh,
	 convert(varchar,gdzcb.zcrq,23),tljhb.dz,gdzcb.hybh
 )zcb on hyxgb.hybh=zcb.hybh
 left join mzb on hyxgb.mzbh=mzb.mzbh
 left join kbb on hyxgb.dwbh=kbb.dwbh
 where hyxgb.dwbh like '%' and hyxgb.cyrq between '2012-08-01' and '2012-08-25' and hyxgb.mzbh like '%'
 order by cyrq desc,kbb.dwmc,fyfs,mzb.mzmc

这么长的sql，问题出在哪呢？分析其执行计划，发现where条件中的日期过滤太慢，所以检查hyxgb表cyrq是否有索引，发现真的没有索引，加上索引时间缩短到4s。

索引分为聚集索引和非聚集索引，而一个表上只能有一个聚集所以，所以这个日期我建成了非聚集索引，对于日期来说应该建成聚集索引。