Sunday, August 10, 2008

Data Access

Performance Tuning can be applied in various areas of sql server like minimizing the number of calls over network, encapsulating the logic in a stored procedure which helps to use the query plan created by the sql engine or writing to multiple data files depending on requirement. This topic mostly covers tuning from the query analyzer perspective i.e. when you are executing the query in the query analyzer how do u look out for options in performance tuning.

Access the Data that is needed:

Do not access all the data available in the table. Instead access the data or columns which are required in the procedure or the logic.

Never deliberately write a code with SELECT * instead always try to use the columns which are necessary. Even if the logic does not need a column like u can write a code if the rows exist for a particular field as shown below

If exists (select * from table where field =

Begin

–Logic Here

End

Instead replace select * by select and include the column name which is a key in the clustered key index or the primary key depending upon the situation. This provides a better performance.

Always use filter criteria by which you can minimize the rows fetched in a table.

No comments: