执行速度特别慢,麻烦帮忙看一下.(100分)

yhd99 · 2006-08-11

程序如下:一分钟只能插入100条数据左右,有什么提高速度的办法吗? procedure TForm1.Button3Click(Sender: TObject); var  yhd,hy_id,jffs_id,com_date,bank_id:string;  presenttime:tdatetime;  nian,yue,ri:word;  total_number,now_number,i:integer; begin  for i:=0 to listbox1.Items.Count -1  do  begin   presenttime:=now;   decodedate(presenttime,nian,yue,ri);   if yue<10 then   com_date:=inttostr(nian)+'-0'+inttostr(yue)   else   com_date:=inttostr(nian)+'-'+inttostr(yue);   query17.Close;   query17.sql.Clear;   query17.SQL.add('select * from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and area_book=:b') ;   //query17.SQL.add('select * from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and exp_sum < 0') ;   query17.parambyname('a').value:=com_date;   query17.parambyname('b').value:=listbox1.Items;   query17.ExecSQL ;   query17.Open;   total_number:=query17.recordcount;   now_number:=0;   progressbar1.Min:=0;   progressbar1.Max:=total_number;   table18.close;   table18.TableName:='EP_AMMETER_DATA';   table18.open;   table18.active:=true;   while (not query17.eof) do   begin   now_number:=now_number + 1;   ProgressBar1.Position:=now_number;   {query37.close;   query37.sql.clear;   query37.SQL.add('select * from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and kh_id=:b');   query37.parambyname('a').value:=com_date;   query37.parambyname('b').value:=query17.fieldbyname('kh_id').asstring;   query37.ExecSQL;   query37.open;   if query37.recordcount>=1 then   begin   query38.close;   query38.sql.clear;   query38.SQL.add('delete from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and kh_id=:b');   query38.parambyname('a').value:=com_date;   query38.parambyname('b').value:=query17.fieldbyname('kh_id').asstring;   query38.ExecSQL;   end; }      table18.append;      table18.fieldbyname('unit_id').asstring:=query17.fieldbyname('unit_id').asstring;      table18.fieldbyname('kh_id').asstring:=query17.fieldbyname('kh_id').asstring;      yhd:=query17.fieldbyname('kh_id').asstring;      table18.fieldbyname('jld_id').asstring:=query17.fieldbyname('jld_id').asstring;      table18.fieldbyname('abk_id').asstring:=query17.fieldbyname('abk_id').asstring;      table18.fieldbyname('ammeter_no').asstring:=query17.fieldbyname('ammeter_no').asstring;      table18.fieldbyname('price_id').asstring:=query17.fieldbyname('price_id').asstring;      table18.fieldbyname('add_id').asstring:=query17.fieldbyname('add_id').asstring;      table18.fieldbyname('area_book').asstring:=query17.fieldbyname('area_book').asstring;      table18.fieldbyname('exp_time').asdatetime:=query17.fieldbyname('exp_time').asdatetime;      table18.fieldbyname('compute_date').asdatetime:=query17.fieldbyname('compute_date').asdatetime;      table18.fieldbyname('ep_status').asstring:=query17.fieldbyname('ep_status').asstring;      table18.fieldbyname('mount_capa').asfloat:=query17.fieldbyname('mount_capa').asfloat;      table18.fieldbyname('smooth_capa').asfloat:=query17.fieldbyname('smooth_capa').asfloat;      table18.fieldbyname('valley_capa').asfloat:=query17.fieldbyname('valley_capa').asfloat;      table18.fieldbyname('nop_capa').asfloat:=query17.fieldbyname('nop_capa').asfloat;      table18.fieldbyname('elec_exp').asfloat:=query17.fieldbyname('elec_exp').asfloat;      table18.fieldbyname('capacity').asinteger:=query17.fieldbyname('capacity').asinteger;      table18.fieldbyname('capa_e').asfloat:=query17.fieldbyname('capa_e').asfloat;      table18.fieldbyname('need_capa').asfloat:=query17.fieldbyname('need_capa').asfloat;      table18.fieldbyname('need_e').asfloat:=query17.fieldbyname('need_e').asfloat;      table18.fieldbyname('power_factor').asfloat:=query17.fieldbyname('power_factor').asfloat;      table18.fieldbyname('adj_ratio').asfloat:=query17.fieldbyname('adj_ratio').asfloat;      table18.fieldbyname('adj_plus').asfloat:=query17.fieldbyname('adj_plus').asfloat;      table18.fieldbyname('adj_neg').asfloat:=query17.fieldbyname('adj_neg').asfloat;      table18.fieldbyname('nwhd').asfloat:=query17.fieldbyname('nwhd').asfloat;      table18.fieldbyname('sxjj').asfloat:=query17.fieldbyname('sxjj').asfloat;      table18.fieldbyname('zhpt').asfloat:=query17.fieldbyname('zhpt').asfloat;      table18.fieldbyname('csfj').asfloat:=query17.fieldbyname('csfj').asfloat;      table18.fieldbyname('kqjj').asfloat:=query17.fieldbyname('kqjj').asfloat;      table18.fieldbyname('other1').asfloat:=query17.fieldbyname('other1').asfloat;      table18.fieldbyname('other2').asfloat:=query17.fieldbyname('other2').asfloat;      table18.fieldbyname('other3').asfloat:=query17.fieldbyname('other3').asfloat;      table18.fieldbyname('mount_exp').asfloat:=query17.fieldbyname('mount_exp').asfloat;      table18.fieldbyname('smooth_exp').asfloat:=query17.fieldbyname('smooth_exp').asfloat;      table18.fieldbyname('valley_exp').asfloat:=query17.fieldbyname('valley_exp').asfloat;      table18.fieldbyname('power_sum').asfloat:=query17.fieldbyname('power_sum').asfloat;      table18.fieldbyname('exp_sum').asfloat:=query17.fieldbyname('exp_sum').asfloat;      table18.fieldbyname('r_p_money').asfloat:=query17.fieldbyname('r_p_money').asfloat;      table18.fieldbyname('error_rp_money').asfloat:=query17.fieldbyname('error_rp_money').asfloat;      table18.fieldbyname('tine_capa').asfloat:=query17.fieldbyname('tine_capa').asfloat;      table18.fieldbyname('ridge_capa').asfloat:=query17.fieldbyname('ridge_capa').asfloat;      table18.fieldbyname('tine_exp').asfloat:=query17.fieldbyname('tine_exp').asfloat;      table18.fieldbyname('ridge_exp').asfloat:=query17.fieldbyname('ridge_exp').asfloat;      table18.fieldbyname('isadd').asstring:=query17.fieldbyname('isadd').asstring;      table18.fieldbyname('serial_no').asstring:=query17.fieldbyname('serial_no').asstring;      {query20.close;      query20.sql.clear;      query20.sql.add('select * from ep_power_price_dic where price_id=:a');      query20.parambyname('a').value:=query17.fieldbyname('price_id').asstring;      query20.execsql;      query20.open;      table18.FieldByName('price_type').asstring:=query20.fieldbyname('price_type').asstring;      table18.fieldbyname('n_basep').asfloat:=query20.fieldbyname('n_basep').asfloat;      table18.fieldbyname('c_basep').asfloat:=query20.fieldbyname('c_basep').asfloat;      table18.fieldbyname('vol_grade').asstring:=query20.fieldbyname('vol_grade').asstring;      table18.fieldbyname('p_mount').asfloat:=query20.fieldbyname('p_mount').asfloat;      table18.fieldbyname('p_smooth').asfloat:=query20.fieldbyname('p_smooth').asfloat;      table18.fieldbyname('p_valley').asfloat:=query20.fieldbyname('p_valley').asfloat;      table18.fieldbyname('p_tine').asfloat:=query20.fieldbyname('p_tine').asfloat;      table18.fieldbyname('p_ridge').asfloat:=query20.fieldbyname('p_ridge').asfloat;      query20.close;      query20.sql.clear;      query20.sql.add('select * from ep_province_add_dic where add_id=:a');      query20.parambyname('a').value:=query17.fieldbyname('add_id').asstring;      query20.execsql;      query20.open;      table18.fieldbyname('nwhd_price').asfloat:=query20.fieldbyname('nwhd').asfloat;      table18.fieldbyname('sxjj_price').asfloat:=query20.fieldbyname('sxjj').asfloat;      table18.fieldbyname('csfj_price').asfloat:=query20.fieldbyname('csfj').asfloat;      table18.fieldbyname('zhpt_price').asfloat:=query20.fieldbyname('zhpt').asfloat;      table18.fieldbyname('kqjj_price').asfloat:=query20.fieldbyname('kqjj').asfloat;      table18.fieldbyname('other1_price').asfloat:=query20.fieldbyname('other1').asfloat;      table18.fieldbyname('other2_price').asfloat:=query20.fieldbyname('other2').asfloat;      table18.fieldbyname('other3_price').asfloat:=query20.fieldbyname('other3').asfloat; }      query19.close;      query19.SQL.clear;      query19.sql.add('select * from po_khjbxx where kh_id=:a');      query19.parambyname('a').value:=yhd;     // showmessage(query19.SQL.DelimitedText);      query19.ExecSQL;      query19.open;      if query19.RecordCount<1 then      else      begin      hy_id:=query19.fieldbyname('hy_id').asstring;      table18.fieldbyname('hy_id').asstring:=query19.fieldbyname('hy_id').asstring;      if query19.fieldbyname('kh_name').asstring='' then table18.fieldbyname('kh_name').asstring:='-'      else table18.fieldbyname('kh_name').asstring:=query19.fieldbyname('kh_name').asstring;      if query19.fieldbyname('yddz').asstring='' then table18.fieldbyname('yddz').asstring:='-'      else table18.fieldbyname('yddz').asstring:=query19.fieldbyname('yddz').asstring;      if query19.fieldbyname('lxdz').asstring='' then table18.fieldbyname('lxdz').asstring:='-'      else table18.fieldbyname('lxdz').asstring:=query19.fieldbyname('lxdz').asstring;      if query19.fieldbyname('ydlb_id').asstring='' then table18.fieldbyname('ydlb_id').asstring:='-'      else table18.fieldbyname('ydlb_id').asstring:=query19.fieldbyname('ydlb_id').asstring;      table18.fieldbyname('jffs').asstring:=query19.fieldbyname('jffs').asstring;      jffs_id:=query19.fieldbyname('jffs').asstring;      table18.fieldbyname('gdfs').asstring:=query19.fieldbyname('gdfs').asstring;      table18.fieldbyname('user_type').asstring:=query19.fieldbyname('user_type').asstring;      table18.fieldbyname('yhlx_id').asstring:=query19.fieldbyname('yhlx_id').asstring;      if query19.fieldbyname('tel').asstring='' then table18.fieldbyname('link_tel').asstring:='-'      else table18.fieldbyname('link_tel').asstring:=query19.fieldbyname('link_tel').AsString;      end;      query20.close;      query20.sql.clear;      query20.sql.add('select * from po_hyxx where hy_id=:a');      query20.parambyname('a').Value :=hy_id;      query20.execsql;      query20.open;      table18.fieldbyname('hy_name').asstring:=query20.fieldbyname('hy_name').asstring;      table18.fieldbyname('fjhy_id').asstring:=query20.fieldbyname('fjhy_id').asstring;      query20.close;      query20.sql.clear;      query20.sql.add('select * from ep_code_dic_deatail where sort_id=''12'' and code_id=:a');      query20.parambyname('a').value:=jffs_id;      query20.execsql;      query20.open;      table18.fieldbyname('jffs_name').asstring:=query20.fieldbyname('code_name').asstring;      query20.close;      query20.sql.clear;      query20.sql.add('select * from ep_abn_para where ammeter_no=:a');      query20.parambyname('a').value:=query17.fieldbyname('ammeter_no').asstring;      query20.execsql;      query20.open;      table18.fieldbyname('multiple').asfloat:=query20.fieldbyname('multiple').asfloat;      //table18.fieldbyname('amm_num').asinteger:=;      table18.fieldbyname('line_time').asdatetime:=query20.fieldbyname('lin_time').asdatetime;      table18.fieldbyname('in_time').asdatetime:=query20.fieldbyname('in_time').asdatetime;      table18.fieldbyname('main_now').asfloat:=query20.fieldbyname('main_now').asfloat;      table18.fieldbyname('mount_now').asfloat:=query20.fieldbyname('mount_now').asfloat;      table18.fieldbyname('smooth_now').asfloat:=query20.fieldbyname('smooth_now').asfloat;      table18.fieldbyname('valley_now').asfloat:=query20.fieldbyname('valley_now').asfloat;      table18.fieldbyname('tine_now').asfloat:=query20.fieldbyname('tine_now').asfloat;      table18.fieldbyname('ridge_now').asfloat:=query20.fieldbyname('ridge_now').asfloat;      table18.fieldbyname('nop_now').asfloat:=query20.fieldbyname('nop_now').asfloat;      table18.fieldbyname('need_reading').asfloat:=query20.fieldbyname('need_reading').asfloat;      table18.fieldbyname('main_last').asfloat:=query20.fieldbyname('main_last').asfloat;      table18.fieldbyname('mount_last').asfloat:=query20.fieldbyname('mount_last').asfloat;      table18.fieldbyname('smooth_last').asfloat:=query20.fieldbyname('smooth_last').asfloat;      table18.fieldbyname('valley_last').asfloat:=query20.fieldbyname('valley_last').asfloat;      table18.fieldbyname('tine_last').asfloat:=query20.fieldbyname('tine_last').asfloat;      table18.fieldbyname('ridge_last').asfloat:=query20.fieldbyname('ridge_last').asfloat;      table18.fieldbyname('nop_last').asfloat:=query20.fieldbyname('nop_last').asfloat;      table18.fieldbyname('unusual_tag').asstring:=query20.fieldbyname('unusual_tag').asstring;      table18.fieldbyname('input_tag').asstring:=query20.fieldbyname('input_tag').asstring;      table18.fieldbyname('chg_sign').asstring:=query20.fieldbyname('chg_sign').asstring;      table18.fieldbyname('p_chg_power').asfloat:=query20.fieldbyname('p_chg_power').asfloat;      table18.fieldbyname('v_chg_power').asfloat:=query20.fieldbyname('v_chg_power').asfloat;      table18.fieldbyname('s_chg_power').asfloat:=query20.fieldbyname('n_chg_power').asfloat;      table18.fieldbyname('t_chg_power').asfloat:=query20.fieldbyname('t_chg_power').asfloat;      table18.fieldbyname('r_chg_power').asfloat:=query20.fieldbyname('r_chg_power').asfloat;      table18.fieldbyname('power_trans').asfloat:=query20.fieldbyname('power_trans').asfloat;      table18.fieldbyname('no_power_trans').asfloat:=query20.fieldbyname('no_power_trans').asfloat;      table18.fieldbyname('lloss_capa').asfloat:=query20.fieldbyname('lloss_capa').asfloat;      {query20.close;      query20.sql.clear;      query20.sql.add('select * from po_ammeter_detail where abk_id=:a');      query20.parambyname('a').value:=query17.fieldbyname('abk_id').asstring;      query20.execsql;      query20.open;      table18.fieldbyname('fix_ratio').asfloat:=query20.fieldbyname('fix_ratio').asfloat;      table18.fieldbyname('fix_capa').asfloat:=query20.fieldbyname('fix_capa').asfloat; }      query21.Close;      query21.sql.clear;      query21.sql.add('select * from ep_area_book_info where area_book=:a');      query21.parambyname('a').value:=query17.fieldbyname('area_book').asstring;      query21.execsql;      query21.open;      if query21.fieldbyname('copy_id').asstring='' then table18.fieldbyname('charge_id').asstring:='-'      else table18.fieldbyname('charge_id').asstring:=query21.fieldbyname('copy_id').asstring;      if query21.fieldbyname('copy_name').asstring='' then table18.fieldbyname('charge_man').asstring:='-'      else table18.fieldbyname('charge_man').asstring:=query21.fieldbyname('copy_name').asstring;      if query21.fieldbyname('yyz_id').asstring='' then table18.fieldbyname('yyz_id').asstring:='-'      else table18.fieldbyname('yyz_id').asstring:=query21.fieldbyname('yyz_id').asstring;      if query21.fieldbyname('yyz_name').asstring='' then table18.fieldbyname('yyz_name').asstring:='-'      else table18.fieldbyname('yyz_name').asstring:=query21.fieldbyname('yyz_name').asstring;      query20.close;      query20.sql.clear;      query20.sql.add('select * from ep_user_para where kydw_id=:a');      query20.parambyname('a').value:=query17.fieldbyname('kh_id').asstring;      query20.execsql;      query20.open;      if query20.RecordCount<1 then      else      begin        if query20.fieldbyname('balance_name').asstring='' then table18.fieldbyname('balance_name').asstring:='-'        else table18.fieldbyname('balance_name').asstring:=query20.fieldbyname('balance_name').asstring;        if query20.fieldbyname('cons_id').asstring='' then table18.fieldbyname('cons_id').asstring:='-'        else table18.fieldbyname('cons_id').asstring:=query20.fieldbyname('cons_id').asstring;        if query20.fieldbyname('bank_id').asstring='' then table18.FieldByName('bank_id').asstring:='-'        else table18.fieldbyname('bank_id').asstring:=query20.fieldbyname('bank_id').asstring;        bank_id:=query20.fieldbyname('bank_id').asstring;        if query20.fieldbyname('account_id').asstring='' then table18.fieldbyname('account_id').asstring:='-'        else table18.fieldbyname('account_id').asstring:=query20.fieldbyname('account_id').asstring;        query20.close;        query20.sql.clear;        query20.sql.add('select * from po_bank where bank_id=:a');        query20.parambyname('a').value:=bank_id;        query20.ExecSQL;        query20.Open;        if query20.fieldbyname('bank_name').asstring='' then table18.fieldbyname('bank_name').asstring:='-'        else table18.fieldbyname('bank_name').asstring:=query20.fieldbyname('bank_name').asstring;      end;      query17.next;      table18.post;   end;   end; end;

