Transfer a lot of records from Database

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

  1. 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...
    , Jun 17, 2006
    #1
    1. Advertising

  2. Danno Guest

    wrote:
    > 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...


    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. Advertising

  3. wrote:
    > 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?
    >

    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).

    > Any other workarounds are better?
    >

    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@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Jun 17, 2006
    #3
  4. wrote:
    > 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...
    >


    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. Chris Smith Guest

    Jeffrey H. Coffield <> wrote:
    > 2. Use a compiled language.
    >


    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 - Lead Software Developer / Technical Trainer
    MindIQ Corporation
    Chris Smith, Jun 18, 2006
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    776
    Luke Airig
    Dec 31, 2003
  2. Replies:
    0
    Views:
    364
  3. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    455
    Ray at
    May 10, 2004
  4. Replies:
    3
    Views:
    645
    Anthony Jones
    Nov 2, 2006
  5. Nikos
    Replies:
    21
    Views:
    237
    Nikos
    Apr 26, 2005
Loading...

Share This Page