• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Migration in same DB with best performance

 
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to create a app, where I have to transfer a million record from one table to another on a click of a button, in a same Data Base.What will be the best approach, for getting best performance?
 
lowercase baba
Posts: 13089
67
Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
umm...My first thought (and I am by NO means an expert) is WHY?

On the surface, this sounds like a bad idea. What purpose does copying the records from one table to another in the same database serve?
 
Kaustubh G Sharma
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

fred rosenberger wrote:umm...My first thought (and I am by NO means an expert) is WHY?

On the surface, this sounds like a bad idea. What purpose does copying the records from one table to another in the same database serve?



it's not a task fred, this is a curious query of an interviewer, I don't have any idea, what he's is expecting from it. May be wanted to test, how good I am in seeking solution.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh, another interview question? In this case, I believe a reasonably good answer would be:

The correct approach is to run one insert into ... select statement. The wrong approach is anything else, particularly reading the source table using ResultSet and inserting individual rows back into the target table using PreparedStatement (or even just plain Statement), as in this case all the data travel needlessly back and forth between the database and the application.



You might also add that this operation is going to take some time (during which the application will be unresponsive), consume significant database resources, and might be really impossible to scale.
 
Ranch Hand
Posts: 50
5
Oracle Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

fred rosenberger wrote:umm...My first thought (and I am by NO means an expert) is WHY?



There are thousands of reasons. A very simple example would be copying records from TRANSACTIONS to TRANSACTIONS_ARCHIVE tables (if you don't use partitioning). Strictly speaking, you'd probably move the records, not copy them. But for the sake of this question, that's the same.

Martin Vajsar wrote:The correct approach is to run one insert into ... select statement. The wrong approach is anything else



I don't entirely agree with this. For millions of rows, using INSERT .. SELECT can prove to be a performance nightmare in databases with lots of concurrency. The reason for this is the fact that large parts of the source and target tables need to be copied into UNDO / REDO log space in order to ensure transactional integrity across this single statement. This can pretty much block the database engine in some circumstances.

A "good" way to tackle this problem would be to use PL/SQL (or T-SQL or whatever) and loop over smaller data subsets, inserting and committing them immediately, keeping UNDO space small.

Another "good" way could be to copy the records into a temporary table without indexes or constraints, releasing the source table read locks early, and moving the temporary table parts to the target table asynchronously in a separate transaction.

Martin Vajsar wrote:particularly reading the source table using ResultSet and inserting individual rows back into the target table



That, of course, is correct.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Lukas Eder wrote:

Martin Vajsar wrote:The correct approach is to run one insert into ... select statement. The wrong approach is anything else


I don't entirely agree with this. For millions of rows, using INSERT .. SELECT can prove to be a performance nightmare in databases with lots of concurrency.


Well, there are databases where the single SQL statement is certainly the fastest possible way (assuming you size the UNDO for the job). Unfortunately I often forget there are also other databases beyond the one I use - thanks for pointing that out.

If this was an interview question, there was probably not enough detail to provide one correct answer. Perhaps just "do it in the database" is what was expected.
 
Lukas Eder
Ranch Hand
Posts: 50
5
Oracle Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:If this was an interview question, there was probably not enough detail to provide one correct answer. Perhaps just "do it in the database" is what was expected.



True.
 
I am going to test your electrical conductivity with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic