Not that I want to teach SQL to any one, but do not forget this simple things to improve your queries:
- Usage of Limi/Top if we know how many rows should be return, can save time.
- Do not return * unless you really need all the fields from the table. Saves time and improve.
- If you need a random value use order by newid() for SqlServer and order by rand() from MySql
- Do not save binary data in the rows! (like images or docs etc…)
- If you need to join two tables don’t do this: select * from table1,table2, instead use JOIN
- Important to have indexes in the most used fields.
- The where clauses try them to be for the indexed fields.
- Usage of the same query, this ones once is done is cached.
- Procedures can save lot of time for common queries.
- Try to avoid the usage of Like ‘%something%’ instead be more specific field=’something’.
And last one:
Please, if you need to return the amount of rows from a table, use count(), and never the recordest.count method.
This last one I saw it at many places and kills applications of course!