Best Practices of SQL 2005/2008

Hey You,

       Have you ever been in projects which has lots of performance issues after released to production or QA, which was working great in local and dev. One might be due to network latency, but can we contact the network team to tell them to check the backbone network ?.

Even if we Contact them they run some tools to tell it is a code issue that is slowing down the application. And when we go back to our code, we mostly find out tweaking our stored procedure will greatly increase the performance.Most of the .Net application loads faster irrespective of number of controls in the page.

   So today you can see some well known information that tweaks the sql performance. Most of them you read will already be known by you, but we would have lost some of them due to our development time crunches or our reference has changed from SQL to some other technology.

I guarantee you, just following any two tweaks will improve your query ten folds .

us consider below tables and below query for tweaking of our SQL. Employee table has details about the employees which has PAYID column which will denote the type of salary the employee gets under his group. PayDetails table has all the details about the salary package for all employees.

For explanatory purpose, there are only 100 employees in which 90 of them are Daily Wages, 7 of them are Supervisors, 2 of them are managers and remaining one is CEO. So there will be only 4 records in PayDetails

Employee	PayDetails
EmployeeID	ID
Name	PayDetName
Age	Year
PayID	Fixed Salary
Indexes	VariablePay
Employee_INDX_ID	TotalPay
Employee_INDX_PayID	Indexes
	PayDet_INDX_ID

Select e.EmployeeID,p.PayDetName,p.TotalName

From Employee e Left Join PayDetails p ON

e.PayID = p.ID

Where e.EmployeeID = '100' and Year between 2004 and 2009 and PayDetName Like 'flexi%'

Filter Order in Select Query.

           Always place filter in the select query in order of index. Let us take the above example, there are three indexes Employee_INDX_ID, Employee_INDX_PayID and PayDet_INDX_ID and their corresponding columns are Employee ID, Employee Payid and PayDetails ID. So when the query is written if you use any of these columns make sure these columns take precedence over other non indexed columns in the WHERE Clause.

Select only needed Columns

    Everybody says this as a broken record, it has been a GOLDEN RULE for ages, DO NOT USE Select * from table. It is really mandatory that you select the columns explicitly even though you select all the columns from the table. If there is any addition of new column later point of time after development, which might be housekeeping column for that table then the unwanted housekeeping column will also be selected when you use Select *. It may screw up some binding in the Front end too.

Use Constants and Literals

           

            Use Constants and Literals wherever possible, if the value in the Where clause is going to stay for a while.  Execution plan is created, when we have constants and literals instead of hardcode values.

Compare Equivalent Data Type

Always compare equivalent data type meaning compare integer type column with integer type column.

If you have any column which has string value and you probably know it can hold only integer as value, create a new column and replicate the data to the new column with Integer data type so that in later queries you use the new column created. Because casting takes more time than fetching data in some cases.

Filter Sequence (filter that filters most rows).

    As a thumb rule have columns in filter which has index to the corresponding columns. You also make sure you have order and a sequence in which the filter should be placed. These are the order the filter should be placed =, IN, BETWEEN, LIKE and finally sub queries. Consider these sequences as case to case basis; always make sure they are in order of filter that filters most rows.

Use Top in right way and it’s the best one to use

In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP.

Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:

·            In SQL Server 2012, SET ROWCOUNT does not affect DELETE, INSERT, MERGE, and UPDATE statements. 

·            As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be considered in a query plan.

SQL Profiler

      Use SQL profile for any debugging activities, learning this tool alone will make you master of the application and also SQL server. You understand many underlying action that takes place between the application and the SQL server

SET NOCount On

           

            If you want increase your query timing from 3 minutes to 3 seconds try this. Just using this alone, I have tweaked n number of stored procedures. Try and let me know how it works for you. It makes the stored procedure not to send the number of rows affected. But it has huge effect in performance.

Remove String Comparison (Use Literal)

            Always use literal wherever you are using string comparison, this also impact your query performance in huge way. Try and let me know.