SQL QuickStart

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

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

1. 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. Make sure you have an instance of InterSystems IRIS ready to go.
   

   Need InterSystems IRIS?

   Get a free, online development sandbox here. Log in with your InterSystems universal account, or register for one below.

   Login Register
2. Next, explore this some using the built-in SQL UI. Open the Management Portal in your web browser -- cannot display value - please provision a sandbox.
3. Click on System Explorer (near the bottom left), then SQL.
4. Make sure you are in the USER namespace 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' and click 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

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.

1. Run the following query To see an example using an implicit join (->), paste the following query into the Execute Query box and click Execute:

   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

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.

1. 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 the InterSystems Web Terminal, by opening your sandbox IDE -- cannot display value - please provision a sandbox, and in the top menubar, choosing InterSystems -> Web Terminal. A new browser window should appear with the prompt: USER >.
3. Now we’re going to use a little ObjectScript, the built-in language for rapid development. 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:

   /// This method queries for all birds by location and return 
   ClassMethod DisplayAllBirdsByLocation(city As %String, state As %String)
   {
   	// Use cursor-based embedded SQL to retrieve data
   	// -> is an implicit join and follows object references to make left joins easier
   	&sql(DECLARE c1 CURSOR FOR SELECT name, petshop->owner, petshop->phone
   	INTO :name, :owner, :phone
   	FROM demo.pet
   	WHERE type = 'bird' and color = 'Yellow' and petshop->location->city = :city and
   	petshop->location->state = :state)
   
   	&sql(OPEN c1)
   
   	//Fetch each row and display it using custom formatting. ?15 allots 15 characters
   	// padded with the appropriate number of spaces. ! adds a new line.
   	write "Name", ?15, "Owner", ?20, "Phone", !
   	&sql(FETCH c1)
   	while (SQLCODE = 0) {
   		write name, ?15, owner, ?20, phone, !
   		&sql(FETCH c1)
   	}
   	&sql(CLOSE c1)
   }
   

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 with sample stock data. If you will be using one of the cloud providers — AWS, Azure, or GCP — you can install the stock data using the following command: