{"id":26672,"date":"2018-08-30T07:00:58","date_gmt":"2018-08-30T07:00:58","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=26672"},"modified":"2021-03-12T15:08:16","modified_gmt":"2021-03-12T09:38:16","slug":"sql-query-optimization","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/","title":{"rendered":"SQL Query Optimization Tools | Query Tuning Tips"},"content":{"rendered":"

In our last SQL tutorial<\/strong>, we discussed SQL Subqueries. In this session of SQL, we will see SQL Query Optimization or SQL Query Tuning. <\/span><\/p>\n

M<\/span>oreover, we will learn different techniques for optimizing or tuning a query in SQL.\u00a0 Also, we will see SQL Query Optimization examples.<\/span><\/p>\n

So, let us start the SQL Query Optimization tutorial.<\/span><\/p>\n

Introduction to SQL Query Optimization<\/span><\/h3>\n

SQL Statements are used to recover data from the database. <\/span><\/p>\n

We can get the same outcomes by optimizing diverse SQL queries. <\/span><\/p>\n

In any case, use of the best query is vital when execution is considered.<\/span><\/p>\n

SQL Query Optimization\/Tuning Techniques<\/h3>\n

1. The SQL query turns out to be quicker in the case that you use the real segment names in SELECT rather than ‘*’. <\/strong><\/p>\n

For example:<\/em> Write the query as<\/span><\/p>\n

SELECT id, first_name, last_name, age, subject FROM student_details;<\/pre>\n
Rather than: <\/span><\/p>\n
SELECT * FROM student_details;<\/pre>\n
2.<\/strong>\u00a0Having<\/strong> condition is used to channel the rows after every one of the rows is chosen. Try not to use HAVING provision for some other purposes.<\/strong><\/p>\n
For example:<\/em> Write the query as<\/span><\/p>\n
SELECT subject, count(subject)\r\nFROM student_details\r\nWHERE subject != 'Science'\r\nAND subject != 'Maths'\r\nGROUP BY subject;<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT subject, count(subject)\r\nFROM student_details\r\nGROUP BY subject\r\nHAVING subject!= 'Vancouver' AND subject!= 'Toronto';<\/pre>\n
3. Sometimes you may have in excess of one subquery in your principle query. <\/strong><\/p>\n
For example:<\/em> Write the query as<\/span><\/p>\n
SELECT name\r\nFROM employee\r\nWHERE (salary, age ) = (SELECT MAX (salary), MAX (age)\r\nFROM employee_details)\r\nAND dept = 'Electronics';<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT name\r\nFROM employee\r\nWHERE salary = (SELECT MAX(salary) FROM employee_details)\r\nAND age = (SELECT MAX(age) FROM employee_details)\r\nAND emp_dept = 'Electronics';<\/pre>\n
4. Use administrator EXISTS, IN and table joins suitably in your query.<\/strong> <\/span><\/p>\n
a) Usually IN has the slowest execution. <\/span>
\nb) IN is effective when a large portion is in the sub-query. <\/span>
\nc) EXISTS is useful.<\/span><\/p>\n
For example:<\/em> Write the query as<\/span><\/p>\n
Select * from product p\r\nwhere EXISTS (select * from order_items o\r\nwhere o.product_id = p.product_id)<\/pre>\n
Instead of:<\/span><\/p>\n
Select * from product p\r\nwhere product_id IN\r\n(select product_id from order_items<\/pre>\n
5. Use EXISTS rather than DISTINCT when using joins which includes tables having the one-to-many relationship. <\/strong><\/p>\n
For example:<\/em> Write the query as<\/span><\/p>\n
SELECT d.dept_id, d.dept\r\nFROM dept d\r\nWHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT DISTINCT d.dept_id, d.dept\r\nFROM dept d,employee e\r\nWHERE e.dept = e.dept;<\/pre>\n
6. Try to use UNION ALL instead of UNION. <\/strong><\/p>\n
For example:<\/em> Write the query as<\/span><\/p>\n
SELECT id, first_name\r\nFROM student_details_class10\r\nUNION ALL\r\nSELECT id, first_name\r\nFROM sports_team;<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, first_name, subject\r\nFROM student_details_class10\r\nUNION\r\nSELECT id, first_name\r\nFROM sports_team;<\/pre>\n
7. Be cautious while using conditions in WHERE statement. <\/strong><\/p>\n
SELECT id, first_name, age FROM student_details WHERE age > 10;<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, first_name, age FROM student_details WHERE age != 10;<\/pre>\n
Write the query as<\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE first_name LIKE 'Chan%';<\/pre>\n
Instead of:<\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE SUBSTR(first_name,1,3) = 'Cha';<\/pre>\n
Write the query as<\/span><\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE first_name LIKE NVL ( :name, '%');<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE first_name = NVL ( :name, first_name);<\/pre>\n
Write the query as<\/span><\/p>\n
SELECT product_id, product_name\r\nFROM product\r\nWHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT product_id, product_name\r\nFROM product\r\nWHERE unit_price >= MAX(unit_price)\r\nand unit_price <= MIN(unit_price)<\/pre>\n
Write the query as<\/p>\n
SELECT id, name, salary\r\nFROM employee\r\nWHERE dept = 'Electronics'\r\nAND location = 'Bangalore';<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, name, salary\r\nFROM employee\r\nWHERE dept || location= 'ElectronicsBangalore';<\/pre>\n
Use non-column expression on one side of the query because it will be processed earlier.<\/span><\/p>\n
Write the query as<\/span><\/p>\n
SELECT id, name, salary\r\nFROM employee\r\nWHERE salary < 25000;<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, name, salary\r\nFROM employee\r\nWHERE salary + 10000 < 35000;<\/pre>\n
Write the query as<\/span><\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE age > 10;<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT id, first_name, age\r\nFROM student_details\r\nWHERE age NOT = 10;<\/pre>\n
8. Use DECODE to stay away from the checking of same columns or joining a similar table monotonously. It can likewise be made used instead of GROUP BY or ORDER BY statement. <\/strong><\/p>\n
For example:<\/em> Write the query as <\/span><\/p>\n
SELECT id FROM employee\r\nWHERE name LIKE 'Ramesh%'\r\nand location = 'Bangalore';<\/pre>\n
Instead of:<\/span><\/p>\n
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee\r\nWHERE name LIKE 'Ramesh%';<\/pre>\n
9. To compose the query which gives proficient execution take after the general SQL standard. <\/strong><\/p>\n
a. Use single case for all SQL verbs.<\/span>
\nb. Begin all SQL verbs on another line.<\/span>
\nc. Separate all words with a solitary space.<\/span>
\nd. Right or left adjusting verbs inside the underlying SQL verb.<\/span><\/p>\n
So, this was all in SQL Query Optimization. Hope you like our explanation of SQL Tuning.<\/p>\n
Summary<\/span><\/h3>\n
Hence, in this SQL Query Optimization tutorial, we discussed how to optimize a query in SQL. <\/span><\/p>\n
Moreover, we saw SQL Query Optimization Techniques. Also, we discussed SQL Query Optimization examples. <\/span><\/p>\n
Still, if you are having any confusion in SQL Query Optimization\/Tuning, feel free to ask in the comments.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
In our last SQL tutorial, we discussed SQL Subqueries. In this session of SQL, we will see SQL Query Optimization or SQL Query Tuning. Moreover, we will learn different techniques for optimizing or tuning...<\/p>\n","protected":false},"author":6,"featured_media":30623,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[11099,11101,13509,13516,13517,13518,13581],"class_list":["post-26672","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-query-optimization-example","tag-query-optimization-techniques-in-sql-server","tag-sql-performance-tuning","tag-sql-query-optimization-tool","tag-sql-query-performance-tuning-tips","tag-sql-query-tuning","tag-sql-tuning-tutorial"],"yoast_head":"\nSQL Query Optimization Tools | Query Tuning Tips - DataFlair<\/title>\n<meta name=\"description\" content=\"sql query optimization,SQL Query Tuning,how to optiimize SQL Query,SQL Query Optimization Tool,SQL Tuning tutorial,Query Optimization example & Tips\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Query Optimization Tools | Query Tuning Tips - DataFlair\" \/>\n<meta property=\"og:description\" content=\"sql query optimization,SQL Query Tuning,how to optiimize SQL Query,SQL Query Optimization Tool,SQL Tuning tutorial,Query Optimization example & Tips\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-30T07:00:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-12T09:38:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Query Optimization Tools | Query Tuning Tips - DataFlair","description":"sql query optimization,SQL Query Tuning,how to optiimize SQL Query,SQL Query Optimization Tool,SQL Tuning tutorial,Query Optimization example & Tips","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/","og_locale":"en_US","og_type":"article","og_title":"SQL Query Optimization Tools | Query Tuning Tips - DataFlair","og_description":"sql query optimization,SQL Query Tuning,how to optiimize SQL Query,SQL Query Optimization Tool,SQL Tuning tutorial,Query Optimization example & Tips","og_url":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-30T07:00:58+00:00","article_modified_time":"2021-03-12T09:38:16+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Query Optimization Tools | Query Tuning Tips","datePublished":"2018-08-30T07:00:58+00:00","dateModified":"2021-03-12T09:38:16+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/"},"wordCount":489,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg","keywords":["Query Optimization example","Query Optimization Techniques in SQL Server","SQL Performance Tuning","SQL Query Optimization Tool","SQL Query performance Tuning tips","SQL Query Tuning","SQL Tuning tutorial"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/","url":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/","name":"SQL Query Optimization Tools | Query Tuning Tips - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg","datePublished":"2018-08-30T07:00:58+00:00","dateModified":"2021-03-12T09:38:16+00:00","description":"sql query optimization,SQL Query Tuning,how to optiimize SQL Query,SQL Query Optimization Tool,SQL Tuning tutorial,Query Optimization example & Tips","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-query-optimization\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/9-Techniques-of-SQL-Query-Optimization-01-1.jpg","width":1200,"height":628,"caption":"SQL Query Optimization Tools | Query Tuning Tips"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-query-optimization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Query Optimization Tools | Query Tuning Tips"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/26672","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=26672"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/26672\/revisions"}],"predecessor-version":[{"id":87125,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/26672\/revisions\/87125"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/30623"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=26672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=26672"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=26672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}