This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Performance and the fly likes Achieving  Performace on large data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Achieving  Performace on large data" Watch "Achieving  Performace on large data" New topic
Author

Achieving Performace on large data

ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

I have an application which has 10 million rows and 1000 columns in Oracle. Each value has a different set of calculations that are stored in User Defined PLSQL functions.

Data is displayed in form of data grid. When a user updates any value, the calculation is performed using plsql function and value is stored in database. Is there an easy way through which calculation is performed on the fly and i get maximum performance ?

Thanks,

Ujjwal Soni


Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2271
    
  28

Yeah, either get a very large database, or don't do it in database.


If you have a database with lots of CPUs and memory, you can turn parallelism on the tables. This will make a lot of your sqls execute faster because internally, oracle will execute the query on multiple threads. Note that this can overload the database, and anything else that is going on at the same time in the database will be affected.

Another way of doing this is to use cloud technologies like Apache Hadoop and use map reduce pattern. This way, you extract the data out of oracle, slice it up into chunks, and distribute the chunks to many machines. Each machine performs the calculations on its own chunk, and sends the results back to the database. This solution can scale a lot better than a database based solution, but is more difficult to implement.
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

thanks for prompt reply, could you please check below query


I have an application where there are 1000 columns in database, each column value is calculated based upon a formulae. This formulae can be modified by a user. I need a way to map these calculations to a particular column in database in such a way that it can be edited by a user.

For example :: My table has 3 columns, COL_A, COL_B & COL_C
I have a calculation which says :: VALUE=COL_A+COL_B

In future, if a user wants to change this calculation say for example :: VALUE=COL_A+COL_B+COL_C then this change should get reflected.

Which is the best way to do this ?

Thanks,

Ujjwal Soni
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 253

Hey Ujjwal,

It is possible to do if following assumption about your application hold true -
1- The users of your application understand the significance of each and every column in database
2- They have basic understanding of sql functions and the way these are used to perform operations, which are more than just addition, subtraction, multiplication, division.

You shall have to create a separate table in database, which will have column names along with formula used to calculate the corresponding value. Once the operations of a column are entered, a trigger on the same table will start execution, make a procedure involving mentioned operation of table and execute in database.

This processing will help you in following ways -
1- The user will have interface of simple UI containing map of column name and corresponding calculation.
2- As soon as a new value for calculation formula is entered, procedure will be created by trigger. This will help help you in making sure that calculation formula entered has valid sql syntax. If syntax is not valid error will be thrown immediately and you would be able to prompt user to provide correct values.

While creating the procedure from trigger, you shall have to make it two phase process to make sure, your existing procedure does not get invalidated, if user did not enter the proper calculation values.

Regards
Salil Verma


Regards
Salil Verma
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2271
    
  28

Hmm.. I don;t see the connection between the 2 questions. However, reading between the lines, it seems like you are trying to build something that can transform millions of rows based on user provided rules. Normally, you call these kind of programs as ETL, and there are off the shelf solutions that can do this for you. I think you might look for off the shelf solutions.

I've used Talend, and Talend generates Java code that you can plugin. I've seen Informatica been used. I think one of the bartenders had his home grown highly customizable framework (although I don't remember what it was called)
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7492
    
  18

ujjwal soni wrote:I have an application where there are 1000 columns in database, each column value is calculated based upon a formulae. This formulae can be modified by a user. I need a way to map these calculations to a particular column in database in such a way that it can be edited by a user.

Simple answer: Give it a key.

That said: this sounds to me like a direct transliteration of a spreadsheet to a database; and spreadsheets are rarely normalized. It also sounds like you may be storing enormous amounts of derived data, which is NOT a good thing.

However, without more information about what these "formulas" are doing, it's very difficult to give specific advice.

Winston

Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
 
wood burning stoves
 
subject: Achieving Performace on large data
 
Similar Threads
Java with PLSQL
Functional Point Analysis for PLSQL batches
Javascript submit problem
Star/Snowflake schema implementation as a Java Persistence Layer
Java With PLSQL