Almost Every website data is stored in a database and served to visitors
upon request. Databases are very fast, but there is lots of things that we need to enhance speed and make sure not to waste any server
resources. In this article, I am suggesting you 10 tips to
optimize and speed up your Sql query.
1. Do not Select column that we do not need.
A very common practice is to use Select * from tablename.
It's better to select column which you need in output.
2. Avoid using Cursor , use while loop instead of Cursor.
3. Avoid using Sql statement in a loop.It's takes a lot of resource.
4.Use Join instead of Subqueries.
5. Use Union instead of OR
7. Use indexing on Table. Create clustered or non-clustered index on those column which you require frequently in sql query.
8.Do not Name Your Stored Procedures with ‘sp_’ at the start.
9. Normalize your tables.
10. Use schema name before your table name. like Select id from dbo.books.
:))
1. Do not Select column that we do not need.
A very common practice is to use Select * from tablename.
It's better to select column which you need in output.
2. Avoid using Cursor , use while loop instead of Cursor.
3. Avoid using Sql statement in a loop.It's takes a lot of resource.
4.Use Join instead of Subqueries.
SELECT a.id, (SELECT MAX(created) FROM posts WHERE book_id = a.id) AS latest_post FROM books a
However subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.
SELECT a.id, MAX(p.created) AS latest_post FROM books a INNER JOIN posts p ON (a.id = p.book_id) GROUP BY a.id
5. Use Union instead of OR
6. Use Set No Count On at the beginning of Stored procedure and Set No Count Off before ending of Stored procedure.
8.Do not Name Your Stored Procedures with ‘sp_’ at the start.
9. Normalize your tables.
10. Use schema name before your table name. like Select id from dbo.books.
:))
I support the write's unique point. It is useful and benefit to your daily life.
ReplyDelete