Performance TIPS - II

Hi,

Let's continue with our Performance tips series - II.

Avoid Functions/Procedures with SQL statement using Group By clause:
Functions / Procedures should not use Group By clause in SQL statement. Although whenever we need to group the result we need to use GROUP by clause.

Avoid long table names: We must not use very long table names. We must limit the table name with X characters.

Avoid tables not involved in foreign keys: We must avoid refering tables which don't take part in foreign keys. Whenever we refer to these then we need to find a path to match data and get relevant or required data, so we must avoid referring to tables without foreign keys.

Avoid using functions in Select and Where clause: Whenever we use function in Select statement of Where clause it will be called for each & every row, which will result in a very slow query. Functions like ltrim(), substring() or any user function will result in low performance.

Avoid Functions / Procedures with queries on too many Tables: Avoid Functions / Procedures with queries on more than 4 Tables. Queries with more than 4 Tables is considered complex. Changing the threshold value requires Metric Assistant configuration update. This means that we should not join more than 4 queries but when we follow normalization and create multiple tables, its difficult to have only 4 tables in the query. We may have to refer to more than 4 tables to achieve required result.

Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction: Avoid Stored Procedures doing an Insert, Update or Delete and not managing a transaction (execute "begin tran" if @@trancount=0). We must always perform and INSERT, UPDATE or DELETE in a transaction, so that the data is always consistent.

Never use an undocumented feature: We must never use an undocumented feature of sql server because there is no notification when it will be removed from future version, which may cause big maintenance problem.\

Truncate Table statement: Always avoid using Truncate Table statement because when this command is used, the data is not logged. Because where clause can't be used with Truncate, it will delete all data. Unless BEGIN TRANS and ROLLBACK is used, nothing can be recovered. So be sure when to use Truncate table statement.

Avoid tables without clustered Index: We must always have clustered index except for table with very little data much data. Having clustered index always help retrieve result faster.

Avoid cursors: Whenever there is a scenario where we need to loop through data for processing, we just simply create a cursor without evaluating overall impact. Consider a scenario when we need to run nested loop using cursor, we need to maintain the state of the outer loop. There can be alternatives like CTE (Common Table Expressions) or Temp tables with Identity columns and then get the minimum and maximum values to run a loop. There can be other alternatives also.

Avoid cursors inside a loop: As its mentioned in the previous point that we must avoid cursors, but if we are using it make sure its not used in a loop.

Avoid using GOTO statement: As we already know we must always avoid GOTO statement but its true that we always encounter situation when we can't avoid using GOTO. In simple words, use it only when there is no alternative.

Avoid SQL queries not using the first column of a composite index in the
WHERE clause: Whenever we write query we must keep in mind the sequence in which composite index is created. We must use the fields in where clause as per the defined index sequence.

Select * from table: Whenever we write query we must avoid using "select *", we must refer to individual fields.

UNION ALL Vs UNION: When we need to combine 2 resultsets to generate 1 resultset and we know that both resultset are distinct we must use UNION ALL rather than UNION because UNION internally uses DISTINCT after the result is returned.


Do write your comments.

Regards