本人在执行如下查询时,当companycode和 a_year 发生变化时,有时很快不到1秒, 有时长达2分钟多。另外已经对
cvdatevno字段建立了索引, 2011年以前,查询一直正常, 2011年8月 发现有不正常的慢。 请高手帮忙,谢谢! SELECT f_voucher.cVnote,
f_voucher.cItemlcode,
f_voucher.iVsum_d,
f_voucher.iVsum_c,
f_voucher.autoid,
f_voucher.companycode,
f_voucher.a_year,
f_voucher.cVno,
f_voucher.dVdate,
f_voucher.cItemcode,
f_voucher.cV_c_d,
f_voucher.cDepcode1,
f_voucher.cDepcode2,
f_voucher.cDepcode3,
f_voucher.cDepcode4,
f_voucher.cDepcode5,
f_voucher.cDepcode6,
f_voucher.cDepcode7,
f_voucher.cFlag,
f_voucher.iVrate,
f_voucher.iVamount_d,
f_voucher.iVamount_c,
f_voucher.cUnit,
f_voucher.iVfsum_d,
f_voucher.iVfsum_c,
f_voucher.cVsn,
f_voucher.cbillno,
f_voucher.cDepcode8,
f_voucher.cName_do
FROM f_voucher
where cvdatevno in (select cvdatevno from f_voucher where f_voucher.companycode='KT1' and f_voucher.a_year=2011 and ((left(citemcode_au,4) in ('1001'))
or (left(citemcode_au,4) in ('1002')))) and f_voucher.companycode='KT1' and f_voucher.a_year=2011 order by month(dVdate),cast(cvno as int),cast(cvsn as int)
cvdatevno字段建立了索引, 2011年以前,查询一直正常, 2011年8月 发现有不正常的慢。 请高手帮忙,谢谢! SELECT f_voucher.cVnote,
f_voucher.cItemlcode,
f_voucher.iVsum_d,
f_voucher.iVsum_c,
f_voucher.autoid,
f_voucher.companycode,
f_voucher.a_year,
f_voucher.cVno,
f_voucher.dVdate,
f_voucher.cItemcode,
f_voucher.cV_c_d,
f_voucher.cDepcode1,
f_voucher.cDepcode2,
f_voucher.cDepcode3,
f_voucher.cDepcode4,
f_voucher.cDepcode5,
f_voucher.cDepcode6,
f_voucher.cDepcode7,
f_voucher.cFlag,
f_voucher.iVrate,
f_voucher.iVamount_d,
f_voucher.iVamount_c,
f_voucher.cUnit,
f_voucher.iVfsum_d,
f_voucher.iVfsum_c,
f_voucher.cVsn,
f_voucher.cbillno,
f_voucher.cDepcode8,
f_voucher.cName_do
FROM f_voucher
where cvdatevno in (select cvdatevno from f_voucher where f_voucher.companycode='KT1' and f_voucher.a_year=2011 and ((left(citemcode_au,4) in ('1001'))
or (left(citemcode_au,4) in ('1002')))) and f_voucher.companycode='KT1' and f_voucher.a_year=2011 order by month(dVdate),cast(cvno as int),cast(cvsn as int)
把所有 f_voucher 改为 f_voucher (nolock)试试,
速度应该就一样了.
另外,试了下面的办法 ,不行!
时快时慢,应该是锁争引起的,
把所有 f_voucher 改为 f_voucher (nolock)试试,下面是 查询慢的 执行计划
http://hiphotos.baidu.com/onedaysoft/pic/item/86877a0010a7bcbe267fb556.jpg下面是 查询快的 执行计划http://hiphotos.baidu.com/onedaysoft/pic/item/85ca05182f3b0d12dbb4bd4d.jpg