1000分解决速度问题

//我这段代码主要是调存储过程VD_SelectValue写入、更新文本文件
//说明：代码是调存储过程五十次并
//查询CountNum的值出来，把CountNum值一次次更新到一个文本文件里，做完一个文本文件5分钟（赛扬2.2 内存512的机器）左右，
//请问有那位高手指点指点能提高速度,1000分相送

//存储过程VD_SelectValue
CREATE PROCEDURE VD_SelectValue
@TabVal NVarchar(50),
@MaxVal NVarchar(50),
@MinVal NVarchar(50),
@Val1 NVarchar(50),
@Val2 NVarchar(50),
@Val3 NVarchar(50),
@Val4 NVarchar(50),
@Val5 NVarchar(50),
@Val6 NVarchar(50),
@Val7 NVarchar(50),
@Val8 NVarchar(50),
@Val9 NVarchar(50),
@Val10 NVarchar(50),
@Val11 NVarchar(50),
@Val12 NVarchar(50),
@Val13 NVarchar(50),
@Val14 NVarchar(50),
@Value1 NVarchar(50),
@Value2 NVarchar(50),
@Value3 NVarchar(50),
@Value4 NVarchar(50),
@Value5 NVarchar(50),
@Value6 NVarchar(50),
@Value7 NVarchar(50),
@Value8 NVarchar(50),
@Value9 NVarchar(50),
@Value10 NVarchar(50),
@Value11 NVarchar(50),
@Value12 NVarchar(50),
@Value13 NVarchar(50),
@Value14 NVarchar(50)

AS

DECLARE @SQLText NVarchar(4000)
DECLARE @SQLVal NVarchar(4000)
SELECT @SQLVal =' SELECT * FROM '+@TabVal+' T2 WHERE T2.Idx=T1.Idx and '
SELECT @SQLText =
'SELECT CountNum=
CASE
WHEN CountNum < ' + @MinVal + ' THEN 0
WHEN CountNum > ' + @MaxVal + ' THEN 0
ELSE 1 END
FROM
(SELECT Tab1.Idx,Num1+Num2 AS CountNum FROM
(SELECT Idx,Num1=
(CASE WHEN EXISTS('+@SQLVal+' A in '+@Val1+') THEN '+@Value1+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' B in '+@Val2+') THEN '+@Value2+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' C in '+@Val3+') THEN '+@Value3+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' D in '+@Val4+') THEN '+@Value4+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' E in '+@Val5+') THEN '+@Value5+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' F in '+@Val6+') THEN '+@Value6+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' G in '+@Val7+') THEN '+@Value7+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' H in '+@Val8+') THEN '+@Value8+' ELSE 0 END)
FROM '+@TabVal+' T1) Tab1
,
(SELECT Idx,Num2=
(CASE WHEN EXISTS('+@SQLVal+' I in '+@Val9+') THEN '+@Value9+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' J in '+@Val10+') THEN '+@Value10+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' K in '+@Val11+') THEN '+@Value11+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' L in '+@Val12+') THEN '+@Value12+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' M in '+@Val13+') THEN '+@Value13+' ELSE 0 END)
+
(CASE WHEN EXISTS('+@SQLVal+' N in '+@Val14+') THEN '+@Value14+' ELSE 0 END)
FROM '+@TabVal+' T1) Tab2
WHERE Tab1.Idx=Tab2.Idx) Tab3
ORDER BY Idx'

EXEC (@SQLText)

GO

//调用存储过程的代码
procedure TfrmMain.MenuDataClick(Sender: TObject);
var
iRow,iTblCount,i: Integer;
strSql,tblName : String;
strMinSet,strMaxSet,str1,str2,str3,str4,str5,str6,str7,str8,str9,str10,str11,str12,str13,str14 :String ;
begin
for i := 1 to iTblCount do
begin
tblName := 'VD_SingleNumber'+IntToStr(I);
for iRow := 1 to 50 do
begin
strMinSet := '5';
strMaxSet := '10';
str1 := splitData(strGridMedia.Cells[2,iRow]);
str2 := splitData(strGridMedia.Cells[3,iRow]);
str3 := splitData(strGridMedia.Cells[4,iRow]);
str4 := splitData(strGridMedia.Cells[5,iRow]);
str5 := splitData(strGridMedia.Cells[6,iRow]);
str6 := splitData(strGridMedia.Cells[7,iRow]);
str7 := splitData(strGridMedia.Cells[8,iRow]);
str8 := splitData(strGridMedia.Cells[9,iRow]);
str9 := splitData(strGridMedia.Cells[10,iRow]);
str10 := splitData(strGridMedia.Cells[11,iRow]);
str11 := splitData(strGridMedia.Cells[12,iRow]);
str12 := splitData(strGridMedia.Cells[13,iRow]);
str13 := splitData(strGridMedia.Cells[14,iRow]);
str14 := splitData(strGridMedia.Cells[15,iRow]);
strSql := 'Exec VD_SelectValue '''+tblName+''','''+strMaxSet+''','''+strMinSet+''','''+str1+''','''+str2+''','''+str3+''','''+str4+''','''+str5+''',''';
strSql :=strSql +str6+''','''+str7+''','''+str8+''','''+str9+''','''+str10+''','''+str11+''','''+str12+''','''+str13+''','''+str14+''',''';
strSql :=strSql +psArrayVal[1]+''','''+psArrayVal[2]+''','''+psArrayVal[3]+''','''+psArrayVal[4]+''','''+psArrayVal[5]+''','''+psArrayVal[6]+''','''+psArrayVal[7]+''',''';
strSql :=strSql +psArrayVal[8]+''','''+psArrayVal[9]+''','''+psArrayVal[10]+''','''+psArrayVal[11]+''','''+psArrayVal[12]+''','''+psArrayVal[13]+''','''+psArrayVal[14]+'''';
RunSql(strSql,False);
SaveDataToFile(iRow,IntToStr(1000+I));
end;
end;
ShowMessage('完成数据分析');
End;
end;
//更新文本的代码
Procedure SaveDataToFile(row: Integer; strFile: String);
var
F : TextFile;
strData,strData1: String;
strFileName: TFileName;
AA: TStringList;
begin

strFileName := ExtractFilePath(Paramstr(0))+'Data\CompareData'+strFile+'.txt';
if row =1 then
begin
AssignFile(F,strFileName);
ReWrite(F);
end else
begin
aa:=tstringlist.Create;
aa.LoadFromFile(strFileName);
end;
with DM.ADOQuery do
begin
while NOT Eof do
begin
if row >1 then
begin
strData := DM.ADOQuery.FieldByName('CountNum').AsString;
strData1 := AA.Strings[Recno-1];
strData1 := IntToStr(StrToInt(strData1)+strToInt(strData));
AA.Strings[Recno-1] := strData1;
end else
begin
strData := DM.ADOQuery.FieldByName('CountNum').AsString;
WriteLn(F,strData);
end;
Next;
end;
end;
if row =1 then
CloseFile(F)
else
begin
aa.SaveToFile(strFileName);
aa.Free;
end;
end;

查看回复