473,597 Members | 2,138 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Speeding Up Communication With Access

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(sS QL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.Execut eNonQuery
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.

Oct 31 '07 #1
10 1302
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**********@g mail.comwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.com.. .
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(sS QL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.Execut eNonQuery
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.

Oct 31 '07 #2
On Oct 31, 2:53 pm, ags5406 <my.spam.5...@g mail.comwrote:
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(sS QL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.Execut eNonQuery
myCnxn.Close()

...the next sSQL is constructed and the process is repeated...

Assumeing that the sql string is basically the same, just different
paramertes... Psuedo code:

using connection as oledbconnection = new oledbconnection
(connectionstri ng)
using command as oledbcommand = new oledbcommand ("insert into
mytable (column1, column2, column3) values (@value1, @value2,
@value3)", connection)

for each record in myrecordstoinse rt
command.paramet ers("value1").v alue = value1
command.paramet ers("value2").v alue = value2
command.paramet ers("value3").v alue = value3

command.execute nonquery()
next
end using

anyway... something like that :) i hope there's no C# in there :)

--
Tom Shelton

Oct 31 '07 #3

"Trevor Benedict" <Tr********@yah oo.comwrote in message news:eH******** ******@TK2MSFTN GP04.phx.gbl...
>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**********@g mail.comwrote in message news:11******** **************@ d55g2000hsg.goo glegroups.com.. .
>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 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..
Nov 1 '07 #4
Tom,

I am not sure if this is really speeding up the actions, probably only the
writting of the code. AFAIK is the "using" a kind of generic code, which has
more in it then if you write it yourself. In this case the dispose of the
connection, which removes the connectionstrin g first from the connection
before clossing (for the rest is the dispose in this case only overloaded
with the close, this for in version 1.1 for the latter I have never
investigated this).

Cor

Nov 1 '07 #5
Tom,

I took your intention wrong, the loop is of course the right way.

Cor
Nov 1 '07 #6
ags,

The Jet database is in fact not build for what you are doing. It was great
to use because it was free and the other free database from Microsoft MSDE
was terrible to use.

However, now there is SQL Express, you can use that, it really speeds up
your process.

And then take the sample from Tom. What you can do as well is creating a
sproc (Stored Procedure), it has no timebenefit in seldom done processes.
However the SQL server has build in a kind of caching were it stores the
latest used sprocs in a precompiled version (this is not done for all sprocs
before you understand this wrong).

Cor

Nov 1 '07 #7
"ags5406" <my**********@g mail.comschrieb
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.
If you need it really fast, use VB6+DAO. Use the AddNew/Update for Recordset
objects and you will be /multiple/ times faster than using INSERT for each
row. Unfortunatelly, in VB.Net we are forced to use ADO.net which is really
slow and a step back in this area. I wouldn't recommend DAO Interop due to
the COM Interop and programming overhead.
Armin

Nov 1 '07 #8
Thanks everyone for the suggestions. I am moving forward with some
success.

So I originally had this code...

myCnxn = New OleDbConnection (sDBCnxnString)
cmUsrSQL = New OleDbCommand(sS QL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.Execut eNonQuery
myCnxn.Close()

.... in Sub execSQLNonQuery (sSQL as string)

The purpose was to build a string anywhere in the code, for whatever
purpose, and send the string to this one subroutine to execute to DB
part. I kind of kept it the same but with the key difference being
that I moved the open and close out of this subroutine. Now
the .Open() occurs when in Main() and the .Close() occurs when the
program exits. The connection to the DB is always open. I ran the
same test from yesterday (almost the same number of records today) and
saw a huge reduction in the update time - between 16 and 17 minutes
savings (~37%).

So that's good. But I still want to speed it up even more.

Now the downloading from the secure site is taking about 12% of the
time, parsing is taking 6%, and updating the local db is taking 82% of
the time. My biggest opportunity for optimization still appears to be
in the updating of the local database.

I still need to figure out how to add multiple records at a time
rather using INSERT INTO over and over for each record.

Robert - you may have been leading me in the right direction. You
mention getting 15000 rows per second back in the day? Just FYI my
target table has 20 fields, 14 of which are text (of varying max
lengths) and 6 are datetime. I think I understand sending the create
table commands. But then wouldn't I just still be adding rows one at
a time to my new table? Or can I create the table with the data all
at once?

Nov 1 '07 #9
On Nov 1, 9:18 am, ags5406 <my.spam.5...@g mail.comwrote:
Thanks everyone for the suggestions. I am moving forward with some
success.

So I originally had this code...

myCnxn = New OleDbConnection (sDBCnxnString)
cmUsrSQL = New OleDbCommand(sS QL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.Execut eNonQuery
myCnxn.Close()

... in Sub execSQLNonQuery (sSQL as string)

The purpose was to build a string anywhere in the code, for whatever
purpose, and send the string to this one subroutine to execute to DB
part. I kind of kept it the same but with the key difference being
that I moved the open and close out of this subroutine. Now
the .Open() occurs when in Main() and the .Close() occurs when the
program exits. The connection to the DB is always open. I ran the
same test from yesterday (almost the same number of records today) and
saw a huge reduction in the update time - between 16 and 17 minutes
savings (~37%).

So that's good. But I still want to speed it up even more.

Now the downloading from the secure site is taking about 12% of the
time, parsing is taking 6%, and updating the local db is taking 82% of
the time. My biggest opportunity for optimization still appears to be
in the updating of the local database.

I still need to figure out how to add multiple records at a time
rather using INSERT INTO over and over for each record.

Robert - you may have been leading me in the right direction. You
mention getting 15000 rows per second back in the day? Just FYI my
target table has 20 fields, 14 of which are text (of varying max
lengths) and 6 are datetime. I think I understand sending the create
table commands. But then wouldn't I just still be adding rows one at
a time to my new table? Or can I create the table with the data all
at once?
I think what robert is suggesting, is to create a mirror of your table
on the local machine. Then, you do your single inserts into that
table. Once you've inserted all of your data to the local database -
you then do a select into form the local table to the real database
table....

Does that make sense?

--
Tom Shelton

Nov 1 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1377
by: John D | last post by:
We have a dynamic SP that dependant on a user name will run a selected tailored to them. One of the criteria is the number of rows retrieved, which we include using 'top @varNoOfRows' in the dynamically created select . As long as the number of rows is less than 130 the SP runs in less than a second, but if you increase that value to over 150 the SP times out. It is being run from ASP in this way: DBCon.execute(SQLQuery)
3
1959
by: Bryan Christopher | last post by:
Hello All! I have a rather abstract question for some genius out there to answer. I want to integrate communication tracking, for customer relations, into an existing Access DB. What I was going for is this... tblCommunications trxCommunicationThreads ----------------- ----------------------- CommunicationID (PK) CommID1 (FK1)(PK) ContactID (FK from contact table) CommID2...
2
1618
by: Wayne | last post by:
I am running a complex query using about 25 criteria that are entered on a query form. If any individual criteria isn't required its field is left as "*" on the form. When I run the query the first time the results take about 3 or 4 seconds to appear on a "Query Results" form. Subsequent queries using either the same or different criteria give a result in less than 1 second. The "Query Results" form has a button to open an equally...
8
3365
by: Stephen K. Young | last post by:
If you have not seen it, this recent Microsoft Knowledge Base article is worth reading: http://support.microsoft.com/kb/889588 "How to optimize Office Access and Jet database engine network performance
13
4967
by: Krzysztof Bartosiewicz | last post by:
Hi! I have a problem with an access database. The database which I designed and made using MS access works perfectly OK on my computer however it is not working at all on computers for which it was done :) When the client tries to open first form the access shows information about error connected to the communication between Access and OLE or Active-X control. The problem is that I cannot go to the client's computer and test it because
2
5122
by: Leonardo D'Ippolito | last post by:
Hi! I have two .NET win apps that need to communicate on a TCP/IP network. 'App A' must ask 'app B' if it's allowed to do some task, and 'app B' must authorize or prohibit it. How can I do this kind of communication in a secure way (protected from sniffing)? It would be a very simple protocol. Question, and two possible answers 'yes' or 'no'.
7
2048
by: Michael Butscher | last post by:
Hi, this is not really Python-specific but I need it for Python. I'm wanting a method for interprocess communication which is OS- independent (sockets would be the normal way to go), but which works if multiple users use the machine at the same time so that one user has no access to the communication of programs of another user. Normally any user could connect to an open socket on a machine
1
1813
by: hardikpatelmca | last post by:
Hi everybody, I want to create web application using C# and live communication server. User can set his presence information, view other presence information and send instant messages. How can I start developing in C# web application using live communication server? I doesn't want to use Communicator Web access. If you have any tutorial, then please provide to me. It is very appreciate if you provide some code example. I am beginner...
5
3166
by: AeonOfTime | last post by:
Let's assume a web application (in this case a browser-based game) with a custom HTTP server built on PHP, and a client also built on PHP. The client uses the server to access and change data. Even if the client server communication is not directly visible to the user (who logs into the client), the fact that the server is publicly accessible (a port sniffer would be enough to find it) means the communication has to be secured. How...
0
7965
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8271
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8031
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5426
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3881
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2399
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1493
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1231
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.