Well, There are many cases where our query are working fine there are few records. But the same query is performing in very bad manner when there are huge records. for example : lacs of records or more..
In such case, we can scan our query/SP for following points to increase the performance of our Query/SP.
1) Always specify column name while selecting the records from table and never write query
like
select * from Table_Name
instead it should be
select column_name1, column_name2, column_name3... from Table_Name
2) while checking existence of records, never use query like below...
if exists(select * from Table_Name where column_name = @Value)
begin
end
Instead it should be
if exists(select 1 from Table_Name where column_name = @Value)
begin
end
3) While selecting records, never forget to write SET NOCOUNT ON.
4) Remove all unnecessary joins.
5) Never use aggregate functions while selecting the records, Instead select it once, store it somewhere like temporary table and then do all your operations. It will definitely improve performance of the query.
Contd..
In such case, we can scan our query/SP for following points to increase the performance of our Query/SP.
1) Always specify column name while selecting the records from table and never write query
like
instead it should be
select column_name1, column_name2, column_name3... from Table_Name
2) while checking existence of records, never use query like below...
Instead it should be
if exists(select 1 from Table_Name where column_name = @Value)
begin
end
3) While selecting records, never forget to write SET NOCOUNT ON.
4) Remove all unnecessary joins.
5) Never use aggregate functions while selecting the records, Instead select it once, store it somewhere like temporary table and then do all your operations. It will definitely improve performance of the query.
Contd..
