Topic outline

• Optimizing SQL Queries in InterSystems IRIS

  Optimizing SQL Queries in InterSystems IRIS

  Content Type
  Exercise

  Duration
  15m

  Level
  Intermediate

  ​See how to optimize SQL queries in InterSystems IRIS® data platform by using query analysis tools for query plans, adding indexing methods, and reviewing your query’s performance. You will use various tools as you query a million-row table containing sample data about stock transactions to determine the average price for all SELL transactions in the table.

  Learning Objectives

  • Display and analyze query plans.
  • Add and test the effects of bitslice and bitmap indices.
  • Review SQL query’s runtime statistics over time.

  Prerequisites

  • Running Fast Queries with SQL in InterSystems IRIS (exercise, 10m)
  • Basic experience using SQL in InterSystems IRIS
• Start the Exercise

  Start the Exercise

  You must be logged in to view this content.

  Don't have an InterSystems account? Create a new account.
• Start the Exercise

  Start the Exercise

  To start, you will run the Tune Table utility, which examines the data in a table and creates the statistics the InterSystems SQL query optimizer uses. After running this utility, you will be able to see the initial plan created by the InterSystems SQL query optimizer and begin working to optimize your query.

  • Launch Your Lab

    Use the online lab provided to complete this exercise.

  • Launch your lab

    Not available unless: Your ID number is not empty
  • Show the query plan

    In this task, you will use two methods to display the query plan and determine how to improve query as you find the average price for all SELL transactions. Querying a table with a million rows could potentially be very slow, and you will see how a query plan helps prepare you to optimize performance. By looking at the query plan, you can determine the most effective place to add indices and improve the execution time. A query plan shows how the SQL query optimizer will use indices, or whether it will read the table data directly without indices to execute statements.

    First, ensure the SQL query optimizer has up-to-date information about the contents of the table by runing the Tune Table utility.

    1. Open the Terminal using the icon on the desktop, then run iris session iris to open the InterSystems IRIS shell.
    2. Log in using the credentials tech and demo.
    3. Run the Tune Table utility on the FirstLook.StockTableDemoTwo table with the following command:

       DO $SYSTEM.SQL.TuneTable("FirstLook.StockTableDemoTwo")

       In the SQL Shell, you can show a query plan by prepending the keyword EXPLAIN to a query.
    
    
    4. In the Terminal, open the SQL Shell by running the following command: DO $SYSTEM.SQL.Shell()
    5. Execute the following EXPLAINstatement in the SQL Shell to show a query plan:Begin code:

       EXPLAIN SELECT AVG(Price) As AveragePrice FROM FirstLook.StockTableDemoTwo WHERE TransactionType = 'SELL'
       

       End code.
       This command will return the following query plan, formatted as XML. Note that there are serious potential performance issues in this query plan; in a later step, we will look at these issues more closely. Begin code:

       Plan |
       | -- |
       | "<plans>
        <plan>
        <sql>
         SELECT AVG ( Price ) AS AveragePrice FROM FirstLook . StockTableDemoTwo WHERE TransactionType = ? /*#OPTIONS {""DynamicSQL"":1} */ /*#OPTIONS {""DynamicSQLTypeList"":""1""} */
        </sql>
        <cost value=""9244148""/>
        Call module F.
        Output the row.
        <module name=""F"" top=""1"">
        Divide master map FirstLook.StockTableDemoTwo.IDKEY into subranges of IDs.
        Call module A in parallel on each subrange, piping results into temp-file A.
        Read temp-file A, looping on a counter.
        For each row:
            Accumulate the count([value]).
            Accumulate the sum([value]).
        </module>
        <module name=""A"" top=""1"">
        Call module B.
        Add a row to temp-file A, subscripted by a counter, with node data of count([value]) and sum([value]).
        </module>
        <module name=""B"" top=""1"">
        Read master map FirstLook.StockTableDemoTwo.IDKEY, looping on the subrange of ID.
        For each row:
            Test the = condition on %SQLUPPER(TransactionType) and the NOT NULL condition on %SQLUPPER(TransactionType).
            Accumulate the count([value]).
            Accumulate the sum([value]).
        </module>
        </plan>
       </plans>"
       

       End code.
       
       A query plan that has been generated to execute a SQL query can be divided into modules, each of which performs a distinct action, such as evaluating a subquery. The query plan that you have now generated is divided into two separate plans:
       • The top plan is for the initial query. It calls a module F, in which the master map is partitioned and module A is executed in parallel over each partition.
       • Module A calls module B and adds the results to a temp-file. Module B reads through each row of the table, counting the number of rows and calculating the sum of the Price field.
       
       In the next step, you will see another method to show a query plan.
    
    
    6. Use the SQL query interface in the Management Portal to show a query plan:
       1. Open the Management Portal using the icon on the desktop. Log in using the same credentials as above.
       2. Make sure you are in the USER namespace.
       3. Navigate to System Explorer > SQL.
       4. Paste the following query into the text field in the Execute Query tab: Begin code.

          SELECT AVG(Price) As AveragePrice FROM FirstLook.StockTableDemoTwo WHERE TransactionType = 'SELL'

          End code.
       5. Click Show Plan to display a query plan.
       
       The results will contain the same information as the XML query plan, presented in a graphical display.
       
       
       
       
       Looking at the query plan results, you can see some potentially serious performance problems with this query. In the plan for module B, the first task is to read the master map and loop on the ID. This means that the InterSystems SQL query optimizer will loop over each of the IDs in the table, rather than using any indices to run the query. Especially in the case of a large table, this indicates a query that will not perform well.
       As you optimize your query, you will see its execution time decrease, and the query plan will change significantly as well. Let’s first test the query without indices so you can compare the initial execution time to the execution time after optimization.
    
    
    7. Run the unoptimized query in the SQL Shell to see how it performs:Begin code:

       SELECT AVG(Price) As AveragePrice FROM FirstLook.StockTableDemoTwo WHERE TransactionType = 'SELL'
       

       End code. Be sure to note the current execution time and number of globals your query reads so you can compare the query’s performance after optimization.
    
    
    The output will return the results of the query, as well as its preparation and execution time. Note that the times shown here may differ from what you see. Begin code:
      AveragePrice
    266.1595139195757844
     
    1 Rows(s) Affected
    statement prepare time(s)/globals/cmds/disk: 0.0805s/37,550/215,711/0ms
              execute time(s)/globals/cmds/disk: 1.9268s/6,556/8,002,234/0ms
                                    query class: %sqlcq.USER.cls2
    
    
    End code. As you can see, the statement preparation and execution metrics are listed separately. While the current execution time does not seem like a very long time, it can be vastly improved with the use of indices. For example, the number of globals read in the execution step was 6,556. To improve query performance, we will try to decrease this number.

    
    

    As you saw in the output, the preparation time is recorded, as well as the execution time. Preparation is done the first time a query is planned, and then automatically redone if a relevant table is modified or if an index is added or removed. However, since this is rare, most applications prepare a query only once and execute it many times, so the focus of this exercise is on tuning execution performance.

  • Task 1 Complete

  • View query performance

    In this task, you will run the query a few more times and navigate to SQL Statements. Note that the execution time will vary slightly each time you run a query.

    1. Run the query a few more times from the SQL Shell to prepare for viewing the average execution time.
       To track down slow-running queries or see how a new query is doing in a production, you can use the SQL Statements view in the Management Portal.
    
    
    2. Navigate to this view by opening the SQL query interface in the Management Portal and clicking SQL Statements.
       Note: It may take a few minutes for the information shown to be updated.
       
       
       This view lists the SQL statements that have been executed along with information about each statement, including the Count, or number of times the statement was run, as well as the Total time and Average time spent executing this query. The numbers you see may differ from this example, depending on the number of times you ran the query and the execution time on your system.
       
       If you are having trouble identifying the query, click on the SQL Statement Text within a row to see the full SQL query and query plan.

    Be sure to note the Total time and Average time for this query. You will return to this view after optimizing the query to see the impact of the optimizations on these values.

  • Task 2 Complete

  • Add initial optimizations

    If your query will include aggregate functions on one or more fields, adding a bitslice index to one or more of those fields may improve performance. A bitslice index is an array which first converts each numeric data value in a field to a binary bit string. A bitmap is then created for each digit in the binary value to record rows that have a 1 for that binary digit in their bit string representation. This type of index works well for numeric fields where each row likely contains a unique number.

    In this task, you will add a bitslice index to the Price field and test it to get the average price for all SELL transactions.

    1. Create the bitslice index PriceIdx on the Price field by executing the following statement in the SQL Shell: Begin code:

       CREATE BITSLICE INDEX PriceIdx ON TABLE FirstLook.StockTableDemoTwo (Price)

       End code. You will see the statement echoed to the SQL shell, followed by preparation and execution metrics. Begin code:

       9.      CREATE BITSLICE INDEX PriceIdx ON TABLE FirstLook.StockTableDemoTwo (Price)
        
       0 Rows Affected
       statement prepare time(s)/globals/cmds/disk: 0.0091s/2000/13151/0ms
                 execute time(s)/globals/cmds/disk: 1.4268s/2087789/55765062/1ms
                                 cached query class: %sqlcq.USER.cls7

       End code.Even if an index is available, the InterSystems SQL query optimizer will not necessarily use it if it does not improve the SQL query performance.
       
       Next, let's see if the new bitslice index makes a difference in how the query is executed.
    
    
    2. Show the query plan by running the following statement in the SQL Shell: Begin code:

       EXPLAIN SELECT AVG(Price) As AveragePrice FROM FirstLook.StockTableDemoTwo 
            WHERE TransactionType = 'SELL'
       

       End code. As you will see, the query plan remains the same as before. The relative cost is still 9,244,148. The InterSystems SQL query optimizer will not use the new index.
       
       
       
       
       You can edit queries to make sure the SQL query optimizer uses the new index.
    
    
    3. Remove the WHERE clause from the query and show the query plan again. Begin code:

       SELECT AVG(Price) As AveragePrice FROM FirstLook.StockTableDemoTwo 
       

       End code. In this case, the bitslice index is read as the first step of the query plan. The master map is no longer read in this plan.
       
       
       

    From this query, you can see that the InterSystems SQL query optimizer will use the bitslice index in some cases, such as this simpler query, and that doing so greatly decreases the relative cost, shown in the query plan. Next, we need to find a way to get the query optimizer to use the index even when the WHERE clause is present.

    Often, the query optimizer will use a bitslice index when there is also a bitmap index on the field in the WHERE clause. Without a WHERE clause, a query can simply aggregate all the data in the bitslice index. When there is a WHERE clause, the query must not include the bits of the index for rows that do not satisfy the WHERE condition, which can only be done efficiently in tandem with a bitmap index.

  • Task 3 Complete

  • Update query optimizations

    In this task, you will add a bitmap index on the other field in the query, TransactionType, which will allow the query optimizer to use the bitslice index with the WHERE clause. TransactionType is a good candidate for a bitmap index because its count of possible values is two (SELL and BUY).

    1. Add a bitmap index to the TransactionType field by executing the following statement in the SQL Shell:Begin code:

       CREATE BITMAP INDEX TransactionTypeIdx ON TABLE FirstLook.StockTableDemoTwo
               (TransactionType)

       End code.
    2. Show the query plan for the following query using either the SQL Shell or the SQL Explorer in the Management Portal. Begin code:

       SELECT AVG(Price) as AveragePrice FROM FirstLook.StockTableDemoTwo
            WHERE TransactionType = 'SELL'

       End code. You will see that the query optimizer uses the two indices you created to obtain the best performance.
       The relative cost of 157703 is a small fraction of the unoptimized query, whose cost was 9244148.
       
       
       
    
    
    3. Run the query in the SQL Shell. Note the number of globals used and the execution time.
       The number of globals used is 594, as opposed to 1000001, which it was the first time you ran the query. You will also see that the indexed query ran many times faster than the unindexed query. The decrease in time and number of globals used shows that the indexed query runs much more efficiently than the original, unoptimized query.
  • Task 4 Complete

  • View query performance over time

    In this task, you will view the impact of optimization over time.

    1. Return to the SQL Statements view in the SQL Explorer. Note the total and average times for the query. How do these compare to the values you noted when you ran the unoptimized query?
       When comparing the current values for the Total time and Average time columns to the previous values that we saw before optimization, you can expect to see improvements.
  • Task 5 Complete

• What's Next?

  What's Next?

  In this exercise, you used the example of calculating the average SELL price stocks in a table with one million entries to see how to optimize SQL queries. This involved viewing a query plan, adding bitmap and bitslice indices, and reviewing the query performance before and after making optimizations.

  With this knowledge, you can create efficient SQL queries in your own application, and review the query performance over time.

  Continue learning with the following resources:

  • View the InterSystems SQL Optimization Guide for more tips on optimizing your queries (documentation, 9m).
  • See how to search unstructured text data with SQL Search (exercise, 15m).

  • Share Your Feedback!