如何提高程序运行速度！ (30分)

  • 主题发起人 主题发起人 ayayzh
  • 开始时间 开始时间
A

ayayzh

Unregistered / Unconfirmed
GUEST, unregistred user!
我用的是delphi6+sql 2000.
在数据汇总时，要从多个表中提取数据，
有没有方法提高其运行速度。
源程序如下：请帮忙优化一下
procedure TFzhz.Button1Click(Sender: TObject);var j:integer;
nc,nm,nm1:double;
a1,a2,a3,a4,a5:double;
a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23:double;
n1,n2,n3,n4:double;
m1,m2,m3,m4,m5,m6,m7,m8,m9,m10:double;
k1,k2,k3:double;
a24,a25,a26,a27,a28,a29,a30,a31:double;
s2,s1:string;
begin
gs:=fzhz.ADOTable1.RecordCount;
dw:='';
fzhz.ADOTable1.First;
repeat
s1:=trim(fzhz.ADOTable1.fieldbyname('dwmc').AsString);
fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj01'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';////过滤单位名称
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj1
fzhz.ADOTable2.Close;
fzhz.ADOTable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.open;
fzhz.ADOTable2.First;
repeat
nc:=fzhz.ADOTable2.fieldbyname('C').AsFloat;
nm:=fzhz.ADOTable2.fieldbyname('D').AsFloat;
nm1:=fzhz.ADOTable2.fieldbyname('G').AsFloat;
fzhz.ADOQuery1.Close;
fzhz.ADOQuery1.SQL.Clear;
fzhz.ADOQuery1.SQL.Add('update Wcj1 set C=C+:C1,D=D+:D1,G=G+:G1 where B=:B1');
fzhz.ADOQuery1.Parameters.ParamByName('C1').Value:=nc;
fzhz.ADOQuery1.Parameters.ParamByName('D1').Value:=nm;
fzhz.ADOQuery1.Parameters.ParamByName('G1').Value:=nm1;
fzhz.ADOQuery1.Parameters.ParamByName('B1').Value:=j;
fzhz.ADOQuery1.Prepared;
fzhz.ADOQuery1.ExecSQL;
///end; //1
fzhz.ADOTable2.Next;
until fzhz.ADOTable2.Eof;
end; ///cj1
fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj02'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj2
fzhz.ADOTable2.Close;
fzhz.ADOTable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.Filtered:=false;
fzhz.ADOTable2.open;
fzhz.ADOTable2.First;
a6:=fzhz.ADOTable2.fieldbyname('H').AsFloat;
a7:=fzhz.ADOTable2.fieldbyname('I').AsFloat;
a8:=fzhz.ADOTable2.fieldbyname('J').AsFloat;
a9:=fzhz.ADOTable2.fieldbyname('K').AsFloat;
a10:=fzhz.ADOTable2.fieldbyname('L').AsFloat;
a11:=fzhz.ADOTable2.fieldbyname('M').AsFloat;
a12:=fzhz.ADOTable2.fieldbyname('N').AsFloat;
a13:=fzhz.ADOTable2.fieldbyname('O').AsFloat;
a14:=fzhz.ADOTable2.fieldbyname('P').AsFloat;
a15:=fzhz.ADOTable2.fieldbyname('Q').AsFloat;
a16:=fzhz.ADOTable2.fieldbyname('R').AsFloat;
a17:=fzhz.ADOTable2.fieldbyname('S').AsFloat;
a18:=fzhz.ADOTable2.fieldbyname('T').AsFloat;
a19:=fzhz.ADOTable2.fieldbyname('U').AsFloat;
a20:=fzhz.ADOTable2.fieldbyname('V').AsFloat;
a21:=fzhz.ADOTable2.fieldbyname('W').AsFloat;
a22:=fzhz.ADOTable2.fieldbyname('X').AsFloat;
a23:=fzhz.ADOTable2.fieldbyname('Y').AsFloat;
fzhz.ADOQuery2.Close;
fzhz.ADOQuery2.SQL.Clear;
fzhz.ADOQuery2.SQL.Add('update Wcj2 set H=H+:H1,I=I+:I1,J=J+:J1,');
fzhz.ADOQuery2.SQL.Add('K=K+:K1,L=L+:L1,M=M+:M1,N=N+:N1,O=O+:O1,');
fzhz.ADOQuery2.SQL.Add('P=P+:P1,Q=Q+:Q1,R=R+:R1,S=S+:S1,T=T+:T1,');
fzhz.ADOQuery2.SQL.Add('U=U+:U1,V=V+:V1,W=W+:W1,X=X+:X1,Y=Y+:Y1');
fzhz.ADOQuery2.SQL.Add('where id=1');
fzhz.ADOQuery2.Parameters.ParamByName('H1').Value:=a6;
fzhz.ADOQuery2.Parameters.ParamByName('I1').Value:=a7;
fzhz.ADOQuery2.Parameters.ParamByName('J1').Value:=a8;
fzhz.ADOQuery2.Parameters.ParamByName('K1').Value:=a9;
fzhz.ADOQuery2.Parameters.ParamByName('L1').Value:=a10;
fzhz.ADOQuery2.Parameters.ParamByName('M1').Value:=a11;
fzhz.ADOQuery2.Parameters.ParamByName('N1').Value:=a12;
fzhz.ADOQuery2.Parameters.ParamByName('O1').Value:=a13;
fzhz.ADOQuery2.Parameters.ParamByName('P1').Value:=a14;
fzhz.ADOQuery2.Parameters.ParamByName('Q1').Value:=a15;
fzhz.ADOQuery2.Parameters.ParamByName('R1').Value:=a16;
fzhz.ADOQuery2.Parameters.ParamByName('S1').Value:=a17;
fzhz.ADOQuery2.Parameters.ParamByName('T1').Value:=a18;
fzhz.ADOQuery2.Parameters.ParamByName('U1').Value:=a19;
fzhz.ADOQuery2.Parameters.ParamByName('V1').Value:=a20;
fzhz.ADOQuery2.Parameters.ParamByName('W1').Value:=a21;
fzhz.ADOQuery2.Parameters.ParamByName('X1').Value:=a22;
fzhz.ADOQuery2.Parameters.ParamByName('Y1').Value:=a23;
fzhz.ADOQuery2.Prepared;
fzhz.ADOQuery2.ExecSQL;
end; ////cj2
///////////////////////////////////////////
fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj03'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj3
fzhz.ADOTable2.Close;
fzhz.ADOTable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.open;
fzhz.ADOTable2.First;
repeat
n1:=fzhz.ADOTable2.fieldbyname('C').AsFloat; ///本年数
n2:=fzhz.ADOTable2.fieldbyname('D').AsFloat; ///年末数
n3:=fzhz.ADOTable2.fieldbyname('G').AsFloat;
n4:=fzhz.ADOTable2.fieldbyname('H').AsFloat;
fzhz.ADOQuery2.Close;
fzhz.ADOQuery2.SQL.Clear;
fzhz.ADOQuery2.SQL.Add('update Wcj3 set C=C+:C1,D=D+:D1,G=G+:G1,H=H+:H1');
fzhz.ADOQuery2.SQL.Add('where B=:B1');
fzhz.ADOQuery2.Parameters.ParamByName('C1').Value:=n1;
fzhz.ADOQuery2.Parameters.ParamByName('D1').Value:=n2;
fzhz.ADOQuery2.Parameters.ParamByName('G1').Value:=n3;
fzhz.ADOQuery2.Parameters.ParamByName('H1').Value:=n4;
fzhz.ADOQuery2.Parameters.ParamByName('B1').Value:=j;
fzhz.ADOQuery2.Prepared;
fzhz.ADOQuery2.ExecSQL;
/// end; ///4
fzhz.ADOTable2.Next;
until fzhz.ADOTable2.Eof;
end; ////cj3
fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj04'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj4
fzhz.ADOTable2.Close;
fzhz.ADOTable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.open;
fzhz.ADOTable2.First;
repeat
m1:=fzhz.ADOTable2.fieldbyname('C').AsFloat;
m2:=fzhz.ADOTable2.fieldbyname('D').AsFloat;
m3:=fzhz.ADOTable2.fieldbyname('E').AsFloat;
m4:=fzhz.ADOTable2.fieldbyname('F').AsFloat;
m5:=fzhz.ADOTable2.fieldbyname('G').AsFloat;
m6:=fzhz.ADOTable2.fieldbyname('H').AsFloat;
m7:=fzhz.ADOTable2.fieldbyname('I').AsFloat;
m8:=fzhz.ADOTable2.fieldbyname('J').AsFloat;
m9:=fzhz.ADOTable2.fieldbyname('K').AsFloat;
m10:=fzhz.ADOTable2.fieldbyname('L').AsFloat;
fzhz.ADOQuery2.Close;
fzhz.ADOQuery2.SQL.Clear;
fzhz.ADOQuery2.SQL.Add('update Wcj4 set C=C+:C1,D=D+:D1,E=E+:E1,');
fzhz.ADOQuery2.SQL.Add('F=F+:F1,G=:G1,H=H+:H1,I=I+:I1,J=J+:J1,K=K+:K1,L=L+:L1');
fzhz.ADOQuery2.SQL.Add('where B=:B1');
fzhz.ADOQuery2.Parameters.ParamByName('C1').Value:=m1;
fzhz.ADOQuery2.Parameters.ParamByName('D1').Value:=m2;
fzhz.ADOQuery2.Parameters.ParamByName('E1').Value:=m3;
fzhz.ADOQuery2.Parameters.ParamByName('F1').Value:=m4;
fzhz.ADOQuery2.Parameters.ParamByName('G1').Value:=m5;
fzhz.ADOQuery2.Parameters.ParamByName('H1').Value:=m6;
fzhz.ADOQuery2.Parameters.ParamByName('I1').Value:=m7;
fzhz.ADOQuery2.Parameters.ParamByName('J1').Value:=m8;
fzhz.ADOQuery2.Parameters.ParamByName('K1').Value:=m9;
fzhz.ADOQuery2.Parameters.ParamByName('L1').Value:=m10;
fzhz.ADOQuery2.Parameters.ParamByName('B1').Value:=j;
fzhz.ADOQuery2.Prepared;
fzhz.ADOQuery2.ExecSQL;
fzhz.ADOTable2.Next;
until fzhz.ADOTable2.Eof;
end;////cj4
fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj05'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj5
fzhz.ADOTable2.Close;
fzhz.ADOTable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.open;
fzhz.ADOTable2.First;
repeat
k1:=fzhz.ADOTable2.fieldbyname('C').AsFloat;
k2:=fzhz.ADOTable2.fieldbyname('F').AsFloat;
k3:=fzhz.ADOTable2.fieldbyname('I').AsFloat;
///
fzhz.ADOQuery2.Close;
fzhz.ADOQuery2.SQL.Clear;
fzhz.ADOQuery2.SQL.Add('update Wcj5 set C=C+:C1,F=F+:F1,I=I+:I1');
fzhz.ADOQuery2.SQL.Add('where B=:B1');
fzhz.ADOQuery2.Parameters.ParamByName('C1').Value:=k1;
fzhz.ADOQuery2.Parameters.ParamByName('F1').Value:=k2;
fzhz.ADOQuery2.Parameters.ParamByName('I1').Value:=k3;
fzhz.ADOQuery2.Parameters.ParamByName('B1').Value:=j;
fzhz.ADOQuery2.Prepared;
fzhz.ADOQuery2.ExecSQL;
///end;
fzhz.ADOTable2.Next;
until fzhz.ADOTable2.Eof;
　end; //cj5
　 fzhz.ADOTable3.Close;
fzhz.ADOTable3.TableName:='Wcj06'; ///动态赋表名
fzhz.ADOTable3.Filter:='dwmc='''+s1+'''';
fzhz.ADOTable3.Filtered:=true;
fzhz.ADOTable3.open;
s2:=trim(fzhz.ADOTable3.fieldbyname('zjm').AsString);
if s2<>'' then
begin //cj6
fzhz.ADOtable2.Close;
fzhz.ADOtable2.TableName:=s2; ///动态赋表名
fzhz.ADOTable2.Filtered:=false;
fzhz.ADOtable2.open;
fzhz.ADOtable2.First;
a1:=fzhz.ADOtable2.fieldbyname('C').AsFloat;
a2:=fzhz.ADOtable2.fieldbyname('D').AsFloat;
a3:=fzhz.ADOtable2.fieldbyname('E').AsFloat;
a4:=fzhz.ADOtable2.fieldbyname('F').AsFloat;
a5:=fzhz.ADOtable2.fieldbyname('G').AsFloat;
a6:=fzhz.ADOtable2.fieldbyname('H').AsFloat;
a7:=fzhz.ADOtable2.fieldbyname('I').AsFloat;
a8:=fzhz.ADOtable2.fieldbyname('J').AsFloat;
a9:=fzhz.ADOtable2.fieldbyname('K').AsFloat;
a10:=fzhz.ADOtable2.fieldbyname('L').AsFloat;
a11:=fzhz.ADOtable2.fieldbyname('M').AsFloat;
a12:=fzhz.ADOtable2.fieldbyname('N').AsFloat;
a13:=fzhz.ADOtable2.fieldbyname('O').AsFloat;
a14:=fzhz.ADOtable2.fieldbyname('P').AsFloat;
a15:=fzhz.ADOtable2.fieldbyname('Q').AsFloat;
a16:=fzhz.ADOtable2.fieldbyname('R').AsFloat;
a17:=fzhz.ADOtable2.fieldbyname('S').AsFloat;
a18:=fzhz.ADOtable2.fieldbyname('T').AsFloat;
a19:=fzhz.ADOtable2.fieldbyname('U').AsFloat;
a20:=fzhz.ADOtable2.fieldbyname('V').AsFloat;
a21:=fzhz.ADOtable2.fieldbyname('W').AsFloat;
a22:=fzhz.ADOtable2.fieldbyname('X').AsFloat;
a23:=fzhz.ADOtable2.fieldbyname('Y').AsFloat;
a24:=fzhz.ADOtable2.fieldbyname('Z').AsFloat;
a25:=fzhz.ADOtable2.fieldbyname('ZA').AsFloat;
a26:=fzhz.ADOtable2.fieldbyname('ZB').AsFloat;
a27:=fzhz.ADOtable2.fieldbyname('ZC').AsFloat;
a28:=fzhz.ADOtable2.fieldbyname('ZD').AsFloat;
a29:=fzhz.ADOtable2.fieldbyname('ZE').AsFloat;
a30:=fzhz.ADOtable2.fieldbyname('ZF').AsFloat;
a31:=fzhz.ADOtable2.fieldbyname('ZG').AsFloat;
fzhz.ADOQuery2.Close;
fzhz.ADOQuery2.SQL.Clear;
fzhz.ADOQuery2.SQL.Add('update Wcj6 set C=C+:C1,D=D+:D1,');
fzhz.ADOQuery2.SQL.Add('E=E+:E1,F=F+:F1,G=G+:G1,H=H+:H1,I=I+:I1,J=J+:J1,');
fzhz.ADOQuery2.SQL.Add('K=K+:K1,L=L+:L1,M=M+:M1,N=N+:N1,O=O+:O1,');
fzhz.ADOQuery2.SQL.Add('P=P+:P1,Q=Q+:Q1,R=R+:R1,S=S+:S1,T=T+:T1,');
fzhz.ADOQuery2.SQL.Add('U=U+:U1,V=V+:V1,W=W+:W1,X=X+:X1,Y=Y+:Y1,');
fzhz.ADOQuery2.SQL.Add('Z=Z+:Z1,ZA=ZA+:ZA1,ZB=ZB+:ZB1,ZC=ZC+:ZC1,ZD=ZD+:ZD1,');
fzhz.ADOQuery2.SQL.Add('ZE=ZE+:ZE1,ZF=ZF+:ZF1,ZG=ZG+:ZG1');
fzhz.ADOQuery2.SQL.Add('where id=1');
fzhz.ADOQuery2.Parameters.ParamByName('C1').Value:=a1;
fzhz.ADOQuery2.Parameters.ParamByName('D1').Value:=a2;
fzhz.ADOQuery2.Parameters.ParamByName('E1').Value:=a3;
fzhz.ADOQuery2.Parameters.ParamByName('F1').Value:=a4;
fzhz.ADOQuery2.Parameters.ParamByName('G1').Value:=a5;
fzhz.ADOQuery2.Parameters.ParamByName('H1').Value:=a6;
fzhz.ADOQuery2.Parameters.ParamByName('I1').Value:=a7;
fzhz.ADOQuery2.Parameters.ParamByName('J1').Value:=a8;
fzhz.ADOQuery2.Parameters.ParamByName('K1').Value:=a9;
fzhz.ADOQuery2.Parameters.ParamByName('L1').Value:=a10;
fzhz.ADOQuery2.Parameters.ParamByName('M1').Value:=a11;
fzhz.ADOQuery2.Parameters.ParamByName('N1').Value:=a12;
fzhz.ADOQuery2.Parameters.ParamByName('O1').Value:=a13;
fzhz.ADOQuery2.Parameters.ParamByName('P1').Value:=a14;
fzhz.ADOQuery2.Parameters.ParamByName('Q1').Value:=a15;
fzhz.ADOQuery2.Parameters.ParamByName('R1').Value:=a16;
fzhz.ADOQuery2.Parameters.ParamByName('S1').Value:=a17;
fzhz.ADOQuery2.Parameters.ParamByName('T1').Value:=a18;
fzhz.ADOQuery2.Parameters.ParamByName('U1').Value:=a19;
fzhz.ADOQuery2.Parameters.ParamByName('V1').Value:=a20;
fzhz.ADOQuery2.Parameters.ParamByName('W1').Value:=a21;
fzhz.ADOQuery2.Parameters.ParamByName('X1').Value:=a22;
fzhz.ADOQuery2.Parameters.ParamByName('Y1').Value:=a23;
fzhz.ADOQuery2.Parameters.ParamByName('Z1').Value:=a24;
fzhz.ADOQuery2.Parameters.ParamByName('ZA1').Value:=a25;
fzhz.ADOQuery2.Parameters.ParamByName('ZB1').Value:=a26;
fzhz.ADOQuery2.Parameters.ParamByName('ZC1').Value:=a27;
fzhz.ADOQuery2.Parameters.ParamByName('ZD1').Value:=a28;
fzhz.ADOQuery2.Parameters.ParamByName('ZE1').Value:=a29;
fzhz.ADOQuery2.Parameters.ParamByName('ZF1').Value:=a30;
fzhz.ADOQuery2.Parameters.ParamByName('ZG1').Value:=a31;
fzhz.ADOQuery2.Prepared;
fzhz.ADOQuery2.ExecSQL;
end; //cj6
//////////////
dw:=dw+','+#13+trim(fzhz.ADOTable1.fieldbyname('dwmc').AsString);
fzhz.ADOTable1.Next;
until fzhz.ADOTable1.Eof;
///循环结束
delete(dw,1,1);
end；
 
存储过程
 
优化SQL，再不中，弄把刀放电脑前，威胁威胁它，有时那玩意就是欺软怕硬的 ：）
 
app2001大哥，说点实在的吧，原子弹也不行
 
增加服务器内存!国营单位最佳选择
 
1.适当使用left join,right join....
2.可以考虑分两次查询,就是把表分成组查询
select into e from a,b
select into f from c,d
select ...from e,f
 
多线成
 
对要提的数据在对应的表中建立索引比什么都重要。
 
您必须登录或注册才可回复。

Similar threads

I
常用算法（3）动态规划
回复
0
查看
1K
import
I
I
modem的at指令集
回复
0
查看
679
import
I
L
如何提高查询速度？(200)
回复
3
查看
511
草原骏马
草
H
INSERT INTO 语句语法问题(6分)
回复
4
查看
420
waterxbottle
W
X
应部分网友要求，公开 myfunctions 单元，里面有很多有用的函数(0)
回复
10
查看
4K
yczjs
Y
后退
顶部