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:
Below are some general resources to learn more about InterSystems IRIS SQL Performance:
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.
- First Look: InterSystems SQL: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlbasics
- First Look: Optimizing SQL Performance with InterSystems Products: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_sqlqueryopt
- InterSystems SQL Optimization Guide: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT
- Resource Guide - Learn Caché SQL: Performance: https://learning.intersystems.com/enrol/index.php?id=255
- Resource Guide – Learn Caché SQL: Design and Execution: https://learning.intersystems.com/enrol/index.php?id=256
- Optimizing SQL Queries: https://learning.intersystems.com/course/view.php?id=707
- Academy: Optimizing SQL Performance: https://learning.intersystems.com/course/view.php?id=80
- Optimizing Your SQL Queries: https://learning.intersystems.com/course/view.php?id=1013
- 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
- Horizontal Scalability with InterSystems IRIS: https://community.intersystems.com/post/horizontal-scalability-intersystems-iris
- Introduction to Outlier Selectivity: https://community.intersystems.com/post/introduction-outlier-selectivity
- Improve SQL Performance for Date Queries.: https://community.intersystems.com/post/improve-sql-performance-date-range-queries
- Improve SQL Performance for Date Queries, AGAIN!: https://community.intersystems.com/post/improve-sql-performance-date-queries-again
- Creating a Custom Index Type in Caché: https://community.intersystems.com/post/creating-custom-index-type-cach%C3%A9
- Globals Are Magic Swords For Managing Data. Part 1: https://community.intersystems.com/post/globals-are-magic-swords-managing-data-part-1
- Globals – Magic swords for storing data. Trees. Part 2: https://community.intersystems.com/post/globals-magic-swords-storing-data-trees-part-2
- Globals – Magic swords for storing data. Sparse arrays. Part 3: https://community.intersystems.com/post/globals-magic-swords-storing-data-sparse-arrays-part-3
-
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 - Indexing of non-atomic attributes: https://community.intersystems.com/post/indexing-non-atomic-attributes
- SQL index for array property elements: https://community.intersystems.com/post/sql-index-array-property-elements
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:
- Introduction To Indexing
- Optimizing SQL Performance (2015)
- %PARALLEL Query Hint (2016)
- Frozen Plans and Parallel Queries (2017)
- Show Plan to Generated COS (2013)
- Programming with SQL (2006)
(Credit for authorship of this article goes to Brendan Bannon, with editing assistance from Cliff Mason and Kyle Baxter)
Top comments (0)