Achieving Performace on large data

[ujjwal soni]

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

[Jayesh A Lalwani]

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]

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]

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

[Jayesh A Lalwani]

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]

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