关于fastreport问题，与SQL SERVER有关，非常急，请大侠帮忙，不胜感激！(200分)

本人用DELPHI程序，报表程序用fastreport，做了一个报表，调用的是
SQL SERVER2000后台数据库，主要是通过存储过程从其他报表中采集数据，
然后通过fastreport形成报表。但从DELPHI程序形成的应用程序中，
打开报表时，弹出一个对话框“超时，已过期”，然后此报表，就
不能打开了。
因为我报表形成主要是通过SQL SERVER2000的存储过程，
所以当我用SQL SERVER2000的查询分析器，调用此程序有关的
存储过程时，共花了1分多时间，才执行完该存储过程，如果我用同一个
存储过程，采集另一个数据量小一点的表，执行完该存储过程花了30多秒，
就能打开报表了。请问高手，这是什么原因呢，跟DELPHI或SQL SERVER2000的
设置有关吗？或者是我的存储过程太冗余，需要优化？
非常感谢！

没见过！[]
顶

嘗試﹕
1.你的SQL Server的查詢等待時限設定是多少??
2.你是用什么引擎連接數據庫的?BDE??ADO??ODBC???

不知道你的数据量有多大，依我愚见：1分多钟时间才执行1个存储过程，那客户得有良好的
心理素质。建议将你的存储过程贴到这里，大家一起想想办法。
另外你提到“执行完该存储过程花了30多秒，就能打开报表了”，这可能是FR的问题，我再
查查。
to Jelly0228
SQL SERVER2000引擎应该用ADO吧。

回答Jelly0228问题1.请问SQL Server的查詢等待時限如何設定?
2。我是用ADO连接的。
请问ty123，“你提到“执行完该存储过程花了30多秒，就能打开报表了”，这可能是FR的问题，我再查查。” 请问是什么问题，查出来了吗？
谢谢！

