Topic outline

• Running Fast Queries with SQL in InterSystems IRIS

  Running Fast Queries with SQL in InterSystems IRIS

  Content Type
  Exercise

  Duration
  10m

  Level
  Introductory

  InterSystems IRIS® data platform provides high-performance, full-featured SQL access. You can use SQL with InterSystems IRIS at scale to perform individual queries on a single CPU core, run parallel queries using dozens of cores, and handle distributed queries across a cluster of InterSystems IRIS servers.

  Using SQL features in InterSystems IRIS carries several advantages, including:

  • High performance and scalability
  • Integration with InterSystems object technology
  • Low maintenance
  • Support for standard SQL queries and stored procedures
  • JDBC and ODBC connectivity
  • Automatic parallel query execution
  • Transparently distributed queries

  Learning Objectives

  • Use the Management Portal to view schemas, stored procedures, and test queries.
  • Run SQL queries from the SQL Shell in the InterSystems IRIS Terminal.
  • Leverage implicit joins to simplify query logic.
  • Optimize queries using bitmap indices.
  • Run SQL queries in the same code where you access data as objects.

  
  

  Prerequisites

  • None

  
  
  

  Watch the video and try the exercise below!
• Start the Exercise

  Start the Exercise

  In this exercise, you will leverage several SQL features of InterSystems IRIS to retrieve pet store data from an instance of InterSystems IRIS. You will use both the InterSystems Management Portal and InterSystems Terminal to run SQL queries.

  Click the launcher to provision an InterSystems IRIS container.
  

  Note:

  If you would like to try the exercise locally with an instance of InterSystems IRIS, you will first need to load the sample data from this repository.

  • Launch the virtual machine (VM) below to begin working on the exercise. For responses to common questions on using VM-based Learning Labs, visit FAQ: Using Learning Services Virtual Machines.

    Your login information for the VM is the following:

    • Username: student
    • Password: LearningServices7
  • You must be logged in to launch an InterSystems IRIS instance.

    • Log in with your existing account.
    • Don't have an InterSystems account? Create a new account.
  • In the first two steps of this exercise, you will use two different approaches to run SQL queries. You will first use a built-in UI in InterSystems IRIS, then try the SQL Shell in the InterSystems Terminal.

  • Use the built-in UI

    A built-in UI in the Management Portal allows you to easily see your schemas and stored procedures, view your data, and test your SELECT queries.

    1. Open Google Chrome on your virtual machine and navigate to the IRIS bookmark.
    2. At the Management Portal login screen, log in with username tech and password demo7.
    3. Navigate to System Explorer > SQL.
    4. Make sure you are in the USER namespace. If needed, you can switch using the link at the top:
       
    5. Open Tables and drag and drop Demo.Pet to the Execute Query box. Edit this query to include where color = 'Yellow' and type = 'bird' at the end and execute.
  • Run queries from the SQL Shell

    The Management Portal SQL interface is great for testing queries, but you may often want to run queries directly from the InterSystems IRIS Terminal, using the SQL Shell.

    1. Open a terminal on your virtual machine via the left hand Favorites bar. Then, run the following command to open an InterSystems IRIS Terminal session:

    docker exec -it iris iris session iris

    2. Open the SQL Shell by entering the following command in the InterSystems Terminal:

       DO $SYSTEM.SQL.Shell()

       This will display the following output:
       Begin code:

       SQL Command Line Shell
       ----------------------------------------------
       The command prefix is currently set to: <<nothing>>.
       Enter q to quit, ? for help.
       [SQL]USER>>
       

       End code.
       
       

       To run commands in the SQL Shell, you can use single-line or multiline mode, but the latter may be more convenient.

    3. Press Enter at the prompt to enter multiline mode. You will see confirmation that you are in multiline mode.
    4. Enter the following SQL query line by line. The keyword GO instructs the shell to execute the query and exit multiline mode.
       (view a text version of this code):
       
       

    Notice that the statement you entered is echoed to the SQL Shell, followed by the query results. After the query results, you will see how long it took to prepare and execute the statement:

    Begin code:

    43 Rows(s) Affected
    statement prepare time(s)/globals/cmds/disk: 0.1336s/41,095/262,719/0ms
    execute time(s)/globals/cmds/disk: 0.0096s/6,739/132,517/0ms
    cached query class: %sqlcq.USER.cls11

    End code.

    Within the listing of each step are the following metrics:

    • The time each step took
    • The number of globals: references made to InterSystems IRIS storage to prepare or execute the SQL statement
    • The number of ObjectScript commands executed to prepare or execute the SQL statement

    At the end of the display is the cached query class, the ObjectScript class that caches the code generated when the statement is first prepared.

    For the rest of the steps in this exercise, you can run queries using either the SQL interface in the Management Portal or multiline mode in the SQL Shell.

  • Use implicit joins

    InterSystems IRIS is more than a simple relational database and as a result provides tools to make your queries simpler while more performant.

    1. Run the following query to see an example using an implicit join (->). Remember to add the keyword GO if you are running this command from the SQL Shell. (view a text version of this code):
       
       
       
       

    Can you figure out which city and state has the most yellow birds? While you can use regular joins, implicit joins within InterSystems IRIS can also be used to follow references in place of left outer joins, which is much simpler and ultimately decreases development time. Note that the sample data does not reflect accurate city and state pairings; the data is automatically populated with city names and state abbreviations.

  • Use bitmap indices

    InterSystems IRIS provides many tools to make your queries even faster, including bitmap indices. To ensure you did not start with a slow query in the previous step, bitmap indices were already added to Demo.Pet on color and type. For comparison, you now will run the same query without bitmap indices, noting the execution time—indicated by Performance or execute time.

    1. Run the same query as before, this time using %NOINDEX to run the query ignoring indices. (view a text version of this code):
       
       

    Notice the query performance below the execution; it should show that the query was much slower than the previous. While execution takes less than a second in these examples, you can imagine the impact of bitmap indices on a larger set of data. Note that there may be situations in which %NOINDEX helps improve performance, such as when most data is included by the condition. Additional optimization hints are available for greater control over your queries.

  • Run SQL side-by-side with objects

    In this step, you will run an ObjectScript class method called DisplayAllBirdsByLocation(), which takes a city and state as arguments. This method queries for all yellow birds based on the city and state and applies additional formatting to the date to print out a formatted report.

    1. Note the city and state for the greatest number of yellow birds from the previous query. You will use these values in an upcoming step.
    2. Open InterSystems Terminal. If you are still in the SQL Shell, enter q to exit.
       Since the data was randomly populated using the built-in population tools of InterSystems IRIS, you will need replace the two method input values with the ones you just noted.
    3. Call the DisplayAllBirdsByLocation() method, replacing TopCity and TopState with the values you found above:

       do ##class(Demo.Pet).DisplayAllBirdsByLocation("TopCity","TopState")

    4. Inspect the following ObjectScript code to see SQL and objects work side-by-side, using embedded SQL. Within the method, SQL is used to query the demo.pet table, using implicit joins to get values from petshop, an object referenced in the table. (view a text version of this code):
       
       

    Using objects and SQL side-by-side provides great flexibility, and you can choose the best model for each task. While this example uses ObjectScript, the multi-model nature of InterSystems IRIS also provides this capability within other languages as well such as Java and .NET.

    With InterSystems IRIS, you can run ANSI-standard SQL from any application through standard APIs such as JDBC and ODBC to create a seamless application. To increase your development output, you can also run this code directly within ObjectScript classes, side-by-side with other models, including object-oriented code.

• What's Next?

  What's Next?
  • Try the multi-model capabilities of InterSystems IRIS (exercise, 10m).
  • Use additional features of InterSystems IRIS to optimize your SQL queries (exercise, 15m).
  • Practice using SQL within an InterSystems IRIS application (online course, 30m).
  • See how to connect and use your own SQL client (video, 2m).

  • Share Your Feedback!