Speeding Up Communication With Access

  • Thread starter Thread starter ags5406
  • Start date Start date
A

ags5406

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.
 
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
 
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...
Click to expand...


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 :)
 
Trevor Benedict said:
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.
Click to expand...


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..
 
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 connectionstring 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
 
Tom,

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

Cor
 
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
 
ags5406 said:
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.
Click to expand...

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
 
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(sSQL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.ExecuteNonQuery
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?
 
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(sSQL, myCnxn)
myCnxn.Open()
iDummy = cmUsrSQL.ExecuteNonQuery
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?
Click to expand...

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?
 
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?
Click to expand...

Actually, yes, that does make sense. When I started the project I was
keeping the DB on my machine. I've since moved it to a remote
server. I didn't quite understand what he was saying because my brain
had forgotten I was storing it remotely. Thanks.
 
Back
Top