Fast Update 10,000 Access records

  • Thread starter Thread starter nycboy
  • Start date Start date
N

nycboy

Hi All,

I am new to ADO and I want to know in which way can I
update Access dataset (about 10,000 records a time, and
the new values are coming from an Excel .csv file) with
best efficentcy? Which way is better, SQL or DataAdapter's
Update()?

Thanks for any suggestions and comments,

David




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
If speed is the issue, once SQL Statement is going to be much faster. The
adapter will walk through the rows on at a time and for each one iwth a
changed rowstate, it will fire its respective command.

However, you can still use the ExecuteNonQuery method for instance to send a
sql statement that updates a whole set of stuff. To some degree the
feasability will lie in what you are trying to update but by and large, and
type of large scale anything is probably better done on the back end if
possible.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Thanks for your prompt reply.

If the 10,000 recoreds have different new values,
am I going to send 10,000 SQL commands? Is it
possible doing it with table operation kind of
thing, if available?


David


--- Original Message ---

Re: Fast Update 10,000 Access records
From: William Ryan eMVP
Date Posted: 6/4/2004 11:34:00 AM



If speed is the issue, once SQL Statement is going to be much faster.
The
adapter will walk through the rows on at a time and for each one iwth a
changed rowstate, it will fire its respective command.

However, you can still use the ExecuteNonQuery method for instance to
send a
sql statement that updates a whole set of stuff. To some degree the
feasability will lie in what you are trying to update but by and large,
and
type of large scale anything is probably better done on the back end if
possible.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
Hi All,

I am new to ADO and I want to know in which way can I
update Access dataset (about 10,000 records a time, and
the new values are coming from an Excel .csv file) with
best efficentcy? Which way is better, SQL or DataAdapter's
Update()?

Thanks for any suggestions and comments,

David
Click to expand...


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Thanks for your prompt reply.