zywcd · 2006-08-11

看起来累。 你很多地方都可以修改。 //    query17.sql.add('select * from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and area_book=:b');   //为了取得记录总数代价太大....    query17.sql.add('select count(*) as 总数 from ep_ammeter_data where to_char(exp_time,''yyyy-mm'')=:a and area_book=:b');

yhd99 · 2006-08-11

不好意思,query17还要循环,并不只是去总数

zywcd · 2006-08-11

这样做真的好累

yhd99 · 2006-08-11

to zywcd 您认为如何修改为好呢?

Johnny_du · 2006-08-11

看起来眼花、头大...你应当对代码优化。

kspaul · 2006-08-11

也太长了点，建议能用存储过程的用存储过程，或是把数据放在结构体中也许会快多，至少没你这么乱，编程也要professional

cqwty · 2006-08-11

先把需求分析清楚吧，这样建数据库的时候根据需求，进行处理，会提高优化的速度，还有就是使用存储过程，将数据处理这一块丢给数据库服务器，这两个办法都是优化的办法。

lqy · 2006-08-11

直接写Insert语句,应该会提高不少 fieldbyname('').as..:=fieldbyname('').as...; 这种方法是最低效的,时间都浪费在查找字段和数据转换上面了。

protossgffnxk · 2006-08-11

