Speed Up Sql Query
1. If there is a large Table going to participate in Query, it is better to filter out records based on some filters and reduce the no of records going to participate in Query.
2. If in a query ,joins include few inner joins and left joins than it is better approach to first apply all inner joins and then on the result of inner join performed query, apply left join.
3. Temp table if something that is very large query and we want to take some portion of query out side and wants to use table variable or temp table then tere are 2 cases:
for small data set:
if the result of queries are smaller than we should use table variable it doesn't has overhead of dropping it in the end of query.
for large data-set:
In the case of large result set it is better to use temp table because there isn't over head of writing it to table(by default in the case of temp table simply result sets are referenced by some pointer all temp table). One more advantage we have here is of Indexes we can apply indexes on temp table and it get dropped when this temp table dropped out.
4. Fields of table should not be used in function, in where clause or select clause If we do this it will be slower, so good will be not to use them in function. We can use constant variable or parameter in function it wont affect query very much.
5. Make sure we have proper indexes on table going to participate specially on large table.
6. Drop temp tables just after the if not usable any longer. In the case of single time use i prefer to use CTE tables.
Happy Living....
Happy Concepts....
Happy Programming....
