Optimizing db performance

A couple of weeks ago my colleague Daniel asked me tips and info on optimizing db.
Afficher en écriture latine

With the documentation I have, I found a certain number of techniques, some available on opensource DBMS and  other only on proprietary DBMS.

• Indexing normaly when you query a table all the table data will be read (full table scan). Indexing data is like indexes in a book. So primary key is a special kind of index, it's the unique identifier for a row on the db. How to build indexes? the answer is not easy, but we must always remember that creating a db is not a project but an ongoing process.
  If for example I want to get all the sales ordered by date I will create an index on date_sales field with descending sort order. In this way retrieving last week's sales won't be slow.
• Bitmap indexing this is a sepcial form of index (unfortunatly  not available on MySql) normaly used in columns with a low number of unique values gender, color, ...). The idea is to create for the distinct number of the values in the column, a bit projection is created. This enable faster access to the indexed values.
• Partitioning introduced in MySql 5.1 it's the capibility  to slice a table into multiple physical pieces. For example a large table containing sales data can be partioned by year so that queries for a given year will be performed only on the portion of the data for the requested year.
• ...

 This is only a starting point, in future I will investigate Change Data Capture

techniques, use of products such as Kettle, Mondrian, ...

Good eveningRinaldo

Nested Sets

It is often required to have tree structures, even if only to manage categories. A tree structure is a tree consisting of a single or multiple root which each node can have multiple nodes son, and that with infinite depth. 
Generally the method used to do this is to have a unique identifier for each node of the tree, and insert the identifier of the father node in the registration of the node.
The disadvantage of this technique is that one is obliged to make a SQL query for each node to retrieve all the son of a node. We can improve that by downloading all the nodes in the database, and rebuilding the tree by hand using an array indexed by parent node.
This technique is not really suitable for very large tree structures of several thousands of nodes.
So why don't trying by represent the tree differently, not as a tree, but as  Nested Sets.

The Nested Set is an adjacent list realized in SQL as a tree.

For example having the following tree structure:

We can represent it as a set

The SQL for create the table is :

CREATE TABLE `tree` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 32 ) NOT NULL ,
`parentid` INT NOT NULL ,
`lft` INT NOT NULL ,
`rgt` INT NOT NULL ,
PRIMARY KEY ( `id` ))

Selection of all nodes is very simple:

SELECT
  n0.id
FROM
  tree n0,
  tree n1
WHERE
    n1.id = 0
AND n0.lft BETWEEN n1.lft AND n1.rgt;

...

ASAP I will wrote a Java classes to maintain Nested Sets

keep in touch 

Rinaldo

references: http://www.mti.epita.fr/blogs/2008/07/06/avoir-une-structure-arborescente-en-sql/

  

OpenERP Eclipse XML (view) Template

OpenERP's XML based view templates to create view, wizard, report with predefined templates. 
Watch the video

A PHP/Java Bridge

For those who want to integrate applications written in php with java I report a series of two articles. By working on the jAPS project I have to investigate about integrating a platform for e-learning written in PHP and the jAPS Intranet platform. Definitely a good basis to resume analysis

Customising the Pentaho User Console

For those who use the Pentaho User Console and want to customise it, I suggest reading the series of post from  Prashant Raju

Rinaldo

News from jAPS2.0

Hi friends, news from jAPS2.0 world:
Andrea create a new screencast Istantiate a new portal in 7 steps.
Thank you Andrea
Nice w.e. to all.
Rinaldo

Creating Cascade Parameters with Pentaho Report Designer 3.5.

New from Prashant :

Cascade parameters provide a way of managing large amounts of data in reports. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

In this example I will be using the Steel Wheels sample data to create a report which will display a list of customers using two parameters: a drop down parameter which will contain a distinct list of countries which will then filter another drop down parameter which contains cities which belong to the country.
Read the rest on the Prashant site

Mysql Access denied for user: 'root@localhost'

For those who like me receives this error due to a change in the db.I propose this solution which works on Ubuntu 9.04:
$ sudo service mysql stop
$ sudo mysqld -skip-grant-tables
so use your favorite tool to do the "grant" of users, then stop mysqld and restarted the mysql service
$ sudo service mysql startI hope this post will serve.


keep in touch
regards
Rinaldo

jAPS 2 0 - Presentation Layer Comparison

A very brief (really, it's short) comparison between jAPS 2.0 and some "Mistery Portlet Container".

Check out this SlideShare Presentation by William Ghelfi.

Pentaho and jAPS Target Italian Government with Business Intelligence

Technology Partnership Forms a Unique and Innovative Solution for Government Agencies
Pentaho Corporation, the commercial open source alternative for business intelligence (BI), and jAPS, the emerging open source Enterprise Accessible Information Platform, announced a strategic technology partnership aimed at delivering cost effective information applications to the Italian government. Through tighter integration between jAPS and Pentaho, the Italian government can take advantage of fully integrated BI solutions to make government intelligence information accessible to more individuals, including those with disabilities.
(more)

Brute Force your OpenERP data integration with OOOR inside the Kettle ETL

OpenERP is all the rage among open source ERP's but its native import/export have limitations when it comes to data integration. Server side OpenERP import/export is powerful but not so easy to get started and get interfaced. On the contrary, the famous Kettle open source ETL from Pentaho connects to almost anything, any SGBD thanks to the JDBC connectors, any CSV, Excell files...
With TerminatOOOR you have all the power of the full OpenERP API right inside your ETL data in/out flow. You can do any Create Read Update Delete operation enforcing the access rights of OpenERP. But you are absolutely not imited to that, in fact you can just do anything you would do with your OpenERP client: click buttons, perform workflow actions, trigger on_change events... This is because OOOR gives you the full access to OpenERP API.Thanks rvalyi (more...)

News from The Roar of the Fagiano*

jAPS 2.0 Tag Library Documentation
They published the TLD Doc for jAPS 2.0.

Have a look at japs-20-tag-library-documentation

Ruby OOOR UML reverse engineering tool

I don't know if you got that, but Ruby OOOR connector for OpenERP is able to draw UML diagrams of any OpenERP classes since version 1.2.3. It uses the ir_model and ir_model_fields table to draw the UML, meaning even fields.function are properly displayed unlike tools that just connect to the database schema. More info on the rvalyi post 

Run jAPS 2.0 screencast now is old

A few months ago my friend Andrea  published a screencast titled "Run jAPS in 10 minutes" which shows how to run jAPS 2.0 version 2.0.6.

So now that video is old :) It's old but still valid!

You can still follow the entire video and just remember that you HAVE TO configure /META-INF/context.xml instead of /WEB-INF/conf/systemParams.properties!!

There was many changes between 2.0.6 and 2.0.8 but in order to run jAPS 2.0 (2.0.8) in 10 minutes follow the old video guide and just editcontext.xml instead of systemParams.properties.

...So I suppose Andrea will  make a new video ASAP :) maybe with jAPS 2.0.10 :D

Video Run jAPS 2.0 in 10 minutes