看得真累呀！！！

yhd99 · 2006-08-11

问题是这样,要从多个表中取值,然后插入另外的一个表中.比如说a、b、c三个表，如果用多表关联的话，假如b表没有符合的记录，那么检索出来的结果为空。但是只要a表有数据，无论b表是否有对应的数据，都得向d表中插入数据。 所以这个关联sql语句不知道如何去写，所以才写的这么复杂。另外这三个表都是别的系统建立的表,不能修改结构.

网中戏 · 2006-08-11

用存储过程。每次只要传递新值即可。很快的

xcplates · 2006-08-12

如果数据库用SQL SERVER 那么这些操作可以考虑用存储过程再配合一些视图

xcplates · 2006-08-12

query19.ExecSQL; query19.open; 这两行连在一起用是什么意思呀

执行速度特别慢,麻烦帮忙看一下.(100分)

yhd99

Unregistered / Unconfirmed

zywcd

Unregistered / Unconfirmed

yhd99

Unregistered / Unconfirmed

zywcd

Unregistered / Unconfirmed

yhd99

Unregistered / Unconfirmed

Johnny_du

Unregistered / Unconfirmed

kspaul

Unregistered / Unconfirmed

cqwty

Unregistered / Unconfirmed

lqy

Unregistered / Unconfirmed

protossgffnxk

Unregistered / Unconfirmed

yhd99

Unregistered / Unconfirmed

网中戏

Unregistered / Unconfirmed

xcplates

Unregistered / Unconfirmed

xcplates

Unregistered / Unconfirmed

Similar threads