If the 10,000 recoreds have different new values,
am I going to send 10,000 SQL commands?
Click to expand...
Yep. For each row that has a rowstate of Modified, Deleted or Added, you're
going to send a command to the db provided you have a respective command
defined in the adapter (obviously you can't send what you don't have so if
no update command was specified you'd throw an exception)

Is it
possible doing it with table operation kind of
thing, if available?
Click to expand...

With a DataAdapter this is pretty much it. But you can use ExecuteNonQuery
for instance to send back one statement like "Update Table Set SomeFiled to
SomeValue" wiithout a wehere clause and I'd do it to every record. You can
use similar set based updates and call them directly although it may not be
practical in many cases.
David


--- Original Message ---

Re: Fast Update 10,000 Access records
From: William Ryan eMVP
Date Posted: 6/4/2004 11:34:00 AM



If speed is the issue, once SQL Statement is going to be much faster.
The
adapter will walk through the rows on at a time and for each one iwth a
changed rowstate, it will fire its respective command.

However, you can still use the ExecuteNonQuery method for instance to
send a
sql statement that updates a whole set of stuff. To some degree the
feasability will lie in what you are trying to update but by and large,
and
type of large scale anything is probably better done on the back end if
possible.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Click to expand...



--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Thanks. But I have a further question on whether
an easy way available for me to match the records.
Say, the original table has recoreds

key1, value1
key2, value2
...
key(n), Value(n)

and a set of new values from a .csv file
has records

key(n1), value(n1)
key(n2), value(n2)
...
key(nm), Value(nm)

and they may not be in the same sorting order.
Do I have to programmatically look up corresponding
records in the original table for each record
in .csv file, or there is a better way merging them?

Thanks.




--- Original Message ---
Re: Fast Update 10,000 Access records

Thanks for your prompt reply.

If the 10,000 recoreds have different new values,
am I going to send 10,000 SQL commands?
Click to expand...
Yep. For each row that has a rowstate of Modified, Deleted or Added,
you're
going to send a command to the db provided you have a respective command
defined in the adapter (obviously you can't send what you don't have so
if
no update command was specified you'd throw an exception)

Is it
possible doing it with table operation kind of
thing, if available?
Click to expand...

With a DataAdapter this is pretty much it. But you can use
ExecuteNonQuery
for instance to send back one statement like "Update Table Set SomeFiled
to
SomeValue" wiithout a wehere clause and I'd do it to every record. You
can
use similar set based updates and call them directly although it may not
be
practical in many cases.
David


--- Original Message ---

Re: Fast Update 10,000 Access records
From: William Ryan eMVP
Date Posted: 6/4/2004 11:34:00 AM



If speed is the issue, once SQL Statement is going to be much faster.
The
adapter will walk through the rows on at a time and for each one iwth a
changed rowstate, it will fire its respective command.

However, you can still use the ExecuteNonQuery method for instance to
send a
sql statement that updates a whole set of stuff. To some degree the
feasability will lie in what you are trying to update but by and large,
and
type of large scale anything is probably better done on the back end if
possible.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
<nycboy> wrote in message news:[email protected]...


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Click to expand...



--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuz



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
On Fri, 04 Jun 2004 11:57:46 -0700, nycboy <nycboy> wrote:

¤ Thanks. But I have a further question on whether
¤ an easy way available for me to match the records.
¤ Say, the original table has recoreds
¤
¤ key1, value1
¤ key2, value2
¤ ...
¤ key(n), Value(n)
¤
¤ and a set of new values from a .csv file
¤ has records
¤
¤ key(n1), value(n1)
¤ key(n2), value(n2)
¤ ...
¤ key(nm), Value(nm)
¤
¤ and they may not be in the same sorting order.
¤ Do I have to programmatically look up corresponding
¤ records in the original table for each record
¤ in .csv file, or there is a better way merging them?

Are you updating existing rows in the Access table, inserting new rows from the .csv file or both?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Yes, I am updating an existing Access table with
new values (a key column and a value column)
coming from a .csv file. So for each line of the .csv
file, I got to locate the key in the table, and update
the corresponding value column. The total number of
rows of the table is around 10k to 30k.

Thanks.

---- Original Message ----
Re: Fast Update 10,000 Access records
From: Paul Clement
Date Posted: 6/7/2004 1:19:00 PM



On Fri, 04 Jun 2004 11:57:46 -0700, nycboy <nycboy> wrote:

? Thanks. But I have a further question on whether
? an easy way available for me to match the records.
? Say, the original table has recoreds
?
? key1, value1
? key2, value2
? ...
? key(n), Value(n)
?
? and a set of new values from a .csv file
? has records
?
? key(n1), value(n1)
? key(n2), value(n2)
? ...
? key(nm), Value(nm)
?
? and they may not be in the same sorting order.
? Do I have to programmatically look up corresponding
? records in the original table for each record
? in .csv file, or there is a better way merging them?

Are you updating existing rows in the Access table, inserting new rows
from the .csv file or both?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
On Mon, 07 Jun 2004 13:30:55 -0700, nycboy <nycboy> wrote:

¤ Yes, I am updating an existing Access table with
¤ new values (a key column and a value column)
¤ coming from a .csv file. So for each line of the .csv
¤ file, I got to locate the key in the table, and update
¤ the corresponding value column. The total number of
¤ rows of the table is around 10k to 30k.
¤

Unfortunately I don't believe you can join an Access table to a Text file natively so you won't be
able to do this with SQL directly. I'm afraid you will have to query the Text file and go through
row by row looking up the corresponding rows in the Access table and then performing the update.

Another method would be to import the Text file as a native Access table and then join this table
with the Access table that needs to be updated. You can perform the import with a single SQL
statement and then perform the join and update in another SQL statement.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Import and then join makes sense to me. Thanks, Paul!

-david

---- Original Message ----

Re: Fast Update 10,000 Access records
From: nycboy

Yes, I am updating an existing Access table with
new values (a key column and a value column)
coming from a .csv file. So for each line of the .csv
file, I got to locate the key in the table, and update
the corresponding value column. The total number of
rows of the table is around 10k to 30k.

Thanks.

---- Original Message ----
Re: Fast Update 10,000 Access records
From: Paul Clement
Date Posted: 6/7/2004 1:19:00 PM



On Fri, 04 Jun 2004 11:57:46 -0700, nycboy <nycboy> wrote:

? Thanks. But I have a further question on whether
? an easy way available for me to match the records.
? Say, the original table has recoreds
?
? key1, value1
? key2, value2
? ...
? key(n), Value(n)
?
? and a set of new values from a .csv file
? has records
?
? key(n1), value(n1)
? key(n2), value(n2)
? ...
? key(nm), Value(nm)
?
? and they may not be in the same sorting order.
? Do I have to programmatically look up corresponding
? records in the original table for each record
? in .csv file, or there is a better way merging them?

Are you updating existing rows in the Access table, inserting new rows
from the .csv file or both?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Back
Top