Heavy Data-Import to Access

[Innuendo]

Hello,

I'm not a specialist with Access - so I hope that you can give me some
input.

I've to import xml data (about 15 different data fields) into an
access-database each night. As scripting language I've to use ASP.

Do you think there will be a problem with about 20.0000 to 30.000
data-records, which have to be updated (about 99 % of the data-records
have to be updated) each night? Is Access strong enough for this heavy
traffic?

In my opinion that it is too heavy traffic - because I have to look at
every data-record (xml) and have to look up, if there is already a
record in the access-db. And this for more than 20.000 times!

What are your thoughts? What will be a good start? Some hints?

Yours,
Andreas Greiner

[DFS]

"Innuendo" <a.*******@gmx.at> wrote in message
news:77**************************@posting.google.c om...

I've to import xml data (about 15 different data fields) into an
access-database each night. As scripting language I've to use ASP.

Do you think there will be a problem with about 20.0000 to 30.000
data-records, which have to be updated (about 99 % of the data-records
have to be updated) each night? Is Access strong enough for this heavy
traffic?
Access (really the Jet db engine) is plenty strong enough.
In my opinion that it is too heavy traffic - because I have to look at
every data-record (xml) and have to look up, if there is already a
record in the access-db. And this for more than 20.000 times!

Sounds like you'll spend about half your time parsing XML data and the other
half looking it up in the Access db.

Describe your problem in more detail and maybe I (or cdma) can offer
suggestions. It might be the case that you can write your XML data to work
tables and execute bulk lookups/inserts against the database.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For feasibility, it really depends on the computer that is running the
application. Does it have enough memory (RAM and disk space)? Is the
speed of the processor adequate? Are other programs sapping the
processing power/memory of the Access application.

You can import the data into a temporary table (see the Access Help
article "ImportXML Method"), then run elimination queries against that
data comparing the new data to existing data and eliminating the new
data that already exists in the db. E.g.:

DELETE DISTINCTROW NewTable.*
FROM NewTable INNER JOIN OldTable
ON NewTable.ID = OldTable.ID

IOW, you'd join the two tables using the fields that uniquely identify
each record, which deletes the new data in the NewTable that already
exists in the OldTable.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQA7ykYechKqOuFEgEQJYBQCggoB2kMwzwEohHAQGt2b0S6 QFPPEAmwQd
ZQV5ugmd929E2WvTtfMjm6A+
=AJtx
-----END PGP SIGNATURE-----
Innuendo wrote:

Hello,

I'm not a specialist with Access - so I hope that you can give me some
input.

I've to import xml data (about 15 different data fields) into an
access-database each night. As scripting language I've to use ASP.

Do you think there will be a problem with about 20.0000 to 30.000
data-records, which have to be updated (about 99 % of the data-records
have to be updated) each night? Is Access strong enough for this heavy
traffic?

In my opinion that it is too heavy traffic - because I have to look at
every data-record (xml) and have to look up, if there is already a
record in the access-db. And this for more than 20.000 times!

What are your thoughts? What will be a good start? Some hints?

Yours,
Andreas Greiner

[Andreas Greiner]

Hi,

thank you for your answer.

Yes, you are right. The XML-Data will contain data like firstname,
sirname, adress, birthday, email, ... - this data has to be "copied" to
the access-database.

At this point I don't know, if it is a new user, or an existing user. If
it is a new user, then I'have to make an insert - if it's an existing
user, I've to control the data, and if something has changed of the
xml-data, then I've to make an update.

Your idea with work tables is good. I think this will be an advantage
for me.

What do you exactly mean with "execute bulk lookups/inserts"?
What possibilitis do I have with access and bulk lookups and inserts?

Thank you for your nice help (and excuse my "Austrian" English)

Yours,
Andreas


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[DFS]

"Andreas Greiner" <a.*******@gmx.at> wrote in message
news:40***********************@news.frii.net...

Hi,

thank you for your answer.

Yes, you are right. The XML-Data will contain data like firstname,
sirname, adress, birthday, email, ... - this data has to be "copied" to
the access-database.

At this point I don't know, if it is a new user, or an existing user. If
it is a new user, then I'have to make an insert - if it's an existing
user, I've to control the data, and if something has changed of the
xml-data, then I've to make an update.

Your idea with work tables is good. I think this will be an advantage
for me.

What do you exactly mean with "execute bulk lookups/inserts"?
What possibilitis do I have with access and bulk lookups and inserts?
Andreas,

Instead of doing 20,000 lookups, insert all the records into a work table
and execute a few queries between the work table and the master:

1) execute updates to existing users

examples:

UPDATE Master INNER JOIN Work
ON Master.UserID = Work.UserID
SET Master.Address = Work.Address
WHERE Work.Address <> Master.Address
OR (Master.Address IS NULL AND Work.Address IS NOT NULL);

UPDATE Master INNER JOIN Work
ON Master.UserID = Work.UserID
SET Master.Email = Work.Email
WHERE Work.Email <> Master.Email
OR (Master.Email IS NULL AND Work.Email IS NOT NULL);

2) insert new users.

INSERT INTO Master (UserID, FirstName, LastName, Birthdate, Address...)
SELECT UserID, FirstName, LastName, Birthdate, Address...
FROM Work

(the above will try to insert all records, but some records will fail on the
primary key. Don't know if you'll get an Error using ASP or not). You
could also try:

INSERT INTO Master (UserID, FirstName, LastName, Birthdate, Address...)
SELECT UserID, FirstName, LastName, Birthdate, Address...
FROM Work
WHERE UserID NOT IN (SELECT UserID FROM Master);
3) empty the work table

DELETE FROM Work;
But, is this a one-time thing? You probably shouldn't have to be parsing
20,000 user records very often.

Thank you for your nice help (and excuse my "Austrian" English)

Yours,
Andreas


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Andreas Greiner]

Hi,

thank you very much for your hints. I think there is a solution with
your code examples.

One thing more: YES, I've to do this parsind EVERY night. So that will
be very heavy for access and the tables!

Andreas
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[DFS]

"Andreas Greiner" <a.*******@gmx.at> wrote in message
news:40***********************@news.frii.net...

Hi,

thank you very much for your hints. I think there is a solution with
your code examples.
You're welcome.

One thing more: YES, I've to do this parsind EVERY night.
I haven't seen your db or system, but you probably shouldn't have to go to
that much trouble. Can you not include a CreateDate and/or LastUpdateDate
field in your db or XML somewhere that will let you know the new and updated
users?

So that will be very heavy for access and the tables!
Again, it won't be a problem for the Access/Jet db engine, but it could be
time consuming depending on how you implement your solution.
Andreas
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!