Performance Tips

• Never use SELECT *. Always be explicit in which columns to retrieve and retrieve only the columns that are required.
• Refer to fields implicitly; do not reference fields by their ordinal placement in a Recordset.
• Use stored procedures in lieu of SQL statements in source code to leverage the performance gains they provide.
• Use a stored procedure with output parameters instead of single-record SELECT statements when retrieving one row of data.
• Verify the row count when performing DELETE operations.
• Perform data validation at the client during data entry. Doing so avoids unnecessary round trips to the database with invalid data.
• Avoid using functions in WHERE clauses.
• If possible, specify the primary key in the WHERE clause when updating a single row.
• When using LIKE, do not begin the string with a wildcard character because SQL Server will not be able to use indexes to search for matching values.
• Use WITH RECOMPILE in CREATE PROC when a wide variety of arguments are passed, because the plan stored for the procedure might not be optimal for a given set of parameters.
• Stored procedure execution is faster when you pass parameters by position (the order in which the parameters are declared in the stored procedure) rather than by name.
• Use triggers only for data integrity enforcement and business rule processing and not to return information.
• After each data modification statement inside a transaction, check for an error by testing the global variable @@ERROR.
• Use forward-only/read-only recordsets. To update data, use SQL INSERT and UPDATE statements.
• Never hold locks pending user input.
• Use uncorrelated subqueries instead of correlated subqueries. Uncorrelated subqueries are those where the inner SELECT statement does not rely on the outer SELECT statement for information. In uncorrelated subqueries, the inner query is run once instead of being run for each row returned by the outer query.

Reference: MSDN