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:
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.
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.
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
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.
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.
%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.
USER >
.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">)
/// 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.
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:
To give you the best possible experience, this site uses cookies and by continuing to use the site you agree that we can save them on your device.