关于ADO的效率问题!!可能也是程序的效率问题!!急~~~

sprite14 2003-05-07 09:31:25
我有一程序,用ADO做连接,象数据库中插入数据
代码如下

Do Until EOF(f3)
Line Input #f3, inputvar2
rst3.AddNew
rst3.Fields(0).Value = databasecode2

If RTrim(LTrim(StrMid(inputvar2, 1, 10))) <> "" Then rst3.Fields(1).Value = RTrim(LTrim(StrMid(inputvar2, 1, 10))) Else rst3.Fields(1).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 83, 30))) <> "" Then rst3.Fields(4).Value = RTrim(LTrim(StrMid(inputvar2, 83, 30))) Else rst3.Fields(4).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 65, 10))) <> "" Then rst3.Fields(2).Value = RTrim(LTrim(StrMid(inputvar2, 65, 10))) Else rst3.Fields(2).Value = " "
If StrMid(inputvar2, 113, 1) <> "" Then rst3.Fields(5).Value = StrMid(inputvar2, 113, 1) Else rst3.Fields(5).Value = " "
If StrMid(inputvar2, 114, 1) <> "" Then rst3.Fields(6).Value = StrMid(inputvar2, 114, 1) Else rst3.Fields(6).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 115, 1))) <> "" Then rst3.Fields(7).Value = StrMid(inputvar2, 115, 1) Else rst3.Fields(7).Value = " "
If StrMid(inputvar2, 310, 15) <> "" Then rst3.Fields(31).Value = StrMid(inputvar2, 310, 15) Else rst3.Fields(31).Value = " "
If StrMid(inputvar2, 325, 15) <> "" Then rst3.Fields(32).Value = StrMid(inputvar2, 325, 15) Else rst3.Fields(32).Value = " "
If StrMid(inputvar2, 340, 15) <> "" Then rst3.Fields(33).Value = StrMid(inputvar2, 340, 15) Else rst3.Fields(33).Value = " "
If StrMid(inputvar2, 355, 15) <> "" Then rst3.Fields(34).Value = StrMid(inputvar2, 355, 15) Else rst3.Fields(34).Value = " "
If StrMid(inputvar2, 370, 15) <> "" Then rst3.Fields(35).Value = StrMid(inputvar2, 370, 15) Else rst3.Fields(35).Value = " "
If StrMid(inputvar2, 385, 15) <> "" Then rst3.Fields(36).Value = StrMid(inputvar2, 385, 15) Else rst3.Fields(36).Value = " "
If StrMid(inputvar2, 400, 15) <> "" Then rst3.Fields(37).Value = StrMid(inputvar2, 400, 15) Else rst3.Fields(37).Value = " "
If StrMid(inputvar2, 415, 15) <> "" Then rst3.Fields(38).Value = StrMid(inputvar2, 415, 15) Else rst3.Fields(38).Value = " "
If StrMid(inputvar2, 430, 15) <> "" Then rst3.Fields(39).Value = StrMid(inputvar2, 430, 15) Else rst3.Fields(39).Value = " "
If StrMid(inputvar2, 445, 15) <> "" Then rst3.Fields(40).Value = StrMid(inputvar2, 445, 15) Else rst3.Fields(40).Value = " "
If StrMid(inputvar2, 212, 8) <> "" Then rst3.Fields(25).Value = StrMid(inputvar2, 212, 8) Else rst3.Fields(25).Value = " "
If StrMid(inputvar2, 159, 10) <> "" Then rst3.Fields(13).Value = StrMid(inputvar2, 159, 10) Else rst3.Fields(13).Value = " "
If StrMid(inputvar2, 535, 10) <> "" Then rst3.Fields(44).Value = StrMid(inputvar2, 535, 10) Else rst3.Fields(44).Value = " "
If StrMid(inputvar2, 122, 1) <> "" Then rst3.Fields(10).Value = StrMid(inputvar2, 122, 1) Else rst3.Fields(10).Value = " "
'If strMid(inputvar2, 524, 11) <> "" Then c15 = strMid(inputvar2, 524, 11) Else c21 = "null"
If StrMid(inputvar2, 117, 5) <> "" Then rst3.Fields(9).Value = StrMid(inputvar2, 117, 5) Else rst3.Fields(9).Value = " "
If StrMid(inputvar2, 116, 1) <> "" Then rst3.Fields(8).Value = StrMid(inputvar2, 116, 1) Else rst3.Fields(8).Value = " "
If StrMid(inputvar2, 545, 5) <> "" Then rst3.Fields(45).Value = StrMid(inputvar2, 545, 5) Else rst3.Fields(45).Value = " "
If StrMid(inputvar2, 220, 1) <> "" Then rst3.Fields(26).Value = StrMid(inputvar2, 220, 1) Else rst3.Fields(26).Value = " "
If StrMid(inputvar2, 138, 20) <> "" Then rst3.Fields(12).Value = StrMid(inputvar2, 138, 20) Else rst3.Fields(12).Value = " "
rst3.Update
Loop
Close #f3

如果我的F3有1000多条数据的话
我这种更新方式会耗时10多秒的时间
请问我有什么好方法可以增加这个效率那?
...全文
6 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
时间可能花费在循环和打开关闭数据库上了。
使用批更新看看。
还有,检查一下表的索引
回复
leswang107 2003-05-07
用事务提交的方式速度应该会有所提高:
conn.begintrance
conn.execute "insert into...."
conn.execute "insert into..."
....
conn.committrance
如果是SQLSERVER数据库的话还可以试试用存储过程,插入记录时只需传递数值参数即可。
回复
sprite14 2003-05-07
我按 flyahead0(苯鸟先飞) 的建议进行了修改
但效果也并不乐观
请问有什么方法可以把我那1000多条数据一次插入到数据库中?
是不是因为我循环插入数据多的原因才导致的速度慢?
回复
CityhunterID 2003-05-07
写的太过与累赘了!

