Tips for better query performance

[Lankesh]

I am currently learning SQL (Structure Query Language) and have learnt many queries during my course work. Actually I have seen that same kind of work can be done using different queries. Now one thing is pinching me that if we have to do the same work why need a different query for that. According this may be because different queries are required in different type of condition. If that so, then I need to know the tips for improving the performance of query result.

[Computer_Freak]

Yes it is true that different queries are required in different condition for doing the same work basically the design of query depends upon the size of the database. Here are some tips for improving the performance of query.
1. Fields which are used to set the criteria must be indexed in query.
2. Index all the fields on the both sides of join. Indexes help to produce result faster.
3. It will be better if you use similar or compatible datatypes in join fields. For example autonumber data type is compatible with Long integer.
4. If possible limit the number of fields which are returned by the queries.

[FlashX]

There are some more tips for increasing the performance of the queries .
1. Better to user expressions in subqueries rather than using calculated fields.
2. Never use non-index fields or the calculated fields for restriction of criteria.
3. Field which are used for sorting must be indexed
4. Do not user domain aggregate function while working on foreign tables.
5. Better to use “between” in place of “>= and <=”
6. User “count(*)” rather than using “count(field name)”.