碰到一个很奇怪的问题
用DELPHI2007做了一个数据库查询的窗口,
窗口控件包括:ADOSTOR,DateSource,cxgird,其他的button,label ,text框之类的使用SQL存储过程,(存储过程就是SELECT 比较多,有些嵌套,但都不复杂,数据量也不多)
存储过程正常,在SQL的查询分析器里运行也正常,执行时间大概是10-20秒左右但奇怪的是,用DELPHI里,ADOSTOR.EXEC后,SQL的执行速度就变的超级的慢,大概要10分钟左右才能执行完毕
而且,在这10分钟里缓慢的刷新过程中,我用SQL查询分析器也再次执行这条存储过程,此时查询分析器也运行得很缓慢,数据是一条一条慢慢显示出来的这个是什么情况,把程序独立做个运行的窗口,东西都最简化了,还是这样~~~~
用DELPHI2007做了一个数据库查询的窗口,
窗口控件包括:ADOSTOR,DateSource,cxgird,其他的button,label ,text框之类的使用SQL存储过程,(存储过程就是SELECT 比较多,有些嵌套,但都不复杂,数据量也不多)
存储过程正常,在SQL的查询分析器里运行也正常,执行时间大概是10-20秒左右但奇怪的是,用DELPHI里,ADOSTOR.EXEC后,SQL的执行速度就变的超级的慢,大概要10分钟左右才能执行完毕
而且,在这10分钟里缓慢的刷新过程中,我用SQL查询分析器也再次执行这条存储过程,此时查询分析器也运行得很缓慢,数据是一条一条慢慢显示出来的这个是什么情况,把程序独立做个运行的窗口,东西都最简化了,还是这样~~~~
在SQL的查询分析器里运行也正常,执行时间大概是10-20秒左右
如按你說的查詢不復雜,數據量也不大,一個過程需要10-20秒也是有問題的。
cxgrid字段是动态创建还是手动创建好了?cxgrid动态创建字段的话也会影响速度
就拖了ADO控件跟DateSource
CXGRID都不做链接,手动点 active 为true时,都要等10分钟应该不是存储问题吧,在SQL的查询分析器里是正常的,时间很短,不查全年的,几秒就完成了--所有客户查询
alter procedure [dbo].[import_all_custom]
@begin_time Datetime,
@end_time Datetime,
@cInvDefine2 varchar(10)=null,
@ccusdefine9 varchar(20)=null,
@cccname varchar(98)=null
as
declare
@STime varchar(10),
@ETime varchar(10)
begin
set @STime=CONVERT(varchar(10),@begin_time,120)
set @ETime=CONVERT(varchar(10),@end_time,120)
if @cInvDefine2 not in('N','T','K','G','D','C')
set @cInvDefine2=null
if @ccusdefine9 not in('历史遗留客户','1型客户','N型客户','T型客户')
set @ccusdefine9=null
if @cccname not in(select cccname from CustomerClass where iCCGrade=2 group by cccname)
set @cccname=null
select sf.cccname as sf,
sq.cccname as sq,
kh.ccuscode as ccuscode,
kh.ccusname as khmc,
kh.ccusdefine9 as ccusdefine9,
bc.fhsl,
bc.fh_d2,
bc.fh_d3,
bc.fh_d4,
bc.xssl,
bc.xs_d2,
bc.xs_d3,
bc.xs_d4,
c.qcqkye,
j.xsje1 ,
k.hlje,
(case when j.xsje1=0 then null else (k.hlje/j.xsje1*100) end) as hll,
i.qkye,
kh.iCusCreLine,
kh.cCusCreGrade,
(case when kh.iCusCreLine=0 then null else kh.iCusCreLine-i.qkye end) as ed
from(--客户名称,信用项目
select ccuscode,ccccode,ccusname,icuscreline,ccuscregrade,ccusdefine9
from customer
where (@ccusDefine9 is null or ccusdefine9=@ccusdefine9)
group by ccuscode,ccccode,ccusname,icuscreline,ccuscregrade,ccusdefine9
) kh left join
(--销售发货数量
select a.ccuscode,
b.fhsl,
b2.fh_d2,
b3.fh_d3,
b4.fh_d4,
c.xssl,
c2.xs_d2,
c3.xs_d3,
c4.xs_d4
from customer a left join
(select ccuscode,sum(iquantity) as fhsl
from DispatchList a inner join DispatchLists b on a.DLID=b.DLID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by ccuscode
) b on a.ccuscode=b.ccuscode left join
(select ccuscode,sum(iquantity) as fh_d2
from DispatchList a inner join DispatchLists b on a.DLID=b.DLID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='2'
group by ccuscode
) b2 on a.ccuscode=b2.ccuscode left join
(select ccuscode,sum(iquantity) as fh_d3
from DispatchList a inner join DispatchLists b on a.DLID=b.DLID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='3'
group by ccuscode
) b3 on a.ccuscode=b3.ccuscode left join
(select ccuscode,sum(iquantity) as fh_d4
from DispatchList a inner join DispatchLists b on a.DLID=b.DLID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='4'
group by ccuscode
) b4 on a.ccuscode=b4.ccuscode left join
(select ccuscode,sum(iquantity) as xssl
from SaleBillVouch a inner join SaleBillVouchs b on a.SBVID=b.SBVID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by ccuscode
) c on a.ccuscode=c.ccuscode left join
(select ccuscode,sum(iquantity) as xs_d2
from SaleBillVouch a inner join SaleBillVouchs b on a.SBVID=b.SBVID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='2'
group by ccuscode
) c2 on a.ccuscode=c2.ccuscode left join
(select ccuscode,sum(iquantity) as xs_d3
from SaleBillVouch a inner join SaleBillVouchs b on a.SBVID=b.SBVID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='3'
group by ccuscode
) c3 on a.ccuscode=c3.ccuscode left join
(select ccuscode,sum(iquantity) as xs_d4
from SaleBillVouch a inner join SaleBillVouchs b on a.SBVID=b.SBVID left join inventory c on b.cinvcode=c.cinvcode
where ddate between @STime and @ETime and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2) and rtrim(cinvdefine3)='4'
group by ccuscode
) c4 on a.ccuscode=c4.ccuscode
)bc on bc.ccuscode=kh.ccuscode left join
(--期初欠款余额
select cdwcode as ccuscode,
sum(idamount-icamount) as qcqkye
from ap_detail a left join inventory b on a.cinvcode=b.cinvcode
where cflag='AR' and iflag=0 and dvouchdate<@STime --and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by cdwcode
) c on c.ccuscode=kh.ccuscode left join
(--销售金额
select isnull(x.ccuscode,isnull(y.ccusven,z.cdwcode)) as ccuscode,isnull(x.amount,0)-isnull(y.amount,0)+isnull(z.amount,0) as xsje1
from (select cCusCode,sum(isnull(inatsum,0)) as amount
from tuhz_Bill
where (dDate between @STime and @ETime) and cInvdefine2 in('N','T','K','G','D','C') and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by cCusCode
) x full JOIN
(select cCusVen,sum(isnull(iamt_f,0)) as amount
from tuhz_closebill
where ((dVouchDate between @STime and @ETime )and cKm='1135' and cSSName='返利' )
group by cCusVen
) y ON x.cCusCode=y.cCusVen full JOIN
(select cDwCode,sum(isnull(iamount_f,0)) as amount
from tuhz_apvouch
where dVouchDate between @STime and @ETime
group by cDwCode
) z ON x.cCusCode=z.cDwCode
)j on j.ccuscode=kh.ccuscode left join
(--回笼金额
select ccuscode,sum(icamount) as hlje
from ap_detail a left join customer b on a.cdwcode=b.ccuscode left join inventory c on a.cinvcode=c.cinvcode
where cflag='AR' and iflag=0 and dvouchdate between @STime and @ETime --and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by ccuscode
)k on k.ccuscode=kh.ccuscode left join
(--欠款余额
select cdwcode as ccuscode,sum(idamount-icamount) as qkye
from ap_detail a left join customer b on a.cdwcode=b.ccuscode left join inventory c on a.cinvcode=c.cinvcode
where cflag='AR' and iflag=0 and dvouchdate<=@ETime --and (@cInvDefine2 is null or cinvdefine2=@cInvDefine2)
group by cdwcode
)i on i.ccuscode=kh.ccuscode left join
(select cCCCode,cCCName,iCCGrade,bCCEnd
from CustomerClass
where iCCGrade=2
)sf on left(kh.ccccode,3)=sf.ccccode left join
(select cCCCode,cCCName,iCCGrade,bCCEnd
from CustomerClass
--where iCCGrade=3
) sq on kh.ccccode=sq.ccccode
where sf.cccname=@cccname or @cccname is null
order by bc.fhsl desc,bc.xssl descend
可以把 cxGrid1DBTableView --> DataController --> DataModeController 的 GridMode 设置为 True
代码可以适当修改一下:
TableView.DataController.BeginUpdate;
try
//ShowData;
finally
TableView.DataController.EndUpdate;
end;