If RTrim(LTrim(StrMid(inputvar2, 1, 10))) <> "" Then rst3.Fields(1).Value = RTrim(LTrim(StrMid(inputvar2, 1, 10))) Else rst3.Fields(1).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 83, 30))) <> "" Then rst3.Fields(4).Value = RTrim(LTrim(StrMid(inputvar2, 83, 30))) Else rst3.Fields(4).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 65, 10))) <> "" Then rst3.Fields(2).Value = RTrim(LTrim(StrMid(inputvar2, 65, 10))) Else rst3.Fields(2).Value = " "
If StrMid(inputvar2, 113, 1) <> "" Then rst3.Fields(5).Value = StrMid(inputvar2, 113, 1) Else rst3.Fields(5).Value = " "
If StrMid(inputvar2, 114, 1) <> "" Then rst3.Fields(6).Value = StrMid(inputvar2, 114, 1) Else rst3.Fields(6).Value = " "
If RTrim(LTrim(StrMid(inputvar2, 115, 1))) <> "" Then rst3.Fields(7).Value = StrMid(inputvar2, 115, 1) Else rst3.Fields(7).Value = " "
If StrMid(inputvar2, 310, 15) <> "" Then rst3.Fields(31).Value = StrMid(inputvar2, 310, 15) Else rst3.Fields(31).Value = " "
If StrMid(inputvar2, 325, 15) <> "" Then rst3.Fields(32).Value = StrMid(inputvar2, 325, 15) Else rst3.Fields(32).Value = " "
If StrMid(inputvar2, 340, 15) <> "" Then rst3.Fields(33).Value = StrMid(inputvar2, 340, 15) Else rst3.Fields(33).Value = " "
If StrMid(inputvar2, 355, 15) <> "" Then rst3.Fields(34).Value = StrMid(inputvar2, 355, 15) Else rst3.Fields(34).Value = " "
If StrMid(inputvar2, 370, 15) <> "" Then rst3.Fields(35).Value = StrMid(inputvar2, 370, 15) Else rst3.Fields(35).Value = " "
If StrMid(inputvar2, 385, 15) <> "" Then rst3.Fields(36).Value = StrMid(inputvar2, 385, 15) Else rst3.Fields(36).Value = " "
If StrMid(inputvar2, 400, 15) <> "" Then rst3.Fields(37).Value = StrMid(inputvar2, 400, 15) Else rst3.Fields(37).Value = " "
If StrMid(inputvar2, 415, 15) <> "" Then rst3.Fields(38).Value = StrMid(inputvar2, 415, 15) Else rst3.Fields(38).Value = " "
If StrMid(inputvar2, 430, 15) <> "" Then rst3.Fields(39).Value = StrMid(inputvar2, 430, 15) Else rst3.Fields(39).Value = " "
If StrMid(inputvar2, 445, 15) <> "" Then rst3.Fields(40).Value = StrMid(inputvar2, 445, 15) Else rst3.Fields(40).Value = " "
If StrMid(inputvar2, 212, 8) <> "" Then rst3.Fields(25).Value = StrMid(inputvar2, 212, 8) Else rst3.Fields(25).Value = " "
If StrMid(inputvar2, 159, 10) <> "" Then rst3.Fields(13).Value = StrMid(inputvar2, 159, 10) Else rst3.Fields(13).Value = " "
If StrMid(inputvar2, 535, 10) <> "" Then rst3.Fields(44).Value = StrMid(inputvar2, 535, 10) Else rst3.Fields(44).Value = " "
If StrMid(inputvar2, 122, 1) <> "" Then rst3.Fields(10).Value = StrMid(inputvar2, 122, 1) Else rst3.Fields(10).Value = " "
'If strMid(inputvar2, 524, 11) <> "" Then c15 = strMid(inputvar2, 524, 11) Else c21 = "null"
If StrMid(inputvar2, 117, 5) <> "" Then rst3.Fields(9).Value = StrMid(inputvar2, 117, 5) Else rst3.Fields(9).Value = " "
If StrMid(inputvar2, 116, 1) <> "" Then rst3.Fields(8).Value = StrMid(inputvar2, 116, 1) Else rst3.Fields(8).Value = " "
If StrMid(inputvar2, 545, 5) <> "" Then rst3.Fields(45).Value = StrMid(inputvar2, 545, 5) Else rst3.Fields(45).Value = " "
If StrMid(inputvar2, 220, 1) <> "" Then rst3.Fields(26).Value = StrMid(inputvar2, 220, 1) Else rst3.Fields(26).Value = " "
If StrMid(inputvar2, 138, 20) <> "" Then rst3.Fields(12).Value = StrMid(inputvar2, 138, 20) Else rst3.Fields(12).Value = " "

……

这样的代码应该写成一个通用的过程或函数中。
If StrMid(inputvar2, 138, 20) <> "" Then rst3.Fields(12).Value = StrMid(inputvar2, 138, 20) Else rst3.Fields(12).Value = " "
回复
sql需要改进。
使用一条SQL插入语句。例:
sqlstr="insert into mytable(field1,field2,field3,...) values('" & iif(a=0,value1,value2) & "','" & iif(b=0,value3,value4) & "','" & iif(c=0,value5,value6) & "',... & "')"
execute sqlstr
回复
相关推荐
发帖
VB基础类
创建于2007-09-28

7489

社区成员

VB 基础类
申请成为版主
帖子事件
创建了帖子
2003-05-07 09:31
社区公告
暂无公告