15 minutes
estimated time of completion.
- 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
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 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
- Next, you can explore this data using a built-in UI. Navigate to the Management Portal > System Explorer > SQL.
- Make sure you are in the USER namespace using the link at the top:
- Open
Demo.Pet
to the box. Edit this query to includewhere color = 'Yellow' and type = 'bird'
and execute.
and drag and drop
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. 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
- 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. - 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
%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
- 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.
- Open InterSystems Terminal using these instructions.
- 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")
- Inspect the following ObjectScript code to see SQL and objects work side by side:
With InterSystems IRIS, you can run ANSI-standard SQL from any application through standard APIs such as JDBC and ODBC, creating a seamless application. Or you can run the code directly within ObjectScript classes, side by side with other models including object-oriented code, increasing your development output.