CHAPTeR 9 DXP PeRfORMANCe OPTIMIzATION Optimize the Component/System/Layer For the first step we will identify the root cause for the performance issue. Once the problem-causing component is identified, the next step is to optimize it. Following are the generic guidelines for performance optimizations. • Fine tuning the database: • Use materialized views to store the database query results for popular queries. • Use database hints. • Optimize queries and avoid Cartesian join (a join of each row of one table to each row of another table). • Check if the indexes are created and used by the application queries. • Calculate the DBA statistics for the key tables. • Check the explain plan for the cost of the query and fine tune the query based on the explain plan. • Wherever possible, aggregate functions or stored procedures can be used to do the database heavy lifting operations. • Check if there are any “full table scans” (a query resulting in a scan of every table row) happening instead of an index scan (a query result using the indexed columns). • Fine tuning a server: • Turn off logging. • Carry out calculations in the database. • Use connection pooling (reuse of existing connections through a managed pool) wherever possible. 251