1. Use Limited Joins
Make a habit of using as little join as possible, using more joins degrades the query performance. Use only when it's absolutely required.
2. Indexing and Primary Key
Make sure to create indexes for the columns being frequently searched.
Make sure to put the primary key in the table, it also helps in boosting the performance.
3. Use Query Optimization/Plan Report
This report breaks the query processing into multiple parts and tells you how much time each part is taking.
Helps a lot in identifying the issue, and you can take necessary actions accordingly.
Note: Name might depend on DB to DB.
4. Restrict Columns in Select
Avoid fetching all columns or any unused columns, focus on the columns you need to accomplish the given task.
Don't try to reuse same query everywhere in which you're using unused columns.
Create another method, and write a separate query.
5. Avoid Creating Too Long Horizontal Tables
Avoid creating 100s of columns in single table, use normalization.
Break down the table into different tables.
Map them with primary/foreign keys.
6. Avoid Wildcard-based Search
The wildcard-based search slows down the query performance.
It scans all the records to find correct results.
Avoid them if the same job can be done with an alternate approach.
7. Avoid Subqueries
Too many subqueries can degrade the performance very badly.
They get executed for each row fetched so it is advisable to limit their use.
8. Prefer EXISTS over Joins
Sometimes, to check if a record exists or not, we use join with the main table.
For example, if you are not retrieving the columns of the other table which you've joined, then use EXISTS instead of Join.
EXISTS is blazing fast!
9. Prefer Searching on Primary Key
Primary keys are optimized for searching, whenever possible always use primary key in where clause instead of using any non primary key column.
10. Consider, Restructuring Your Tables
You should consider restructuring if you find some glitch with the DB design.
Or if you later found there is a better design available that can solve the performance issue. Use it as last resort.