存储过程如下：
ALTER proc bpjcc
@date datetime,
@wh_name varchar(20)
as
declare @wh char(4)
select @wh=wh from my_wh where name=@wh_name
DELETE MrpCyTmp
Insert Into MrpCyTmp(PRD_NO,WH,PRD_MARK,UNIT,RC_ID,QTY1,TrueQty1_QTY1,CST1,DEP)
Select PRD_NO,WH,PRD_MARK,'1' As UNIT,'3' As RC_ID,QTY,QTY1,CST,'SP'as dep
From SPRD Where (YY=DATEPART(YEAR,@date)And(MM=isnull(datepart(month,@date)-1,12))) and wh=@wh
/*本日销货,销货客户为N9999表示委外出库，其它情况为国内销售*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,QTY1,TrueQty1_QTY1,CST1,QTY2,TrueQty1_QTY2,CST2,AMTN2,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,'') as PRD_MARK,WH,BAT_NO,UNIT,PS_NO,ITM,RC_ID=(case when PS_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
QTY1=(case when PS_ID ='PB' then
-QTY when PS_ID='PC' then
Qty else
0 end),
TrueQty1_QTY1=(case when PS_ID ='PB' then
-QTY1 when PS_ID='PC' then
Qty1 else
0 end),
CST1=(case when PS_ID in ('PB','PD') then
-isnull(AMTN_NET,0)-isnull(AMTN_EP,0) when PS_ID='PC' then
isnull(AMTN_NET,0)+isnull(AMTN_EP,0) else
0 end),
QTY2=(case when PS_ID='SB' then
-QTY when PS_ID='SA' then
Qty else
0 end),
TrueQty1_QTY2=(case when PS_ID='SB' then
-QTY1 when PS_ID='SA' then
Qty1 else
0 end),
CST2=(case when PS_ID='SB' then
-CSTN_SAL when PS_ID='SA' then
CSTN_SAL else
0 end),
AMTN2=(case when PS_ID in ('SB','SD') then
-AMTN_NET when PS_ID='SA' then
AMTN_NET else
0 end),
DEP=(case (select cus_no from mf_pss where (ps_id=tf_pss.ps_id) and (ps_no=tf_pss.ps_no))
when 'N9999' then
'1' else
'2' end),
REF_ID=(case when PS_ID='PC' then
'PC' when PS_ID='PB' then
'PB' when PS_ID='PD' then
'PD' when PS_ID='SA' then
'SA' when PS_ID='SB' then
'SB' when PS_ID='SD' then
'SD' end)
From TF_PSS Where (DATEDIFF(day,PS_DD,@date) <=0)And(DATEDIFF(day,PS_DD,@date) >=0)
and Exists(Select CHK_MAN From MF_PSS where (PS_ID=TF_PSS.PS_ID)and(PS_NO=TF_PSS.PS_NO)and(CHK_MAN<>'')and(CHK_MAN is not null)) and wh=@wh
/*累计销货*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,SUM_QTY1,SUM_TrueQty1_QTY1,CST1,SUM_QTY2,SUM_TrueQty1_QTY2,CST2,AMTN2,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,'') as PRD_MARK,WH,BAT_NO,UNIT,PS_NO,ITM,RC_ID=(case when PS_DD<dateadd(day,1-datepart(day,@date),@date)then
'0' else
'1' end ),
QTY1=(case when PS_ID ='PB' then
-QTY when PS_ID='PC' then
Qty else
0 end),
TrueQty1_QTY1=(case when PS_ID ='PB' then
-QTY1 when PS_ID='PC' then
Qty1 else
0 end),
CST1=(case when PS_ID in ('PB','PD') then
-isnull(AMTN_NET,0)-isnull(AMTN_EP,0) when PS_ID='PC' then
isnull(AMTN_NET,0)+isnull(AMTN_EP,0) else
0 end),
QTY2=(case when PS_ID='SB' then
-QTY when PS_ID='SA' then
Qty else
0 end),
TrueQty1_QTY2=(case when PS_ID='SB' then
-QTY1 when PS_ID='SA' then
Qty1 else
0 end),
CST2=(case when PS_ID='SB' then
-CSTN_SAL when PS_ID='SA' then
CSTN_SAL else
0 end),
AMTN2=(case when PS_ID in ('SB','SD') then
-AMTN_NET when PS_ID='SA' then
AMTN_NET else
0 end),
DEP=(case (select cus_no from mf_pss where (ps_id=tf_pss.ps_id) and (ps_no=tf_pss.ps_no))
when 'N9999' then
'1' else
'2' end),
REF_ID=(case when PS_ID='PC' then
'PC' when PS_ID='PB' then
'PB' when PS_ID='PD' then
'PD' when PS_ID='SA' then
'SA' when PS_ID='SB' then
'SB' when PS_ID='SD' then
'SD' end)
From TF_PSS Where (DATEDIFF(day,PS_DD,dateadd(day,1-datepart(day,@date),@date)) <=0)And(DATEDIFF(day,PS_DD,@date) >=0)
and Exists(Select CHK_MAN From MF_PSS where (PS_ID=TF_PSS.PS_ID)and(PS_NO=TF_PSS.PS_NO)and(CHK_MAN<>'')and(CHK_MAN is not null)) and wh=@wh
/*本日入库*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,QTY1,TrueQty1_QTY1,CST1,QTY2,TrueQty1_QTY2,CST2,DEP,REF_ID )
Select PRD_NO,isnull(PRD_MARK,''),WH,BAT_NO,UNIT,IJ_NO,ITM,RC_ID=(case when IJ_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
QTY1=(case when (isnull(Qty,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
Qty else
0 end),
TrueQty1_QTY1=(case when (isnull(Qty1,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
Qty1 else
0 end),
CST1=(case when (isnull(Qty,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
CST else
0 end),
QTY2=(case when (isnull(Qty,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Qty else
0 end),
TrueQty1_QTY2=(case when (isnull(Qty1,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Qty1 else
0 end),
CST2=(case when (isnull(Qty,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Cst else
0 end),
DEP='IJ',
REF_ID=(case when isnull(Qty,0)>=0 then
'IJ' else
'DJ' end)
From TF_IJ Where (DATEDIFF(day,IJ_DD,@date) <=0)And(DATEDIFF(day,IJ_DD,@date) >=0)
and Exists(Select CHK_MAN From MF_IJ where (IJ_NO=TF_IJ.IJ_NO)and(CHK_MAN<>'')and(CHK_MAN is not null))and wh=@wh
/*累计入库*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,SUM_QTY1,SUM_TrueQty1_QTY1,CST1,SUM_QTY2,SUM_TrueQty1_QTY2,CST2,DEP,REF_ID )
Select PRD_NO,isnull(PRD_MARK,''),WH,BAT_NO,UNIT,IJ_NO,ITM,RC_ID=(case when IJ_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
QTY1=(case when (isnull(Qty,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
Qty else
0 end),
TrueQty1_QTY1=(case when (isnull(Qty1,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
Qty1 else
0 end),
CST1=(case when (isnull(Qty,0)>=0)and(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')<>char(31)))) then
CST else
0 end),
QTY2=(case when (isnull(Qty,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Qty else
0 end),
TrueQty1_QTY2=(case when (isnull(Qty1,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Qty1 else
0 end),
CST2=(case when (isnull(Qty,0)<0)or(Exists (select * from mf_ij where (ij_no=tf_ij.ij_no)and(isnull(IJ_REASON,'')=char(31)))) then
-Cst else
0 end),
DEP='IJ',
REF_ID=(case when isnull(Qty,0)>=0 then
'IJ' else
'DJ' end)
From TF_IJ Where (DATEDIFF(day,IJ_DD,dateadd(day,1-datepart(day,@date),@date)) <=0)And(DATEDIFF(day,IJ_DD,@date) >=0)
and Exists(Select CHK_MAN From MF_IJ where (IJ_NO=TF_IJ.IJ_NO)and(CHK_MAN<>'')and(CHK_MAN is not null))and wh=@wh
/*本日调增*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,QTY1,TrueQty1_QTY1,CST1,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,''),WH2,BAT_NO2,UNIT,IC_NO,ITM,RC_ID=(case when IC_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
Qty,QTY1,Cst,'IC',
REF_ID='IC'
From TF_IC Where (DATEDIFF(day,Ic_DD,@date) <=0)And(DATEDIFF(day,Ic_DD,@date) >=0)and(Wh2 in ('0000','1101','1102','1103','1104','1201','1202','1203','2101','2102','2103','3101','3102','3103','3104','4020','4101','4102','4103','u022','u041','u101','u115','u125','u126','u127','u135','u145','u155','z012','zzzz'))
and(not Exists(select wh from my_wh where (wh=tf_ic.wh2)and(invalid='T')))
and Exists(Select CHK_MAN From MF_IC where (IC_NO=TF_IC.IC_NO)and(CHK_MAN<>'')and(CHK_MAN is not null))and wh2=@wh
/*累计调增*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,SUM_QTY1,SUM_TrueQty1_QTY1,CST1,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,''),WH2,BAT_NO2,UNIT,IC_NO,ITM,RC_ID=(case when IC_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
Qty,QTY1,Cst,'IC',
REF_ID='IC'
From TF_IC Where (DATEDIFF(day,Ic_DD,dateadd(day,1-datepart(day,@date),@date)) <=0)And(DATEDIFF(day,Ic_DD,@date) >=0)and(Wh2 in ('0000','1101','1102','1103','1104','1201','1202','1203','2101','2102','2103','3101','3102','3103','3104','4020','4101','4102','4103','u022','u041','u101','u115','u125','u126','u127','u135','u145','u155','z012','zzzz'))
and(not Exists(select wh from my_wh where (wh=tf_ic.wh2)and(invalid='T')))
and Exists(Select CHK_MAN From MF_IC where (IC_NO=TF_IC.IC_NO)and(CHK_MAN<>'')and(CHK_MAN is not null))and wh2=@wh
/*本日调减*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,QTY2,TrueQty1_QTY2,CST2,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,''),WH1,BAT_NO,UNIT,IC_NO,ITM,RC_ID=(case when IC_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
Qty,QTY1,Cst,'IC',
REF_ID='ID'
From TF_IC Where (DATEDIFF(day,Ic_DD,@date) <=0)And(DATEDIFF(day,Ic_DD,@date) >=0)and(Wh1 in ('0000','1101','1102','1103','1104','1201','1202','1203','2101','2102','2103','3101','3102','3103','3104','4020','4101','4102','4103','u022','u041','u101','u115','u125','u126','u127','u135','u145','u155','z012','zzzz'))
and Exists(Select CHK_MAN From MF_IC where (IC_NO=TF_IC.IC_NO)and(CHK_MAN<>'')and(CHK_MAN is not null)) and wh1=@wh
/*累计调减*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,ITM,RC_ID,SUM_QTY2,SUM_TrueQty1_QTY2,CST2,DEP,REF_ID)
Select PRD_NO,isnull(PRD_MARK,''),WH1,BAT_NO,UNIT,IC_NO,ITM,RC_ID=(case when IC_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
Qty,QTY1,Cst,'IC',
REF_ID='ID'
From TF_IC Where (DATEDIFF(day,Ic_DD,dateadd(day,1-datepart(day,@date),@date)) <=0)And(DATEDIFF(day,Ic_DD,@date) >=0)and(Wh1 in ('0000','1101','1102','1103','1104','1201','1202','1203','2101','2102','2103','3101','3102','3103','3104','4020','4101','4102','4103','u022','u041','u101','u115','u125','u126','u127','u135','u145','u155','z012','zzzz'))
and Exists(Select CHK_MAN From MF_IC where (IC_NO=TF_IC.IC_NO)and(CHK_MAN<>'')and(CHK_MAN is not null)) and wh1=@wh
/*成品缴库*/
Insert Into MrpCyTmp(PRD_NO,PRD_MARK,WH,BAT_NO,UNIT,REF_NO,RC_ID,QTY1,TrueQty1_QTY1,CST1,DEP,REF_ID)
Select MRP_NO,isnull(PRD_MARK,''''),WH,BAT_NO,UNIT,MM_NO,RC_ID=(case when MM_DD<dateadd(day,1-datepart(day,@date),@date) then
'0' else
'1' end ),
QTY,QTY1,isnull(CST,0)+isnull(CST_MAKE,0)+isnull(CST_PRD,0)+isnull(CST_MAN,0)+isnull(CST_OUT,0),
DEP,
REF_ID='MP'
From MF_MM Where (DATEDIFF(day,MM_DD,@date) <=0)And(DATEDIFF(day,MM_DD,@date) >=0)
and(CHK_MAN<>'''')and(CHK_MAN is not null)
DELETE MrpCyRep
INSERT INTO MrpCyRep (PRD_NO,bat_no,wh)
SELECT PRD_NO,bat_no,wh FROM MrpCyTmp group by prd_no,bat_no,wh
update MrpCyRep
set
QTY1=(select sum(isnull(qty1,0)-isnull(qty2,0)) from MrpCyTmp where (RC_ID='3')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
TrueQty1_QTY1=(select sum(isnull(TrueQty1_qty1,0)-isnull(TrueQty1_qty2,0)) from MrpCyTmp where (RC_ID='3')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),

QTY2=(select sum(isnull(qty1,0)) from MrpCyTmp where (RC_ID='1')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
TrueQty1_QTY2=(select sum(isnull(TrueQty1_qty1,0)) from MrpCyTmp where (RC_ID='1')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
SUM_QTY2=(select sum(isnull(SUM_qty1,0)) from MrpCyTmp where (RC_ID='1')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
SUM_TrueQty1_QTY2=(select sum(isnull(SUM_TrueQty1_qty1,0)) from MrpCyTmp where (RC_ID='1')and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
QTY3=(select sum(isnull(qty2,0)) from MrpCyTmp where (RC_ID='1')and (dep='2') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
TrueQty1_QTY3=(select sum(isnull(TrueQty1_qty2,0)) from MrpCyTmp where (RC_ID='1') and (dep='2') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
SUM_QTY3=(select sum(isnull(SUM_qty2,0)) from MrpCyTmp where (RC_ID='1')and (dep='2') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
SUM_TrueQty1_QTY3=(select sum(isnull(SUM_TrueQty1_qty2,0)) from MrpCyTmp where (RC_ID='1')and (dep='2') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),

/*QTY_SO表示委外出库米数，SUM_QTY_SO表示累计米数，采购单数量表示箱数，进货数量表示箱数累计*/
QTY_SO=(select sum(isnull(QTY2,0)) from MrpCyTmp where (RC_ID='1')and (dep='1') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
SUM_QTY_SO=(select sum(isnull(SUM_QTY2,0)) from MrpCyTmp where (RC_ID='1')and (dep='1') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
采购单数量=(select sum(isnull(TrueQty1_qty2,0)) from MrpCyTmp where (RC_ID='1')and (dep='1') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH)),
进货数量=(select sum(isnull(SUM_TrueQty1_qty2,0)) from MrpCyTmp where (RC_ID='1')and (dep='1') and
(IsNull(BAT_NO,'#')=IsNull(MrpCyRep.BAT_NO,'#'))AND(PRD_NO=MrpCyRep.Prd_NO)AND(WH=MrpCyRep.WH))

update MrpCyRep
set Qty4=isnull(Qty1,0)+isnull(SUM_Qty2,0)-isnull(SUM_Qty3,0)-isnull(SUM_QTY_SO,0),
TrueQty1_Qty4=isnull(TrueQty1_Qty1,0)+isnull(SUM_TrueQty1_Qty2,0)-isnull(SUM_TrueQty1_Qty3,0)-ISNULL(进货数量,0)
,prd_name=(select name from prdt where prd_no=MrpCyRep.prd_no)

to 樓主﹐
1.SQL Server查詢等待時限設定﹕打開EM﹐點選你的SQL Server服務器,然后選擇工具菜單中的"選項"命令--進階選項頁就可以看到了。如果設置為0﹐則表示沒有限制查詢等待時間﹔說明不是由SQL Server設置問題引起﹔
2.關于你的存儲過程執行時間﹐首先不管你的代碼是否寫得有效率﹐總之在后端執行需要1分多鐘的過程是絕對不會引起愈時問題的﹔嘗試修改TADOStoredProc組件的CommandTimeout值為更大的值。默認是30秒﹐即如果查詢沒有在CommandTimeout秒之內執行完的話就會引起愈時。