Categories
Other

Improve your SQL queries

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!