0

I'm looking at a spring boot application that is used to copy data from temp to permanent table based on last updated date. It copies only if last updated date is greater than desired date, so not all records are copied over. Currently the table has around 300K+ records and the process with spring JPA is taking over 2 hours (for all of them) and is not at all feasible. The goal is to bring it down to under 15 mins maximum. I'm trying to see how much difference using JDBCtemplate would bring in. Would a pl/sql script be a better option? Also wanted to see if there are better options out there. Appreciate your time.

Using oracle database at the moment but postgresql migration is on the cards.

Thanks!

Improve this question
1
  • 1
    can you add code for method doing this copy ? Commented May 4, 2020 at 3:43

1 Answer 1

Reset to default
2

You can do this operation with a straight SQL query (which will work on Oracle or PostgreSQL). Assuming your temp_table has the same columns as the permanent table, the last updated date column is called last_updated and you wanted to copy all records updated since 2020-05-03 you could write a query like:

INSERT INTO perm_table
SELECT *
FROM temp_table
WHERE last_updated > TO_DATE('2020-05-03', 'YYYY-MM-DD')

In your app you would pass '2020-05-03' via a placeholder either directly or via JdbcTemplate.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

sri, NEVER perform row-by-row processing when you don't have to. ALWAYS use SQL to perform bulk operations like this. This is literally what databases were designed and built to do, and trying to replicate the process in application code is an exercise in futility. If you can do what Nick is suggesting, your performance will likely improve by several orders of magnitude.
@pmdba I couldn't have put it better myself!
@Nick/pmdba- Thankyou both for your inputs, I figured using direct sql would be much faster. I had to optimize this existing program (and thankfully it is in development phase) , so here i am. Will keep you posted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.