PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Real Time Performance tuning examples !!

Performance tuning

 

Performance Tuning  #1 Recently we encountered an issue wherein a query was running with high impact CPU/IO but having considerate PJI/UII/CPUSKEW/IOSKEW The query has 2 parts , inner subquery and outer main query, like below.

 SELECT 1,2,3, XXXXX FROM ( SELECT XXXX FROM XYZ )

In this scenario the inner query had 5 attributes extracted and outer query had 9 attributes extracted wherein the addition 4 values were fixed parameterized value. On replacing the query with below we found the performance of the query to be improved drastically.

SELECT 1,2,3, XXXXX FROM ( SELECT 1,2,3, XXXXX FROM XYZ )

Conclusion : Addition of external fixed value in internal query rather then appending in outer query only reduces the IO operation performed. Impact CPU reduced from 13000 to 2000 Best practice : Always include the values in internal query itself unless in extreme situation its required only in outer query. ********************************************************************************************************************************************************************* Performance Tuning  #2 An query was performing bad having high impact CPU/IO but having considerable PJI/UII In the query  it had a main driver table joining with the internal subquery , The joining condition had multiple join value.

  • It was noticed that , A column of driver table was joining with the derived column of subquery.
  • Both the driving table attribute and derived column value had 1 null value each.
  • Joining Null value with Null value, Always results in Null value & also increases Skewness.
  • Filtering out the null value which does not have any impact on data resultset improves the query performance and significantly reduces skewness of the query.
  • Best practice : In case of multiple table join, Look out for any null value Attribute joining condition, If exist filter out the null values.

*********************************************************************************************************************************************************************

Add a Comment

Your email address will not be published. Required fields are marked *