效率,效率!dbf 插入 oracle
dbf表不停刷新,最大可有5w条以上记录.程序要求不停扫描此dbf表,把新增数据导入oracle.
但dbf表无唯一id之类的字段,
主要的代码如下,但效率太低.请大家看看怎么优化?
//打开dbf表(dbfsj.dbf)
with dbf_query do
begin
close;
sql.clear;
sql.add('select * from dbfsj ');
open;
end;
//定位到上次最后插入oracle的纪录,如果没有,就定位到第一条记录.
try
dbf_query.Locate('sj;kh', VarArrayOf([ora_lasttime,ora_lastcust]), [loPartialKey]);
except
dbf_query.first;
end;
//如果有新增记录,逐条按dbf的记录构造sql语句(dtssql)
while not dbf_query.eof do
begin
dtssql := 'insert into ora_dbfsj (';
for i := 0 to dbf_Query.FieldCount-1 do
begin
if i <> dbf_Query.FieldCount-1 then
dtssql := dtssql + dbf_Query.Fields[i].FieldName+','
else
dtssql := dtssql + dbf_Query.Fields[i].FieldName+') values (';
end;
for i := 0 to dbf_Query.FieldCount-1 do
begin
if i <> dbf_Query.FieldCount-1 then
begin
dtssql := dtssql + '''' + dbf_Query.Fields[i].asString+ ''','
else
dtssql := dtssql + '''' + dbf_Query.Fields[i].asString+ ''')' ;
end;
end;
//一条语句构造好之后,在oracle中执行,即插入一条dbf的记录.
with oracle_query1 do
begin
close;
sql.clear;
sql.add(dtssql);
try
execsql;
except
showmessage(dtssql);
end;
end;
dbf_query.next;
end;
//最后处理的记录的两个字段的值
ora_lasttime := dbf_query.fieldbyname('sj').asstring;
ora_lastcust := dbf_query.fieldbyname('kh').asstring;
//把最后处理的两个字段值记录下来.
with oracle_query1 do
begin
close;
sql.clear;
sql.add('update sms_ls1 set lastcust='+''''+ora_lastcust+''''+',lasttime='+''''+ora_lasttime+''''+' where deptcode='+''''+ora_deptcode+'''' );
execsql;
end;
最主要慢在读dbf的数据,和插入oracle这两个地方,请大家赐教,如何优化?