Try to define the exact requirement, before writing the query.<\/p>\n
[2] Use Fields Name in Query instead of SELECT * table_name<\/strong><\/p>\n Inefficient<\/strong>:<\/p>\n Efficient<\/strong>: because will fetch only specified column<\/p>\n [3] Use the WHERE clause instead of the HAVING clause<\/strong><\/p>\n Inefficient<\/strong>: Because the HAVING clause filters the rows after selecting all the rows. So, It should be used only for filtering the values based on a condition not anywhere else, It is just like a filter only.<\/p>\n Efficient<\/strong>: In SQL operating order, HAVING statements are determined after the WHERE statement. So, a Query with a WHERE clause executes faster than a query with a HAVING clause.<\/p>\n [4] Use JOIN rather than SUBQUERY<\/strong><\/p>\n Inefficient: <\/strong>Because for the execution of every row of the customer table, an inner query or subquery will execute first then the outer query will execute. So, It will reduce the performance. JOIN is used for such cases.<\/p>\n Efficient: <\/strong><\/p>\n [5] Avoid DISTINCT from the SELECT query<\/strong><\/p>\n Inefficient: <\/strong>Using DISTINCT to remove duplicates is an inefficient way because it works by creating groups in the query. However, It consumes lots of power in computation. So, performance can be improved by increasing the number of fields.<\/p>\n Efficient:<\/strong><\/p>\n [6] Check Whether my record Exist Or Not<\/strong><\/p>\n Inefficient: <\/strong>EXITS() or COUNT()\u00a0 both are used for matching a record entry. However, COUNT() is not efficient just because it scans the whole table even if the targeted thing is found in the first row. And EXITS() works such as execution will exit as soon as the targeted thing is found.<\/p>\n IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE \u2018%JOHN%\u2019) > 0 PRINT \u2018YES\u2019<\/p>\n Efficient: <\/strong><\/p>\n IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE \u2018%JOHN%\u2019)PRINT \u2018YES\u2019<\/p>\n <\/p>\n [7] <\/strong>W<\/strong>isely<\/strong> Use wildcard characters <\/strong><\/p>\n Inefficient:<\/strong><\/p>\n This finds data that is not required data other than required data. As It finds “Ramesh”, “Ram”, “And Raghav” which is required but it also finds “Kiran”, “Viral” which was not required.<\/p>\n Efficient:<\/strong><\/p>\n But this finds only required data as “Ramesh”, “Ram”, and “Raghav”.<\/p>\n [8] Whenever Possible, Use <\/strong>UNION ALL instead of UNION <\/strong><\/p>\n Inefficient: <\/strong>UNION performs a DISTINCT on the result set, eliminating any duplicate rows<\/p>\n Efficient: <\/strong>UNION ALL does not remove duplicates, and it is therefore faster than UNION.<\/p>\n Conclusion<\/strong>: By the above description of queries’ execution. It can be concluded that the optimization operation is a necessary operation that should be taken care of by the software developers during the writing of SQL Queries for the development of the software.<\/p>\n","protected":false},"excerpt":{"rendered":" Query optimization is a technique for obtaining required data in a very efficient manner when we deal with databases which are having a very large amount of data and very complex data. And also there is a scenario i.e. in general queries are tested with a database, which is having very fewer data at the […]<\/p>\n","protected":false},"author":1,"featured_media":7808,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[892],"tags":[722,894,216,895,893],"class_list":["post-7795","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-blog","tag-optimization-techniques","tag-sql","tag-sql-blog","tag-sql-query-optimization"],"yoast_head":"\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b1-300x19.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b2-300x25.png\")
<\/strong><\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b3-300x23.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b4-300x23.png\")
<\/strong><\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b5-300x23.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b6-300x26.png\")
<\/strong><\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b7-300x11.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b8-300x26.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b9-300x11.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b10-300x12.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b11-300x10.png\")
<\/p>\n![\"\"](\"https:\/\/innovationm.co\/wp-content\/uploads\/2023\/08\/b12-300x10.png\")
<\/p>\n