DALESANDRO.NET

Tip 1: Optimize The Query

In my opinion, the first and most important step in performance tuning an application is to optimize the underlying query. If you have a slow query or a query that returns too much data, then the downstream processing will have a negative performance impact regardless of any optimization used in those subsequent steps.

• Minimize the number of columns returned by the query by using named columns instead of SELECT *. Are all of the columns from all of the joined tables truly needed?
• Add the appropriate constraint clauses to your query so that the returned data set is reduced to the right size.

If your operational data set is too large, i.e. you don’t need the data, then don’t return it. This will help improve downstream performance by limiting the amount of data processed by the DBMS and subsequently transferred over the network between the database and application layers.

• Use data aggregation functions in the query. If the data can be consolidated through summation or other aggregation techniques, this will help reduce the data set by returning one row instead of many.
• Identify opportunities to eliminate zero based row data. Depending upon your application requirements, it may not be necessary to return rows with zero or null data elements.
• Provide query hints to the DBMS. In some instances, it may be beneficial to assist the built-in query optimizer when the DBMS generates the execution plan. The optimizer does its best to generate the optimal execution plan, but sometimes the developer knows best.