Here are some useful tips to improve the performance
1) Try to use where clause for restrict result.
2)Use predecessor “dbo.” for tables.
3)Use proper join (“INNER JOIN ,OUTER JOIN “)
4) Try to avoid “OR” condition use “UNION” over there.
5) Try to avoid “IN” Operation .
6) Try To avoid “NOT IN” operation
7) Try to avoid “DISTINCT”.
8) Try to avoid “CROSS JOIN”.
9) Try to avoid use of Temporary Table. but if needed then define pre structure for that.
9) Define PRIMARY Key & UNIQUE Key Constraint for each table.
10) Try to avoid “HAVING Clause”
11)Include “SET NOCOUNT” at the first of your store Procedure.
12) Try to avoid “CURSOR”.
13) Use “UNION ALL” Instead Of “UNION”.
14) Try to create INDEX.
15) Create Index On column which is frequently used in Where , order by & Join.
16) Try to create index on Integer Column.
17) try to avoid “SELECT * ” instead of it use “SELECT ColumnName,”