0

I am using plain JDBC (because JPA was too slow for me) to implement a job which will run daily:

  1. Set Auto-Commit False
  2. TRUNCATE TARGET TBL (This is required to delete data from previous day)
  3. LOOP - SELECT FROM Servr1.DB.SRC in batches of 5000
  4. INSERT BATCH OF 5000 TO Servr2.DB.SRCTarget
  5. Continue Loop Or Goto #6
  6. Connection.commit

This works for me but has a couple of issues-

a. The load is still slow as it is taking 20mins to load a million. For 10M I am estimating that it will go upto 3hrs. Can this be reduced?

b. When I do a SELECT COUNT (from a SQL Client) while the job is running, both the SELECT and the INSERTs (in the job) get blocked and I have to reboot the server where the Java code is deployed to release the locks. Can this be prevented ?

Note: The isolation level set on the Target DB is - read committed

deepak
  • 339
  • 6
  • 16
  • Can you try a bulk export and import? This usually works via a file local to the server(s) – Peter Lawrey Nov 02 '18 at 19:04
  • 2
    I prefer the "hire an intern then go on vacation" technique. – markspace Nov 02 '18 at 19:06
  • Why use Java? Why not use your database’s bulk-loading feature? For example, in Postgres use the `COPY` command as described in this [guide to populating a database](https://www.postgresql.org/docs/current/static/populate.html) and in this [Stack Overflow question](https://stackoverflow.com/q/2987433/642706). The speed will be *enormously* improved. – Basil Bourque Nov 02 '18 at 19:15
  • 1
    Looping a result set is row-by-row which always means slow-by-slow. Making good use of the RDBMS bulk loading utilities, or if those are not an option, JDBCs batch operations, could make this orders of magnitude faster. On the other hand, we haven't seen the code nor do we know if network latency is an issue, so possible answers are close to hand waving. – Mick Mnemonic Nov 02 '18 at 19:27
  • Are you using [rewriteBatchedStatements=true](https://stackoverflow.com/a/26313288/2144390) ...? – Gord Thompson Nov 02 '18 at 20:45

0 Answers0