Z
zouduanke
Unregistered / Unconfirmed
GUEST, unregistred user!
我在客户那里遇到这样的问题,用下面的程序在一台客户端运行16个人用了不到1秒,另一台用了1分30秒以上,我想知道是什么原因造成的,谢谢var StartTime, EndTime: TDateTime; I, TableSerNo, J, K: Integer; IsJS: Boolean; Insert_tmp: WideString; Sort_A, Sort_B, Inde_N: String; Hour_T: Double; Con_Tmp, ConD_Tmp, Sheet_Tmp, SheetD_Tmp, Find_Str: WideString; Update_Tmp, UpdateD_Tmp, Item_Tmp, PeriodName_Tmp, Emp_Tmp: String;begin inherited; StartTime:=Now; frmWaitForm.Show; Sheet_Tmp:=''; if A_Find.Active then A_Find.Close; A_Find.CommandText:='select Condition from Datasheet where OperatorNo='''+User_No+''' and Datasheet=''MonthJB'''; A_Find.Open; SheetD_Tmp:=''; if A_Find.Active then A_Find.Close; A_Find.CommandText:='select Condition from Datasheet where OperatorNo='''+User_No+''' and Datasheet=''Personal'''; A_Find.Open; if A_Find.RecordCount>0 then SheetD_Tmp:=A_Find.Fields[0].AsString; Con_Tmp:=''; ConD_Tmp:=''; Item_Tmp:=Trim(LCB_Company.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and Company='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.Company='''+Item_Tmp+''''; end; Item_Tmp:=Trim(LCB_Depart.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and Depart='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.Depart='''+Item_Tmp+''''; end; Item_Tmp:=Trim(LCB_Team.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and Team='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.Team='''+Item_Tmp+''''; end; Item_Tmp:=Trim(LCB_Group.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and Group0='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.Group0='''+Item_Tmp+''''; end; Item_Tmp:=Trim(LCB_Salary.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and Salary='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.Salary='''+Item_Tmp+''''; end; Item_Tmp:=Trim(TE_EmployeeNo.Text); if Item_Tmp<>'' then begin Con_Tmp:=Con_Tmp+' and EmployeeNo='''+Item_Tmp+''''; ConD_Tmp:=ConD_Tmp+' and b.EmployeeNo='''+Item_Tmp+''''; end; if Trim(Con_Tmp)<>'' then begin Con_Tmp:=Copy(Con_Tmp,5,MaxInt); ConD_Tmp:=Copy(ConD_Tmp,5,MaxInt); end; if Trim(Con_Tmp)<>'' then begin if Trim(Sheet_Tmp)<>'' then Sheet_Tmp:=Sheet_Tmp+' and '+Con_Tmp else Sheet_Tmp:=Con_Tmp; if Trim(SheetD_Tmp)<>'' then SheetD_Tmp:=SheetD_Tmp+' and '+Con_Tmp else SheetD_Tmp:=Con_Tmp; end; if CB_Analysis.Checked then begin Update_Tmp:='insert into MonthJB(EmployeeNo, PeriodName) select EmployeeNo,'''+Period_Name+''' from Personal where '; if Trim(SheetD_Tmp)<>'' then Update_Tmp:=Update_Tmp+SheetD_Tmp+' and '; Update_Tmp:=Update_Tmp+' not exists(select EmployeeNo from MonthJB where EmployeeNo=Personal.EmployeeNo and PeriodName='''+Period_Name+''')'; Update_Tmp:=Update_Tmp+' and EntryDutyDate<='''+DateToStr(Date_End)+''''; AC_Insert.CommandText:=Update_Tmp; AC_Insert.Execute; if A_Find.Active then A_Find.Close; A_Find.CommandText:='select InsideNameD,InsideNameP from Interface where DatasheetID=''Personal'' and DatasheetIP=''MonthJB'''; A_Find.Open; A_Find.First; Update_Tmp:=''; while not A_Find.Eof do begin Update_Tmp:=Update_Tmp+A_Find.Fields[1].AsString+'=b.'+A_Find.Fields[0].AsString+','; A_Find.Next; end; Update_Tmp:=LeftStr(Update_Tmp, Length(Update_Tmp)-1); Update_Tmp:='update MonthJB set '+Update_Tmp+' from MonthJB a inner join Personal b on a.EmployeeNo=b.EmployeeNo where a.PeriodName='''+Period_Name+''''; if Trim(ConD_Tmp)<>'' then Update_Tmp:=Update_Tmp+' and '+ConD_Tmp; AC_Insert.CommandText:=Update_Tmp; AC_Insert.Execute; AC_Insert.CommandText:='delete from MonthJB where EntryDutyDate>'''+DateToStr(Date_End)+''' and PeriodName='''+Period_Name+''''; if Trim(Sheet_Tmp)<>'' then AC_Insert.CommandText:=AC_Insert.CommandText+' and '+Sheet_Tmp; AC_Insert.Execute; AC_Insert.CommandText:='delete from MonthJB where PeriodName='''+Period_Name+''' and not exists(select EmployeeNo from Personal where Personal.EmployeeNo=MonthJB.EmployeeNo) and not exists(select EmployeeNo from Personal4 where EmployeeNo=MonthJB.EmployeeNo)'; if Trim(Sheet_Tmp)<>'' then AC_Insert.CommandText:=AC_Insert.CommandText+' and '+Sheet_Tmp; AC_Insert.Execute; if A_Emp.Active then A_Emp.Close; A_Emp.CommandText:='select EmployeeNo from MonthJB where PeriodName='''+Period_Name+''''; if Trim(Sheet_Tmp)<>'' then A_Emp.CommandText:=A_Emp.CommandText+' and '+Sheet_Tmp; A_Emp.Open; if A_Emp.RecordCount>0 then begin if A_Find.Active then A_Find.Close; A_Find.CommandText:='select InsideNameD,InsideNameP from Interface where DatasheetID=''MonthJB'' and DatasheetIP=''MonthJB'''; A_Find.Open; if A_Find.RecordCount>0 then begin A_Find.First; Update_Tmp:=''; UpdateD_Tmp:=''; TS_DataP.Clear; while not A_Find.Eof do begin Update_Tmp:=Update_Tmp+A_Find.Fields[0].AsString+','; UpdateD_Tmp:=UpdateD_Tmp+A_Find.Fields[1].AsString+'=:'+A_Find.Fields[1].AsString+','; TS_DataP.Add(A_Find.Fields[1].AsString); A_Find.Next; end; if A_Find.Active then A_Find.Close; A_Find.CommandText:='select max(PeriodName) from Period where PeriodName<'''+Period_Name+''''; A_Find.Open; PeriodName_Tmp:=A_Find.Fields[0].AsString; Update_Tmp:=LeftStr(Update_Tmp, Length(Update_Tmp)-1); UpdateD_Tmp:=LeftStr(UpdateD_Tmp, Length(UpdateD_Tmp)-1); if A_Find.Active then A_Find.Close; A_Find.CommandText:='select '+Update_Tmp+' from MonthJB where EmployeeNo=:EmployeeNo and PeriodName='''+PeriodName_Tmp+''''; A_Find.Prepared:=True; AC_Insert.CommandText:='update MonthJB set '+UpdateD_Tmp+' where EmployeeNo=:EmployeeNo and PeriodName='''+Period_Name+''''; AC_Insert.Prepared:=True; A_Emp.First; while not A_Emp.Eof do begin Emp_Tmp:=A_Emp.Fields[0].AsString; if A_Find.Active then A_Find.Close; A_Find.Parameters.ParamByName('EmployeeNo').Value:=Emp_Tmp; A_Find.Open; if A_Find.RecordCount>0 then begin for I:=0 to TS_DataP.Count-1 do begin AC_Insert.Parameters.ParamByName(TS_DataP.Strings).Value:=A_Find.Fields.AsString; end; AC_Insert.Parameters.ParamByName('EmployeeNo').Value:=Emp_Tmp; AC_Insert.Execute; end; A_Emp.Next; end; A_Find.Prepared:=False; AC_Insert.Prepared:=False; end; if not A_Sheet.Active then A_Sheet.Open; if A_Sheet.RecordCount>0 then begin //Application.MessageBox('bbb','bbb',48); A_Sheet.First; while not A_Sheet.Eof do begin Item_Tmp:=A_Sheet.Fields[0].AsString; //Application.MessageBox(PChar(Item_Tmp),'bbb',48); if A_Find.Active then A_Find.Close; A_Find.CommandText:='select InsideNameD,InsideNameP from Interface where DatasheetID='''+Item_Tmp+''' and DatasheetIP=''MonthJB'''; A_Find.Open; if A_Find.RecordCount>0 then begin A_Find.First; Update_Tmp:=''; UpdateD_Tmp:=''; TS_DataP.Clear; while not A_Find.Eof do begin Update_Tmp:=Update_Tmp+'sum('+A_Find.Fields[0].AsString+'),'; UpdateD_Tmp:=UpdateD_Tmp+A_Find.Fields[1].AsString+'=:'+A_Find.Fields[1].AsString+','; TS_DataP.Add(A_Find.Fields[1].AsString); A_Find.Next; end; Update_Tmp:=LeftStr(Update_Tmp, Length(Update_Tmp)-1); UpdateD_Tmp:=LeftStr(UpdateD_Tmp, Length(UpdateD_Tmp)-1); if A_Find.Active then A_Find.Close; A_Find.CommandText:='select '+Update_Tmp+' from '+Item_Tmp+' where EmployeeNo=:EmployeeNo and (RecordDate between '''+DateToStr(Date_Start)+''' and '''+DateToStr(Date_End)+''')'; A_Find.Prepared:=True; AC_Insert.CommandText:='update MonthJB set '+UpdateD_Tmp+' where EmployeeNo=:EmployeeNo and PeriodName='''+Period_Name+''''; AC_Insert.Prepared:=True; //Application.MessageBox('ccc','ccc',48); A_Emp.First; while not A_Emp.Eof do begin Emp_Tmp:=A_Emp.Fields[0].AsString; //Application.MessageBox(PChar(Emp_Tmp),'aaa',48); //if A_Find.Active then A_Find.Close; A_Find.Parameters.ParamByName('EmployeeNo').Value:=Emp_Tmp; A_Find.Open; if A_Find.RecordCount>0 then begin for I:=0 to TS_DataP.Count-1 do begin AC_Insert.Parameters.ParamByName(TS_DataP.Strings).Value:=A_Find.Fields.AsFloat; end; AC_Insert.Parameters.ParamByName('EmployeeNo').Value:=Emp_Tmp; AC_Insert.Execute; end else begin for I:=0 to TS_DataP.Count-1 do begin AC_Insert.Parameters.ParamByName(TS_DataP.Strings).Value:=0; end; AC_Insert.Parameters.ParamByName('EmployeeNo').Value:=Emp_Tmp; AC_Insert.Execute; end; A_Emp.Next; end; A_Find.Prepared:=False; AC_Insert.Prepared:=False; end; A_Sheet.Next; end; end; //Application.MessageBox('aaa','aaa',48); end; AC_Insert.CommandText:='update MonthJB set StartDate='''+DateToStr(Date_Start)+''',EndDate='''+DateToStr(Date_End)+''',PeriodDays='+IntToStr(Period_Days)+' where PeriodName='''+Period_Name+''''; if Trim(Sheet_Tmp)<>'' then AC_Insert.CommandText:=AC_Insert.CommandText+' and '+Sheet_Tmp; AC_Insert.Execute; end; //Application.MessageBox('bbb','bbb',48); if CB_Calu.Checked and (LB_CaluFormula.SelCount>0) then begin for I:= 0 to LB_CaluFormula.Count - 1 do begin if LB_CaluFormula.Selected then begin //Emp_Tmp:=Sheet_Tmp; //Application.MessageBox(PChar(Emp_Tmp),'aaa',48); TableSerNo:= StrToInt(Trim(LeftStr(LB_CaluFormula.Items,2))); if SP_CollectFormula.Active then SP_CollectFormula.Close; SP_CollectFormula.Parameters.ParamByName('@UpdateStr').Value:= Sheet_Tmp; SP_CollectFormula.Parameters.ParamByName('@PeriodName').Value:= Period_Name; SP_CollectFormula.Parameters.ParamByName('@TableSerNo').Value:= TableSerNo; SP_CollectFormula.ExecProc; if SP_CollectFormula.Parameters.ParamByName('@RETURN_VALUE').Value <> 0 then begin if SP_CollectFormula.Active then SP_CollectFormula.Close; Application.MessageBox('计算有误', '消息', 48); Abort; end; end; end; if SP_CollectFormula.Active then SP_CollectFormula.Close; end; if A_Find.Active then A_Find.Close; if A_Emp.Active then A_Emp.Close; if A_Sheet.Active then A_Sheet.Close; //Application.MessageBox('ccc','ccc',48); AC_Insert.CommandText:='delete from MonthJB where (JBXS+JJSJ+JJXS+ZLHour)=0 and PeriodName='''+Period_Name+''''; if Trim(Sheet_Tmp)<>'' then AC_Insert.CommandText:=AC_Insert.CommandText+' and '+Sheet_Tmp; AC_Insert.Execute; frmWaitForm.Hide; EndTime:=Now-StartTime; Application.MessageBox(PChar('分析完毕,共用'+IntToStr(HourOf(EndTime))+'时'+IntToStr(MinuteOf(EndTime))+'分'+IntToStr(SecondOf(EndTime))+'秒'),'消息',48); end;
