SQL Performance Resources
Tags: performance, sql, practice, programming
Published: 2021-08-10 17:31:15

<p style="margin: 0in 0in 8pt;">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.</span></span></span>
</p>

<p style="margin:0in 0in 8pt">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:</span></span></span>
</p>

<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">The types of indices available</span></span></span>
</li>
<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Using one index type over another</span></span></span>
</li>
<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">The information TuneTable gathers for a table and what it means to the Optimizer</span></span></span>
</li>
<li style="margin:0in 0in 8pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">How to read a Show Plan to better understand if a query is good or bad</span></span></span>
</li>




<p style="margin:0in 0in 8pt">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Below are some general resources to learn more about InterSystems IRIS SQL Performance:</span></span></span>
</p>

<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif"><span style="font-size:14.0pt"><span style="line-height:107%">The Worldwide Response Center (WRC) at 617-621-0700</span></span><br />The WRC is there for you.  They can cover SQL performance with you at whatever level you are comfortable.  There are several good SQL Support people and you may also feel free to ask for Brendan Bannon, Support Manager to be connected to an appropriate advisor.</span></span></span><br /> 
</li>
<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif"><span style="font-size:14.0pt"><span style="line-height:107%">InterSystems online documentatio<b>n</b></span></span></span></span></span><ol style="list-style-type:lower-alpha">
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">First Look:  InterSystems SQL:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlbasics">https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlbasics</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">First Look: Optimizing SQL Performance with InterSystems Products:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlqueryopt">https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlqueryopt</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">InterSystems SQL Optimization Guide:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT">https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT</a> </span></span></span></span></span><br /> 
    </li>
  </ol>
</li>

<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif"><span style="font-size:14.0pt"><span style="line-height:107%">InterSystems eLearning</span></span></span></span></span><ol style="list-style-type:lower-alpha">
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Resource Guide - Learn Caché SQL: Performance: <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://learning.intersystems.com/enrol/index.php?id=255">https://learning.intersystems.com/enrol/index.php?id=255</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Resource Guide – Learn Caché SQL: Design and Execution: <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://learning.intersystems.com/enrol/index.php?id=256">https://learning.intersystems.com/enrol/index.php?id=256</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Optimizing SQL Queries:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://learning.intersystems.com/course/view.php?id=707">https://learning.intersystems.com/course/view.php?id=707</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Academy:  Optimizing SQL Performance:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://learning.intersystems.com/course/view.php?id=80">https://learning.intersystems.com/course/view.php?id=80</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Optimizing Your SQL Queries:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://learning.intersystems.com/course/view.php?id=1013">https://learning.intersystems.com/course/view.php?id=1013</a></span></span></span></span></span><br /> 
    </li>
  </ol>
</li>

<li style="margin:0in 0in 0.0001pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif"><span style="font-size:14.0pt"><span style="line-height:107%">InterSystems Developers Community</span></span></span></span></span><ol style="list-style-type:lower-alpha">
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">The One Query Performance Trick You NEED to Know? Tune Table! <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/one-query-performance-trick-you-need-know-tune-table">https://community.intersystems.com/post/one-query-performance-trick-you-need-know-tune-table</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Horizontal Scalability with InterSystems IRIS:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/horizontal-scalability-intersystems-iris">https://community.intersystems.com/post/horizontal-scalability-intersystems-iris</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Introduction to Outlier Selectivity:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/introduction-outlier-selectivity">https://community.intersystems.com/post/introduction-outlier-selectivity</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Improve SQL Performance for Date Queries.:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/improve-sql-performance-date-range-queries">https://community.intersystems.com/post/improve-sql-performance-date-range-queries</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Improve SQL Performance for Date Queries, AGAIN!:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/improve-sql-performance-date-queries-again">https://community.intersystems.com/post/improve-sql-performance-date-queries-again</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Creating a Custom Index Type in Caché:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><span style="font-size:10.5pt"><span style="background:white"><span style="line-height:107%"><span style='font-family:"Arial",sans-serif'><a href="https://community.intersystems.com/post/creating-custom-index-type-cach%C3%A9">https://community.intersystems.com/post/creating-custom-index-type-cach%C3%A9</a></span></span></span></span></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Globals Are Magic Swords For Managing Data.  Part 1:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/globals-are-magic-swords-managing-data-part-1">https://community.intersystems.com/post/globals-are-magic-swords-managing-data-part-1</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Globals – Magic swords for storing data. Trees.  Part 2:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/globals-magic-swords-storing-data-trees-part-2">https://community.intersystems.com/post/globals-magic-swords-storing-data-trees-part-2</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Globals – Magic swords for storing data. Sparse arrays.  Part 3:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/globals-magic-swords-storing-data-sparse-arrays-part-3">https://community.intersystems.com/post/globals-magic-swords-storing-data-sparse-arrays-part-3</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in" value="10">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Free Text Search:  The Way To Search Your Text Fields That SQL Developers Are Hiding From You!*: <br /><span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/free-text-search-way-search-your-text-fields-sql-developers-are-hiding-you">https://community.intersystems.com/post/free-text-search-way-search-your-text-fields-sql-developers-are-hiding-you</a></span></span> </span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Indexing of non-atomic attributes:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/indexing-non-atomic-attributes">https://community.intersystems.com/post/indexing-non-atomic-attributes</a></span></span></span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">SQL index for array property elements:  <span class="MsoHyperlink" style="color:blue"><span style="text-decoration:underline"><a href="https://community.intersystems.com/post/sql-index-array-property-elements">https://community.intersystems.com/post/sql-index-array-property-elements</a></span></span></span></span></span><br /> 
    </li>
  </ol>
</li>

<li style="margin:0in 0in 8pt 0.5in">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif"><span style="font-size:14.0pt"><span style="line-height:107%">PowerPoints from prior years </span></span></span></span></span>
</li>

<p style="margin: 0in 0in 8pt 80px;">
  <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">The accompanying presentations are older but provide solid material.  Most of the information should apply to InterSystems IRIS although double-check with current documentation to confirm.  All of the following can be found in PDF format attached to this article:</span></span></span>
</p>

<li style="list-style-type:none">
  <ol style="list-style-type:lower-alpha">
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Introduction To Indexing</span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Optimizing SQL Performance (2015)</span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">%PARALLEL Query Hint (2016)</span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Frozen Plans and Parallel Queries (2017)</span></span></span>
    </li>
    <li style="margin:0in 0in 0.0001pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Show Plan to Generated COS (2013)</span></span></span>
    </li>
    <li style="margin:0in 0in 8pt 0.5in">
      <span style="font-size:11pt"><span style="line-height:107%"><span style="font-family:Calibri,sans-serif">Programming with SQL (2006)</span></span></span>
    </li>
  </ol>
</li>

<p style="margin-top: 0in; margin-right: 0in; margin-bottom: 8pt;">
  <span style="font-size:12px;">(Credit for authorship of this article goes to Brendan Bannon, with editing assistance from Cliff Mason and Kyle Baxter) </span>
</p>

<p style="margin:0in 0in 8pt 0.5in">
   
</p>