如何提高这段sql 代码的速度 急!!!!!(200)

  • 主题发起人 主题发起人 victor9871
  • 开始时间 开始时间
V

victor9871

Unregistered / Unconfirmed
GUEST, unregistred user!
procedure TFrm_A_Sale.CreateReport;var TempCity,TempCode,TempIn,TempOut,TempCityIn,TempCityOut,n,nIn,nout :string; i,j :integer;begin DBGridEh1.Columns.Clear; Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'delete from T_FieldShow where TableName=''TempDetail'''); try Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'drop table T_TempDetail'); Except end; Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES (''IDD'',''序号'',0,50,''TempDetail'',0)'); Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES (''Goodsname'',''设备名称'',1,200,''TempDetail'',0)'); Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES (''SortCount'',''总数量'',1,50,''TempDetail'',1)'); Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES (''SortCountIn'',''保内'',1,50,''TempDetail'',1)'); Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES (''SortCountOut'',''保外'',1,50,''TempDetail'',1)'); try Frm_Data.ExecDataSet(Frm_Data.Qry_Temp,'Create table T_TempDetail (IDD Integer NULL,Goodsname varchar(100),SortCount float,SortCountIn float,SortCountOut float)'); Except end; for i := 0 to CheckListBox1.Items.Count - 1 do begin if CheckListBox1.Checked then begin TempCity:=CheckListBox1.Items; TempCityIn:=TempCity+'|保内'; TempCityOut:=TempCity+'|保外'; TempIn:='TempIn'+inttostr(i); TempOut:='TempOut'+inttostr(i); try Frm_Data.ExecDataSet(Frm_Data.Qry_Sql,'ALTER table T_TempDetail Add '+TempIn+' float,'+TempOut+' float'); Except end; Frm_Data.ExecDataSet(Frm_Data.Qry_Sql,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES ('''+TempIn+''','''+TempCityIn+''',1,50,''TempDetail'',1)'); Frm_Data.ExecDataSet(Frm_Data.Qry_Sql,'Insert Into T_FieldShow(field,name,show,width,TableName,SumField) VALUES ('''+TempOut+''','''+TempCityOut+''',1,50,''TempDetail'',1)'); if Combo_gys.Text='' then Frm_Data.OpenDataSet(Frm_Data.Qry_Str,'select DISTINCT goodsname from view1 where [model]='''+TempCity+'''') else Frm_Data.OpenDataSet(Frm_Data.Qry_Str,'select DISTINCT goodsname,name from view1 where [name]='''+Combo_gys.Text+''' and [model]='''+TempCity+''''); Frm_Data.Qry_Str.First; For j:=1 to Frm_Data.Qry_Str.RecordCount do begin TempCode:=Frm_Data.Qry_Str.FieldByName('Goodsname').AsString; n:='0'; nIn:='0'; nOut:='0'; Frm_Data.OpenDataSet(Frm_Data.Qry_FieldShow,'select Goodsname,SortCount,SortCountIn,SortCountOut from T_TempDetail where Goodsname='''+TempCode+''''); if Frm_Data.Qry_FieldShow.RecordCount=0 then begin Frm_Data.ExecDataSet(Frm_Data.Qry_Sql,'Insert Into T_TempDetail(Goodsname) VALUES ('''+TempCode+''')'); end; Frm_Data.OpenDataSet(Frm_Data.Qry_TempIn,'select [Number] from view1 where [model]='''+TempCity+''' and Goodsname='''+TempCode+''' and buyDate>='''+DateToStr(Date1.Date)+'''');//保内 Frm_Data.OpenDataSet(Frm_Data.Qry_TempOut,'select [Number] from view1 where [model]='''+TempCity+''' and Goodsname='''+TempCode+''' and buyDate<='''+DateToStr(Date1.Date)+'''');//保外 Frm_Data.ExecDataSet(Frm_Data.Qry_TempBill,'Update T_TempDetail set '+TempIn+'='+inttostr(Frm_Data.Qry_TempIn.RecordCount)+','+TempOut+'='+inttostr(Frm_Data.Qry_TempOut.RecordCount)+' where [Goodsname]='''+TempCode+''''); if Frm_Data.Qry_FieldShow.FieldByName('SortCount').AsString='' then n:=inttostr(strtoint(n)+Frm_Data.Qry_TempIn.RecordCount+Frm_Data.Qry_TempOut.RecordCount) else n:=inttostr(strtoint(n)+Frm_Data.Qry_TempIn.RecordCount+Frm_Data.Qry_TempOut.RecordCount+Frm_Data.Qry_FieldShow.FieldByName('SortCount').AsInteger); //某货品的总数 if Frm_Data.Qry_FieldShow.FieldByName('SortCountIn').AsString='' then nIn:=inttostr(strtoint(nIn)+Frm_Data.Qry_TempIn.RecordCount) else nIn:=inttostr(strtoint(nIn)+Frm_Data.Qry_TempIn.RecordCount+Frm_Data.Qry_FieldShow.FieldByName('SortCountIn').AsInteger); if Frm_Data.Qry_FieldShow.FieldByName('SortCountOut').AsString='' then nOut:=inttostr(strtoint(nOut)+Frm_Data.Qry_TempOut.RecordCount) else nOut:=inttostr(strtoint(nOut)+Frm_Data.Qry_TempOut.RecordCount+Frm_Data.Qry_FieldShow.FieldByName('SortCountOut').AsInteger); Frm_Data.ExecDataSet(Frm_Data.Qry_Sql,'Update T_TempDetail set [SortCount]='+n+',[SortCountIn]='+nIn+',[SortCountOut]='+nOut+' where [Goodsname]='''+TempCode+''''); Frm_Data.Qry_Str.Next; end; end; //-------------------------------------- end; Frm_Data.OpenDataSet(Frm_Data.Qry_Str,'select * from T_FieldShow where show=1 and TableName=''TempDetail'' order by ID'); Frm_Data.Qry_Str.First; For i:=0 to Frm_Data.Qry_Str.RecordCount-1 do begin TempCode:=Frm_Data.Qry_Str.FieldByName('ID').AsString; Frm_Data.ExecDataSet(Frm_Data.Qry_TempBill,'Update T_FieldShow set [ShowID]='+inttostr(i)+' where [ID]='+TempCode+''); Frm_Data.Qry_Str.Next; end; Frm_Data.OpenDataSet(Frm_Data.Qry_Str,'select * from T_TempDetail order by Goodsname'); Frm_Data.Qry_Str.First; For i:=1 to Frm_Data.Qry_Str.RecordCount do begin TempCode:=Frm_Data.Qry_Str.FieldByName('Goodsname').AsString; Frm_Data.ExecDataSet(Frm_Data.Qry_TempBill,'Update T_TempDetail set [IDD]='+inttostr(i)+' where Goodsname='''+TempCode+''''); Frm_Data.Qry_Str.Next; end; Frm_Data.OpenDataSet(Qry_Count,'select * from T_TempDetail order by Goodsname'); Frm_main.AddToGridEh(DBGridEh1,Qry_Count,'TempDetail');end;
 
建议你写成存储过程更好一些~~
 
看上去每个sql语句都很简单,不会浪费时间,但由于执行的语句多,导致速度慢。1.最后更新T_TempDetail的方法可以不需要用循环:Update T_TempDetailSet IDD = (Select count(*) from T_TempDetail b where b.Goodsname<=T_TempDetail.Goodsname)看你前面对日期字段的操作,猜测应该是sql server数据库。
 
谢谢二位了 我试试
 
关于TempDetail数据表中的数据插入,可以在for循环之前执行这样的语句: Insert into T_TempDetail(Goodsname) select goodsname From (Select DISTINCT goodsname from view1 where ...) A where not Exists (Select 1 From T_TempDetail b where b.Goodsname=a.Goodsname)可能语法编译有些错误,自己调试一下。
 
关于T_TempDetail的另外3个字段还可以用类似这样的方法批量更新,而不用更新多次:Update T_TempDetailSet SortCount=x.SortCount, SortCountIn= X.S_IN, SortCountOut=x.S_OutFrom T_TempDetail, (Select Goodsname, Sum(S_IN) as S_IN, Sum(S_Out) S_Out, sum(SortCount) as SortCount From (Select Goodsname, Sum(Case when buydate>'2009-01-01' then 1 else 0 end) as S_IN, Sum(Case when buydate<='2009-01-01' then 1 else 0 end) as S_Out, 0 AS SortCount From view1 Group by Goodsname Union All Select Goodsname, 0 as S_IN, 0 as S_Out, Count(SortCount) as SortCount From T_TempDetail Group by Goodsname ) A group by goodsName ) XWhere T_TempDetail.GooldName=X.Goodsname可能语法编译有些错误,自己调试一下。 ---------------------------------------------如果上面的几个语句看明白,且写入到你函数里了,那么你的代码将会大大简化,至于速度,应该会比以前快(注意建立适当的索引)。----------------------------希望能早点结贴,给点分给我,呵呵
 
znxia 我这边还是有点不会调试啊 能否放到我整个代码里面去啊
 
抱歉,我只帮人提供思路,不帮人写代码。况且,我也没完全明白你的程序,只是知道可以通过这种方式来减少执行SQL的次数,提高速度。
 
接受答案了.
 
后退
顶部