Design dilemma:Mysql stored procedure or java business objects?

[chiyo matsushita]

Hi,

I am looking for inputs on a design dilemma.

I have a requirement were content stored in TableA has to be modified and stored in TableB. This is a background process and transparent to the user. it will run at off peak hours. TableA has close to 1 million records.
The rules for modifying content have medium complexity. My options are:
1. Read batches of data from Table A into Java objects, program the rules in the business objects, modify the data, persist it to TableB.
2. Write a mysql procedure that does the same.

Given the volume of the content and the lack of user interaction, I am inclined to go with the second option. However I am also a bit hesitant as the approach introduces stored procedures in the mix.

Any advise on why I should pick one over the other?

Thanks in advance for the feedback.

--Chiyo.

[Jimmy Clark]

Do you think that you can implement all of the business rules using MySQL's version of SQL?

[chiyo matsushita]

Yes. The rules themselves are relatively simple so should be relatively easy to implement. It is the volume of content that is the challenge.

[Jimmy Clark]

Ok. What is the challenge associated with the volume? 1 million records is a small data set and certainly does not present a "challenge." Unless, there is something else that you have not mentioned...

[chiyo matsushita]

Jimmy Clark wrote:Ok. What is the challenge associated with the volume? 1 million records is a small data set and certainly does not present a "challenge." Unless, there is something else that you have not mentioned...

Hi Jimmy,

It is reassuring to know that you think 1 mill records is a small data set to handle with java. Can you suggest some best practices/patterns/potential pitfalls that would be useful in handling the volume?

Thanks in advance.
Chiyo

[Jimmy Clark]

If you know how to write efficient SQL code and all business requirements can be implemented with MySQL's version of SQL, then there is no reason to use Java.

[shukla raghav]

I dont know whether MySQL supports Scheduling otherwise scheduling is the best way to perform this like Oracle Jobs are used for such purposes. Otherwise creating a procedure to serve the purpose is much better approach. because i assume you have create more than a million java objects to handle this.

in case if your modification is petty in one or two columns you can do the following

1. create a function for modification : funct1()
2. insert into new_tab (select col_1, col_2, funct1(col_3) from old_tab);

i hope there is no problem using user derfined functions in select clause of insert statements.