{"id":275,"date":"2020-10-06T09:00:56","date_gmt":"2020-10-06T09:00:56","guid":{"rendered":"https:\/\/turtleflyblog.com\/?p=275"},"modified":"2022-02-28T09:45:48","modified_gmt":"2022-02-28T09:45:48","slug":"sql-performance","status":"publish","type":"post","link":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/","title":{"rendered":"SQL Performance"},"content":{"rendered":"\n<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>If subquery is a parameter, then use <strong>EXISTS<\/strong> instead<strong> IN<\/strong><\/li><\/ol>\n\n\n\n<p>If you use subquery as a parameter, it is better that you us <strong>EXISTS<\/strong> than <strong>IN<\/strong> even these two statements return same result, the performance of <strong>EXISTS <\/strong>is superior to <strong>IN<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Use <strong>IN<\/strong><\/li><\/ol>\n\n\n\n<p>SELECT * FROM Class_A WHERE id IN (SELECT id FROM CLASS_B);<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Use EXISTS<\/li><\/ul>\n\n\n\n<p>SELECT * FROM CLASS_A A WHERE EXISTS<\/p>\n\n\n\n<p>(SELECT * FROM Class_B B WHERE A.id=B.id);<\/p>\n\n\n\n<p>The reason why<strong> EXISTS <\/strong>has higher speed<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>If id(key) has index, it then will reference index<\/li><li><strong>EXISTS <\/strong>will stop executing once it has found a qualified row, but <strong>IN <\/strong>will continue to search all the data for a table.<\/li><\/ol>\n\n\n\n<p>2.&nbsp; Avoid using order<\/p>\n\n\n\n<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DBMS does ordering execution frequently, so the user doesn\u2019t have to care which ordering operation is performing<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Use All option<\/li><\/ol>\n\n\n\n<p>For example UNION, if you don\u2019t need repeated data, you can use UNION ALL instead UNION<\/p>\n\n\n\n<p>1.<\/p>\n\n\n\n<p>SELECT * FROM CLASS_A<\/p>\n\n\n\n<p>UNION<\/p>\n\n\n\n<p>SELECT * FROM CLASS_B<\/p>\n\n\n\n<p>2.<\/p>\n\n\n\n<p>SELECT * FROM CLASS_A<\/p>\n\n\n\n<p>UNION ALL<\/p>\n\n\n\n<p>SELECT * FROM CLASS_B<\/p>\n\n\n\n<p>Use <strong>EXISTS<\/strong> rather than <strong>DISTINCT<\/strong><\/p>\n\n\n\n<p><strong>DISTINCT <\/strong>will exclude repeated data. When you try to combine two tables, you shall use <strong>EXISTS<\/strong> instead of <strong>DISTINCT <\/strong>to avoid ordering.<\/p>\n\n\n\n<p>For example,<\/p>\n\n\n\n<p>1. SELECT DISTINCT I.item_no FROM Items I INNER JOIN SalesHistory SH ON I.item_no = SH.item_no;<\/p>\n\n\n\n<p>2. SELECT item_no FROM Items I WHERE EXISTS<\/p>\n\n\n\n<p>(SELECT * FROM SalesHistory SH WHERE I.item_no=SH.item_no);<\/p>\n\n\n\n<p>3<strong>.(MAX\/MIN) use index<\/strong><\/p>\n\n\n\n<p>When you use <strong>MAX<\/strong> and <strong>MIN <\/strong>two math functions, these two will do ordering but if the parameter has index, it will seek not scan all the table<\/p>\n\n\n\n<p><strong>4.Write the statement in WHERE than HAVING<\/strong><\/p>\n\n\n\n<p>1.<\/p>\n\n\n\n<p>SELECT sale_date, SUM(quantity) FROM SalesHistory<\/p>\n\n\n\n<p>GROUP BY sale_date<\/p>\n\n\n\n<p>HAVING sale_date = \u20182010-10-2\u2019;<\/p>\n\n\n\n<p>2.<\/p>\n\n\n\n<p>SELECT sale_date, SUM(quantity) FROM SalesHistory<\/p>\n\n\n\n<p>WHERE sale_date= \u20182010-10-2\u2019 GROUP BY sale_date<\/p>\n\n\n\n<p>The second example (WHERE) has better performance. There are two reasons. Firstly, index works on where statement. Secondly, GROUP BY will do ordering and hashing executions. Hence we have to minimize the row to improve the performance of ordering. Index does not work on HAVING.<\/p>\n\n\n\n<p><strong>5. Reduce the intermediary table<\/strong><\/p>\n\n\n\n<p>Intermediary table needs lots of memory and it is hard to use index, therefore minimize the use of intermediary table is a key to improve table performance.<\/p>\n\n\n\n<p>Use HAVING<\/p>\n\n\n\n<p>SELECT * FROM(SELECT sale_date, MAX(quantity) AS max_qty FROM SalesHistroy<\/p>\n\n\n\n<p>GROUP BY sale_date)TMP WHERE max_qty &gt;= 10;<\/p>\n\n\n\n<p>Do not build extra middle table, use Having<\/p>\n\n\n\n<p>SELECT sale_date, MAX(quantity) FROM SalesHistory<\/p>\n\n\n\n<p>GROUP BY sale_date HAVING MAX(quantity) &gt;= 10<\/p>\n\n\n\n<p>The above five tips are the key method to improve overall SQL performance, but the most important thing is to find the fundamental problem and then solve it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance. If subquery is a parameter, then use EXISTS instead IN If you use subquery as a parameter, it is better that you us EXISTS than IN even these two statements return same result, the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[29],"tags":[],"class_list":["post-275","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Performance - Who said turtle cannot fly<\/title>\n<meta name=\"description\" content=\"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Performance - Who said turtle cannot fly\" \/>\n<meta property=\"og:description\" content=\"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/\" \/>\n<meta property=\"og:site_name\" content=\"Who said turtle cannot fly\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-06T09:00:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-28T09:45:48+00:00\" \/>\n<meta name=\"author\" content=\"Joseph\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/\",\"url\":\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/\",\"name\":\"SQL Performance - Who said turtle cannot fly\",\"isPartOf\":{\"@id\":\"https:\/\/turtleflyblog.com\/#website\"},\"datePublished\":\"2020-10-06T09:00:56+00:00\",\"dateModified\":\"2022-02-28T09:45:48+00:00\",\"author\":{\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172\"},\"description\":\"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.\",\"breadcrumb\":{\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/#breadcrumb\"},\"inLanguage\":\"en-AU\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/turtleflyblog.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Performance\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/turtleflyblog.com\/#website\",\"url\":\"https:\/\/turtleflyblog.com\/\",\"name\":\"Who said turtle cannot fly\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/turtleflyblog.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-AU\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172\",\"name\":\"Joseph\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-AU\",\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g\",\"caption\":\"Joseph\"},\"sameAs\":[\"https:\/\/turtleflyblog.com\/\/\"],\"url\":\"https:\/\/turtleflyblog.com\/author\/joseph\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Performance - Who said turtle cannot fly","description":"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.","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:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/","og_locale":"en_US","og_type":"article","og_title":"SQL Performance - Who said turtle cannot fly","og_description":"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.","og_url":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/","og_site_name":"Who said turtle cannot fly","article_published_time":"2020-10-06T09:00:56+00:00","article_modified_time":"2022-02-28T09:45:48+00:00","author":"Joseph","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Joseph","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/","url":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/","name":"SQL Performance - Who said turtle cannot fly","isPartOf":{"@id":"https:\/\/turtleflyblog.com\/#website"},"datePublished":"2020-10-06T09:00:56+00:00","dateModified":"2022-02-28T09:45:48+00:00","author":{"@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172"},"description":"\u00a0\u00a0\u00a0 SQL performance is always a key issue that every DBA faces. This article introduces five ways for improving SQL performance.","breadcrumb":{"@id":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/#breadcrumb"},"inLanguage":"en-AU","potentialAction":[{"@type":"ReadAction","target":["https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/turtleflyblog.com\/en\/2020\/10\/06\/sql-performance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/turtleflyblog.com\/en\/"},{"@type":"ListItem","position":2,"name":"SQL Performance"}]},{"@type":"WebSite","@id":"https:\/\/turtleflyblog.com\/#website","url":"https:\/\/turtleflyblog.com\/","name":"Who said turtle cannot fly","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/turtleflyblog.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-AU"},{"@type":"Person","@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172","name":"Joseph","image":{"@type":"ImageObject","inLanguage":"en-AU","@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g","caption":"Joseph"},"sameAs":["https:\/\/turtleflyblog.com\/\/"],"url":"https:\/\/turtleflyblog.com\/author\/joseph\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/comments?post=275"}],"version-history":[{"count":1,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/275\/revisions"}],"predecessor-version":[{"id":665,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/275\/revisions\/665"}],"wp:attachment":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/media?parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/categories?post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/tags?post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}