SQL Performance Resources

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.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

1. The types of indices available
2. Using one index type over another
3. The information TuneTable gathers for a table and what it means to the Optimizer
4. How to read a Show Plan to better understand if a query is good or bad

Below are some general resources to learn more about InterSystems IRIS SQL Performance:

1. The Worldwide Response Center (WRC) at 617-621-0700
   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.
    
2. InterSystems online documentation
   1. First Look:  InterSystems SQL:  https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlbasics
   2. First Look: Optimizing SQL Performance with InterSystems Products:  https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlqueryopt
   3. InterSystems SQL Optimization Guide:  https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT
       
3. InterSystems eLearning
   1. Resource Guide - Learn Caché SQL: Performance: https://learning.intersystems.com/enrol/index.php?id=255
   2. Resource Guide – Learn Caché SQL: Design and Execution: https://learning.intersystems.com/enrol/index.php?id=256
   3. Optimizing SQL Queries:  https://learning.intersystems.com/course/view.php?id=707
   4. Academy:  Optimizing SQL Performance:  https://learning.intersystems.com/course/view.php?id=80
   5. Optimizing Your SQL Queries:  https://learning.intersystems.com/course/view.php?id=1013
       
4. InterSystems Developers Community
   1. The One Query Performance Trick You NEED to Know? Tune Table! https://community.intersystems.com/post/one-query-performance-trick-you-need-know-tune-table
   2. Horizontal Scalability with InterSystems IRIS:  https://community.intersystems.com/post/horizontal-scalability-intersystems-iris
   3. Introduction to Outlier Selectivity:  https://community.intersystems.com/post/introduction-outlier-selectivity
   4. Improve SQL Performance for Date Queries.:  https://community.intersystems.com/post/improve-sql-performance-date-range-queries
   5. Improve SQL Performance for Date Queries, AGAIN!:  https://community.intersystems.com/post/improve-sql-performance-date-queries-again
   6. Creating a Custom Index Type in Caché:  https://community.intersystems.com/post/creating-custom-index-type-cach%C3%A9
   7. Globals Are Magic Swords For Managing Data.  Part 1:  https://community.intersystems.com/post/globals-are-magic-swords-managing-data-part-1
   8. Globals – Magic swords for storing data. Trees.  Part 2:  https://community.intersystems.com/post/globals-magic-swords-storing-data-trees-part-2
   9. Globals – Magic swords for storing data. Sparse arrays.  Part 3:  https://community.intersystems.com/post/globals-magic-swords-storing-data-sparse-arrays-part-3
   10. Free Text Search:  The Way To Search Your Text Fields That SQL Developers Are Hiding From You!*: 
       https://community.intersystems.com/post/free-text-search-way-search-your-text-fields-sql-developers-are-hiding-you
   11. Indexing of non-atomic attributes:  https://community.intersystems.com/post/indexing-non-atomic-attributes
   12. SQL index for array property elements:  https://community.intersystems.com/post/sql-index-array-property-elements
        
5. PowerPoints from prior years

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:

1. 1. Introduction To Indexing
   2. Optimizing SQL Performance (2015)
   3. %PARALLEL Query Hint (2016)
   4. Frozen Plans and Parallel Queries (2017)
   5. Show Plan to Generated COS (2013)
   6. Programming with SQL (2006)

(Credit for authorship of this article goes to Brendan Bannon, with editing assistance from Cliff Mason and Kyle Baxter)