SQL SERVER - Rules for Optimizining

SQL SERVER - Rules for Optimizining
• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Non-clustered index should be created on columns of table based on query
which is running
• Following priority order should be followed when any index is created a)
WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original source table
• Triggers should not be used if possible, incorporate the logic of trigger in
stored procedure
• Remove any adhoc queries and use Stored Procedure instead
• Check if there is atleast 30% HHD is empty - it improves the performance a
bit
• If possible move the logic of UDF to SP as well
• Remove * from SELECT and use columns which are only necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is any other way to avoid the
usage of this (either by SELECT … INTO or INSERT … INTO, etc)

SQL SERVER - Optimization Rules of Thumb

Here are few more tips I hope will help you to understand.
One: only “tune” SQL after code is confirmed as working correctly.
(use top (sqlServer) and LIMIT to limit the number of results where appropriate,
SELECT top 10 jim,sue,avril FROM dbo.names )

Two: ensure repeated SQL statements are written absolutely identically to facilate
efficient reuse: re-parsing can often be avoided for each subsequent use.

Three: code the query as simply as possible i.e. no unnecessary columns are
selected, no unnecessary GROUP BY or ORDER BY.

Four: it is the same or faster to SELECT by actual column name(s). The larger the
table the more likely the savings.

Five: do not perform operations on DB objects referenced in the WHERE clause:

Six: avoid a HAVING clause in SELECT statements - it only filters selected rows
after all the rows have been returned. Use HAVING only when summary operations
applied to columns will be restricted by the clause. A WHERE clause may be more
efficient.

Seven: when writing a sub-query (a SELECT statement within the WHERE or HAVING
clause of another SQL statement):
– use a correlated (refers to at least one value from the outer query) sub-query
when the return is relatively small and/or other criteria are efficient i.e. if
the tables within the sub-query have efficient indexes.
– use a noncorrelated (does not refer to the outer query) sub-query when dealing
with large tables from which you expect a large return (many rows) and/or if the
tables within the sub-query do not have efficient indexes.
– ensure that multiple sub-queries are in the most efficient order.
– remember that rewriting a sub-query as a join can sometimes increase efficiency.

Eight: minimize the number of table lookups especially if there are sub-query
SELECTs or multicolumn UPDATEs.

Nine: when doing multiple table joins consider the benefits/costs for each of
EXISTS, IN, and table joins. Depending on your data one or another may be
faster.

‘IN is usually the slowest’.
Note: when most of the filter criteria are in the sub-query IN may be more
efficient; when most of the filter criteria are in the parent-query EXISTS may
be more efficient.
Ten: where possible use EXISTS rather than DISTINCT.

Twelve Tips For Optimizing Sql Server 2005 Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level


How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable