InterSystems IRIS™ data platform provides high-performance, full-featured SQL access. You can use SQL at scales from queries running on a single CPU core, to parallel queries using dozens of cores, to distributed queries across a cluster of InterSystems IRIS servers.

SQL features available in InterSystems IRIS include:

• 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

For this activity, you will use the InterSystems Management Portal to run SQL queries, leveraging several SQL features of InterSystems IRIS to retrieve petstore data from an instance of the database.

Note

If you don’t have InterSystems IRIS set up yet, get a free development sandbox here.

Load pet store data

If you are using Learning Labs Sandbox, the sample pet store data is already loaded. Skip to the next step.

Otherwise, clone or download this repository: https://github.com/intersystems/Samples-Petstore-Data. A folder named data contains all files needed. In the Management Portal, navigate to System Explorer > Classes and import DemoAddressCls.xml, DemoPetCls.xml, DemoPetShopCls.xml and PetUtilCls.xml files into the USER namespace from the data folder.

Access the InterSystems IRIS Terminal through the InterSystems IRIS launcher menu on Windows or using the iris terminal command in the operating system command line. Type the following command:

do ##class(Demo.PetUtil).PopulateData()

Note

To launch the IRIS Terminal from the Learning Labs Sandbox, select InterSystems IRIS Terminal from the InterSystems menu. If you have InterSystems IRIS installed another way, use the instructions here.

Use built-in pages to easily run SQL and manage your data

1. Next, you can explore this data using a built-in UI. Navigate to the Management Portal > System Explorer > SQL.
2. Make sure you are in the USER namespace using the link at the top:
3. Open Tables and drag and drop Demo.Pet to the Execute Query box. Edit this query to include where color = 'Yellow' and type = 'bird' and execute.

A UI is provided to easily view your schemas and stored procedures, and to view data and test your queries.

Use implicit joins to leverage object references and accelerate development time

1. InterSystems IRIS is more than a simple relational database and, as a result, provides tools to make your queries simpler while at the same time more performant. Run the following query to see an example using an implicit join (->):

   SELECT petshop->location->city, petshop->location->state, count(*) as NumberYellow
   FROM demo.pet 
   WHERE color = 'yellow' and type = 'bird'
   GROUP BY petshop->location->city, petshop->location->state
   ORDER BY NumberYellow desc

2. 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 joins, greatly simplifying your joins and ultimately decreasing development time.

Use bitmap indices and other built-in tools to improve performance

1. With InterSystems IRIS, you have many tools available to make your queries even faster, including bitmap indices. To ensure you did not start with a slow query, bitmap indices have already been added to Demo.Pet on color and type to make the previous query fast. To be able to compare this fast query to the next query you will run without bitmap indices, note the execution time as indicated by Performance, below the execution box.
2. Next, run the same query as before, this time using %NOINDEX to run the query ignoring indices:

   SELECT petshop->location->city, petshop->location->state, count(*) as NumberYellow
   FROM demo.pet 
   WHERE %NOINDEX color = 'yellow' and %NOINDEX type = 'bird'
   GROUP BY petshop->location->city, petshop->location->state
   ORDER BY NumberYellow desc

Notice the query performance below the execution. It should be quite a bit slower than the previous query. While execution takes less than a second in these cases, you can imagine the impact on a larger set of data. It should be noted that there may be situations where you want to use %NOINDEX to improve performance such as if almost all data is included by the condition. Additional optimization hints are available should you need greater control over your queries.

Run SQL side by side with objects, using the model that works best for each task

1. Note the city and state for the greatest number of yellow birds from the previous query. Be aware this is populated data and is not real. You will use these values in an upcoming step.
2. Open InterSystems Terminal using these instructions.
3. A method has been written 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. Since the data was randomly populated using the built-in population tools of InterSystems IRIS, call this 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 objects and SQL side by side provides great flexibility, so you can choose the best model for each task. While this example uses ObjectScript, the multi-model nature of InterSystems IRIS provides this capability within other languages as well, such as Java and .NET.

Use SQuirreL SQL

While built-in tools are available to view and manage your data, you can easily connect and use your own SQL client with InterSystems IRIS as well.

In the following video, you will see how to connect the SQuirreL SQL Client, one example of a third-party SQL client, to InterSystems IRIS. In order to follow along with this video, you need to have SQuirreL installed with JDK1.8. You also need an instance of InterSystems IRIS preloaded sample stock data. Still need an instance? Check out your options here. If you will be using one of the cloud providers — AWS, Azure, or GCP — you can install the stock data using the following command: