File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Performance and the fly likes Advice on Checking the data with database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Advice on Checking the data with database" Watch "Advice on Checking the data with database" New topic

Advice on Checking the data with database

PRavi kumar
Ranch Hand

Joined: May 13, 2007
Posts: 69

I have 5 lacks records in csv file. the records needs to be uploaded to databse every day. Each record have a key

now before updating these records. I need to check with data base the key is already exist in master table.

whcih is the best way to compare the Key with master table

1) Hit the database every time. in thins case it's 5 lacks data base calls.
2) store the 5 lack records in collection object and compare.

now which is the best soluation in performance wise.

I think that holding the 5 lacks records in collection may be the performance issue. The JVM can able to hold the 5 lack Objects .

Kindly advice mw which is the best soluation to handle.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
some databases have the ability to ignore duplicates. this would allow you to simply insert everything adn let the database do the work of deciding if it is already there. another option would be to insert all the csv records into a table, and then use sql to only insert the records in the real table that don't already exist there. - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Ajith Prasad
Ranch Hand

Joined: Aug 22, 2006
Posts: 66
This can be achieved in two ways,

1) Writing the batch process, read it from CSV file , call Batch process and load in to the database

2) Stored procedures avoid network traffic, but you should take how do you pass data from jdbc calls to storedprocedures
Rajah Nagur
Ranch Hand

Joined: Nov 06, 2002
Posts: 239
1. Hit the database and retrieve ONLY the keys for the 5 lakh records.
2. Match & Filter the CSV file keys with the retrieved keys
3. Upload the CSV (with unique keys) into the DB. Use Batch process.

You can't wake a person who is <b><i>pretending</i></b> to be asleep.<br />Like what <b>"it"</b> does not like - <i> Gurdjieff </i>
Pat Farrell

Joined: Aug 11, 2007
Posts: 4659

Originally posted by Rajah Nagur:
Use Batch process.

Rajah has hit on a fundamental performance fix. If something is slow, do something different. rethink the problem. Have the computer do less work, and it will get faster.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
I didn't know what 'lakh' meant, so I looked it up on wikipedia...

"One lakh is equal to a hundred thousand,..."
imaya Munusamy

Joined: May 19, 2008
Posts: 20
1) Upload the whole cvs file into a temporary table using batch upload
2) select all the records which is not there in master table by join the temp table with master and insert into master table
I agree. Here's the link:
subject: Advice on Checking the data with database
It's not a secret anymore!