加快与Access的沟通 [英] Speeding Up Communication With Access
问题描述
我创建了一个应用程序，每天从安全的
网站下载数据，并将该数据存储在Access数据库中。然后有
不同的选项允许用户在数据库上进行关键字和日期
搜索，并为他们显示信息。
一切看起来和功能都很棒，我唯一真正的不满
与我的应用程序是更新时间，在我的上一次测试中花费了大约45-46分钟为
9800条记录。
更新发生时，通常会执行三个步骤。数据
作为单个XML字符串下载，解析XML字符串，然后将新记录添加到数据库中。我使用INSERT
INTO将所有
记录从VB.NET添加到临时表（逐个），然后有几个查询合并临时表
数据进入决赛桌。
我已经确定下载大约占了
的8％处理时间，解析约需4％，与数据库的通信时间为88％（45-46分钟）。
这45分钟几乎完全由INSERT INTO语句组成
按以下方式一次添加一条记录......
... string sSQL（ INSERT INTO blah blah blah）在此之前构建了
...
myCnxn =新的OleDbConnection（sDBCnxnString）
cmUsrSQL =新的OleDbCommand（sSQL，myCnxn）
myCnxn.Open（）
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close（）
...构建下一个sSQL并重复该过程特德......
有没有办法大规模添加记录？如果我正在使用INSERT INTO，可以
我用一个SQL语句添加多条记录吗？
刚才我输入这个我'我想知道我浪费了多少时间
打开和关闭以及打开和关闭...
任何关于此主题的文章的任何链接都会非常感谢。
Thx。
I''ve created an application that downloads data daily from a secure
web site and stores that data in an Access database. Then there are
different options for allowing the user to do keyword and date
searches on the database and have the information displayed for them.
Everything looks and functions great, my only real dissatisfaction
with my application is the update time, which in my last test took
about 45-46 minutes for 9800 records.
When the update occurs, generally three steps are performed. The data
is downloaded as a single XML string, the XML string is parsed, and
then the new records are added to the database. I add all of the
records to a temporary table (one by one) from VB.NET using INSERT
INTO and then have a couple of queries that merge the temporary table
data into the final table.
I''ve have determined that the download is taking roughly 8% of the
process time, the parsing is taking about 4%, and the communication
with the database is taking 88% (45-46 minutes) of the time.
This 45 minutes almost exclusively consist of INSERT INTO statements
adding records one at a time in the following way...
...string sSQL (INSERT INTO blah blah blah) is constructed
right before this...
myCnxn = New OleDbConnection(sDBCnxnString)
cmUsrSQL = New OleDbCommand(sSQL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close()
...the next sSQL is constructed and the process is repeated...
Is there a way to add records in mass? If I''m using INSERT INTO, can
I add more than one record with a single SQL statement?
Just now as I type this I''m wondering how much time I''m wasting
opening and closing and opening and closing...
Any links to any articles on this topic would be greatly appreciated.
Thx.
推荐答案
需要注意的两件事。 />
1.不要打开和关闭连接。打开一次并执行数据
按要求插入
2.使用StringBuilder构建SQL。这可以帮助重复
迭代。
问候，
Trevor Benedict
MCSD
" ags5406" < my ********** @ gmail.comwrote in message
news：11 ******************** **@d55g2000hsg.googlegr oups.com ...
2 things to note.
1. DO NOT OPEN AND CLOSE THE CONNECTION. Open it once and perform the data
insert as required
2. Use a StringBuilder to build your SQL. This could help with repeated
iterations.
Regards,
Trevor Benedict
MCSD
"ags5406" <my**********@gmail.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...
我创建了一个每天从安全下载数据的应用程序
网站并将数据存储在Access数据库中。然后有
不同的选项允许用户在数据库上进行关键字和日期
搜索，并为他们显示信息。
一切看起来和功能都很棒，我唯一真正的不满
与我的应用程序是更新时间，在我的上一次测试中花费了大约45-46分钟为
9800条记录。
更新发生时，通常会执行三个步骤。数据
作为单个XML字符串下载，解析XML字符串，然后将新记录添加到数据库中。我使用INSERT
INTO将所有
记录从VB.NET添加到临时表（逐个），然后有几个查询合并临时表
数据进入决赛桌。
我已经确定下载大约占了
的8％处理时间，解析约需4％，与数据库的通信时间为88％（45-46分钟）。
这45分钟几乎完全由INSERT INTO语句组成
按以下方式一次添加一条记录......
... string sSQL（ INSERT INTO blah blah blah）在此之前构建了
...
myCnxn =新的OleDbConnection（sDBCnxnString）
cmUsrSQL =新的OleDbCommand（sSQL，myCnxn）
myCnxn.Open（）
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close（）
...下一个s构建SQL并重复该过程...
有没有办法大量添加记录？如果我正在使用INSERT INTO，可以
我用一个SQL语句添加多条记录吗？
刚才我输入这个我'我想知道我浪费了多少时间
打开和关闭以及打开和关闭...
任何关于此主题的文章的任何链接都会非常感谢。
Thx。
I''ve created an application that downloads data daily from a secure
web site and stores that data in an Access database. Then there are
different options for allowing the user to do keyword and date
searches on the database and have the information displayed for them.
Everything looks and functions great, my only real dissatisfaction
with my application is the update time, which in my last test took
about 45-46 minutes for 9800 records.
When the update occurs, generally three steps are performed. The data
is downloaded as a single XML string, the XML string is parsed, and
then the new records are added to the database. I add all of the
records to a temporary table (one by one) from VB.NET using INSERT
INTO and then have a couple of queries that merge the temporary table
data into the final table.
I''ve have determined that the download is taking roughly 8% of the
process time, the parsing is taking about 4%, and the communication
with the database is taking 88% (45-46 minutes) of the time.
This 45 minutes almost exclusively consist of INSERT INTO statements
adding records one at a time in the following way...
...string sSQL (INSERT INTO blah blah blah) is constructed
right before this...
myCnxn = New OleDbConnection(sDBCnxnString)
cmUsrSQL = New OleDbCommand(sSQL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close()
...the next sSQL is constructed and the process is repeated...
Is there a way to add records in mass? If I''m using INSERT INTO, can
I add more than one record with a single SQL statement?
Just now as I type this I''m wondering how much time I''m wasting
opening and closing and opening and closing...
Any links to any articles on this topic would be greatly appreciated.
Thx.
10月31日下午2:53， ags5406< my.spam.5 ... @ gmail.comwrote：
On Oct 31, 2:53 pm, ags5406 <my.spam.5...@gmail.comwrote:
我创建了一个每天从安全下载数据的应用程序
网站并将数据存储在Access数据库中。然后有
不同的选项允许用户在数据库上进行关键字和日期
搜索，并为他们显示信息。
一切看起来和功能都很棒，我唯一真正的不满
与我的应用程序是更新时间，在我的上一次测试中花费了大约45-46分钟为
9800条记录。
更新发生时，通常会执行三个步骤。数据
作为单个XML字符串下载，解析XML字符串，然后将新记录添加到数据库中。我使用INSERT
INTO将所有
记录从VB.NET添加到临时表（逐个），然后有几个查询合并临时表
数据进入决赛桌。
我已经确定下载大约占了
的8％处理时间，解析约需4％，与数据库的通信时间为88％（45-46分钟）。
这45分钟几乎完全由INSERT INTO语句组成
按以下方式一次添加一条记录......
... string sSQL（ INSERT INTO blah blah blah）在此之前构建了
...
myCnxn =新的OleDbConnection（sDBCnxnString）
cmUsrSQL =新的OleDbCommand（sSQL，myCnxn）
myCnxn.Open（）
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close（）
...构建下一个sSQL并重复该过程...
I''ve created an application that downloads data daily from a secure
web site and stores that data in an Access database. Then there are
different options for allowing the user to do keyword and date
searches on the database and have the information displayed for them.
Everything looks and functions great, my only real dissatisfaction
with my application is the update time, which in my last test took
about 45-46 minutes for 9800 records.
When the update occurs, generally three steps are performed. The data
is downloaded as a single XML string, the XML string is parsed, and
then the new records are added to the database. I add all of the
records to a temporary table (one by one) from VB.NET using INSERT
INTO and then have a couple of queries that merge the temporary table
data into the final table.
I''ve have determined that the download is taking roughly 8% of the
process time, the parsing is taking about 4%, and the communication
with the database is taking 88% (45-46 minutes) of the time.
This 45 minutes almost exclusively consist of INSERT INTO statements
adding records one at a time in the following way...
...string sSQL (INSERT INTO blah blah blah) is constructed
right before this...
myCnxn = New OleDbConnection(sDBCnxnString)
cmUsrSQL = New OleDbCommand(sSQL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.ExecuteNonQuery
myCnxn.Close()
...the next sSQL is constructed and the process is repeated...
假设sql字符串基本相同，只是不同的
参数。 .. Psuedo代码：
使用连接作为oledbconnection = new oledbconnection
（连接字符串）
使用命令oledbcommand = new oledbcommand （插入
mytable（column1，column2，column3）值（@ value1，@ value2，
@ value3）"，connection）
myrecordstoinsert中的每条记录
command.parameters（" value1"）。value = value1
command.parameters（" value2"） .value = value2
command.parameters（" value3"）。value = value3
command.executenonquery（）
下一个
结束使用
无论如何......就像那样:)我希望那里没有C＃:)
-
Tom Shelton
Assumeing that the sql string is basically the same, just different
paramertes... Psuedo code:
using connection as oledbconnection = new oledbconnection
(connectionstring)
using command as oledbcommand = new oledbcommand ("insert into
mytable (column1, column2, column3) values (@value1, @value2,
@value3)", connection)
for each record in myrecordstoinsert
command.parameters("value1").value = value1
command.parameters("value2").value = value2
command.parameters("value3").value = value3
command.executenonquery()
next
end using
anyway... something like that :) i hope there''s no C# in there :)
--
Tom Shelton
" Trevor Benedict" < Tr ******** @ yahoo.com在消息新闻中写道：eH ************** @ TK2MSFTNGP04.phx.gbl ...
"Trevor Benedict" <Tr********@yahoo.comwrote in message news:eH**************@TK2MSFTNGP04.phx.gbl...
> 2注意事项。
1.不要打开和关闭连接。打开一次并根据需要执行数据插入
2.使用StringBuilder构建SQL。这可以帮助重复迭代。
" ags5406" < my ********** @ gmail.com在消息新闻中写道：11 ********************** @ d55g2000hsg.googlegr oups。 com ...
>2 things to note.
1. DO NOT OPEN AND CLOSE THE CONNECTION. Open it once and perform the data insert as required
2. Use a StringBuilder to build your SQL. This could help with repeated iterations.
"ags5406" <my**********@gmail.comwrote in message news:11**********************@d55g2000hsg.googlegr oups.com...
>我创建了一个应用程序，每天从安全的网站下载数据并将数据存储在Access数据库中。然后有不同的选项允许用户在数据库上进行关键字和日期搜索，并为他们显示信息。
一切看起来和功能都很棒，我唯一真正的不满
我的应用程序是更新时间，在我的上一次测试中，对于9800条记录大约需要45-46分钟。
>I''ve created an application that downloads data daily from a secure
web site and stores that data in an Access database. Then there are
different options for allowing the user to do keyword and date
searches on the database and have the information displayed for them.
Everything looks and functions great, my only real dissatisfaction
with my application is the update time, which in my last test took
about 45-46 minutes for 9800 records.
10年前486-66我曾经在本地高清上每秒获得15000行。
以上建议很好，重用连接。
使用交易进行批量生产，我记得1000是好的。曾经有过
（我认为）64k的命令限制。
此外，运行本地表要快得多。 IE创建一个本地数据库，触发
一些Create Table命令，然后在本地插入你的数据。最后，
执行Select Into将数据导入远程服务器。对于创建报告，这个
也可以反向运行。选择一些数据
到本地表中，按下它，然后将其转储到报告中。完成后删除
本地数据库。
你应该100％磁盘绑定..
10 years ago on a 486-66 I used to get 15000 rows per second on a local HD.
The above advice is good, reuse the connection.
Use transactions to do batches, I recall 1000 being good. There used to be
(I think) a 64k limit on commands.
Also, running a local table is much faster. IE Create a local db, fire off
some Create Table commands, then insert your data locally. Finally,
do a Select Into to firehouse the data into your remote server. This
also works very well in reverse, for creating reports. Select some data
into a local table, massage it, then dump it into a report. Delete the
local database when done.
You should be 100% disk bound on this..
这篇关于加快与Access的沟通的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
