Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Advice on Checking the data with database

 
PRavi kumar
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HI,

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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ajith Prasad
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Pat Farrell
Rancher
Posts: 4678
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I didn't know what 'lakh' meant, so I looked it up on wikipedia...


"One lakh is equal to a hundred thousand,..."
 
imaya Munusamy
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic