Artículos IT, información y publicaciones sobre Performance

Últimas publicaciones destacadas en Dataprix sobre esta temática TIC

UPDATE with JOIN in ORACLE

 

Suppose we want to update in our ORACLE database the costs fields of the fact table FAC_TABLE with the unit cost of our table COSTS.

We can do this in two ways:

1. (Slow, but valid for a few data or to sporadic uses)

update FAC_TABLE ft  set UNIT_COST = (select distinct UNIT_COST from COSTS ct  where (ft.id_article = ct.id_article);

2. (The best way is this, and the performance is ideal if you have constraints)

UPDATE ( SELECT ft.UNIT_COST AS old_cost,  ct.UNIT_COST AS new_cost FROM FAC_TABLE ft  INNER JOIN COSTS ct ON ft.id_article ct = ct.id_article) )  SET old_cost = new_cost;

To the proper functionality of this second option you need a UNIQUE or PRIMARY KEY constraint on ct.id_articulo. 
If you don't have this constraint, you can use the hint / * + BYPASS_UJVC * / after the word UPDATE (Bypass update join view constraint).

The performance increase if we have the constraint but even without it, the second option should run quite faster than the first option.

 

AWR Formatter

 

Anyone who usually look at AWR performance reports to analyze Oracle performance problems, often have their own reading process and approach to all data that shows this report, but I always missed a tool that make easier reading all data.

 

Improve MySQL performance by adjusting some parameters

 

MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.

Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.

I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)

The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.

The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.

Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.

 

Syndicate content