Transfer a lot of records from Database

Discussion in 'Java' started by howachen, Jun 17, 2006.

  1. howachen

    howachen Guest

    Hi,

    I want to do a query to a remote DB (MySQL) which is expect to return a
    lot of records (around 1M of rows). The job is expected to run as a
    cron job during mid-night.

    What should I need to take care of when designing a application like
    this? Any other workarounds are better?

    Thanks...
     
    howachen, Jun 17, 2006
    #1
    1. Advertisements

  2. howachen

    Danno Guest

    I personally would either have a solution that would stream those
    results over the line, or I would use something like JMS to guarantee
    that transfer of data.
     
    Danno, Jun 17, 2006
    #2
    1. Advertisements

  3. Consider:
    - the data volume (in bytes)
    - transfer speed end-to-end between the remote server and your
    application. Measure min/avg/max rate at the time you expect the cron
    job to run.
    - how long the transfer will take.
    - failure frequency
    - recovery methods after a failed transfer
    - duplication detection and avoidance after a failure
    - data sensitivity
    - security of the network: if its not private or a VPN it's INSECURE.
    - what privacy policy applies to the data
    - how you'll meet the privacy requirement (encryption, etc).
    If the connection is unreliable and/or insecure or if the retrieved rows
    will be handled in bulk you may want to write the rows to a file on the
    remote server and then use scp to retrieve the file. This provides easy
    security (scp transfers are encrypted, the DB isn't exposed, you can set
    ssh up to securely exchange keys instead of passwords) and recovery
    after a failure is likely to be easier to implement.
     
    Martin Gregorie, Jun 17, 2006
    #3
  4. 1. Run the job on the server and only move the result (usually much
    smaller).
    2. Use a compiled language.
     
    Jeffrey H. Coffield, Jun 17, 2006
    #4
  5. howachen

    Chris Smith Guest

    Eh? First of all, this message was posted to comp.lang.java.programmer,
    which suggests that the plan was already to use a compiled language.
    That being said, though, it very well may not make a whit of difference
    whether it's an interpreted or a compiled language that is waiting on
    MySQL to execute a complex query. Everything depends on what's going on
    here, and no one really knows.
     
    Chris Smith, Jun 18, 2006
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.