Transfer a lot of records from Database

[howachen]

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

 

[Danno]

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.

 

[Martin Gregorie]

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.

 

[Jeffrey H. Coffield]

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.

 

[Chris Smith]

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.