• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Tim Cooke
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Frank Carver
  • Henry Wong
  • Ron McLeod
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Himai Minh

Advice on Checking the data with database

 
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 4686
7
Mac OS X VI Editor Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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,..."
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Skool. Stay in. Smartness. Tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic