In this article we are going to see the performance impact of some of the queries in SQL SERVER.
SELECT instead of SET:
Always prefer to use SELECT keyword instead of SET , to assign a value for variable.Because in select we can assign a value for multiple variable at a time at a single execution. It takes a one value from a result,if it have more than one value in the result set.
Exists instead of Count():
Always try to use the Exists keyword to check the existence of record, instead of Count(*).Count() needs a full record set to be retrieved
UnionAll instead of Union:
Always try to use UnionAll because Union use to return distinct values due to this some internal operations take more time.
Joins instead of SubQuery:
Try to use Joins instead of subQuery, Because subQuery takes more time to retrieve the records in from table.
Order by :
Try to avoid order by in Query , try to select the data in db and order the data in front end . it takes few second only.If you use order by in Queries. It takes more time to order and takes the data.
Functions :
Try to avoid the usage of functions in where condition, It takes the more time to execute the function for each and every record.
Dynamic Query:
Try to avoid the dynamic query, Because at the run time only query framed and executed so the cache is not possible for that query.
Cursor:
Try to use While loop instead of using Cursor , because cursor is executed in record by record in sequence.
More Joins :
Avoid more joins in tables,because this gives very slow performance hit,so try to kept the data in optimized table structure.
Indexes :
Try to create a indexes for the tables to retrieve the records very fast in SELECT, and Avoid the more non-clustered indexed column in Where clause.
I Hope From this article you can see some of the performance tips in SQL SERVER.
No comments:
Post a Comment