New post

#SQL

14

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

The 2021.2 release of the InterSystems IRIS Data Platform includes many exciting new features for fast, flexible and secure development of your mission-critical applications. Embedded Python definitely takes the limelight (and for good reason!), but in SQL we've also made a massive step forward towards a more adaptive engine that gathers detailed statistical information about your table data and exploits it to deliver the best query plans. In this brief series of articles, we'll take a closer at three elements that are new in 2021.2 and work together towards this goal, starting with Run Time Plan Choice.

It's hard to figure out the right order to talk about these (you can't imagine how often I've reshuffled them in writing this article!) because they fit together in such a nice way. As such, feel free to go on a limb and read these in random order smiley.

13 2
1 687

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

10 0
0 482

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

3 0
1 314
Article
· May 11, 2021 8m read
IRIS in Astronomy

In this article we are going to show the results of the comparision between IRIS and Postgress when handling Astronomy data.

Introduction

Since the earliest days of human civilization we have been fascinated by the sky at night. There are so many stars! Everybody has dreamed about them and fantasized about life in other planets.

10 7
0 699

Hi folks!

Sometimes when we develop a mockup or PoC there is a need for a simple interface that will provide data in IRIS in JSON against SQL queries.

And recently I contributed a simple module that does exactly that:

accepts SQL string and returns the JSON.

How to install? Just call:

zpm "install sql-rest"

If you install it in a namespace X it will setup a /sql endpoint to your system that will accept POST requests with SQL string and will return the result for you for the data available in the namespace X.

Open Exchange app
7 0
2 419
Article
· Feb 2, 2021 12m read
A custom SQL index with Python features

Image search like Google's is a nice feature that wonder me - as almost anything related to image processing.

A few months ago, InterSystems released a preview for Python Embedded. As Python has a lot of libs for deal with image processing, I decided to start my own attemptive to play with a sort of image search - a much more modest version in deed :-)

Open Exchange app
3 0
0 337

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

1 0
0 689
Article
· Feb 5, 2016 11m read
Class Queries in InterSystems IRIS

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

17 17
7 7.2K

Presenter: Anton Umnikov
Task: Identify your slowest SQL queries and tune them for better performance
Approach: Use InterSystems’ query profiling and analysis tools. Discuss how system configuration can affect performance

This session will show you how you identify the weakest link in your application SQL and introduce you to the fine art of tuning those queries. To do this we will take a look at InterSystems query profiling and analysis tools, as well as how system configuration can impact SQL performance.

Problem: Obscurity on how our SQL engine works

Content related to this session, including slides, video and additional learning content can be found here.

0 2
0 354

Keywords: IRIS, IntegratedML, Machine Learning, Covid-19, Kaggle

Purpose

Recently I noticed a Kaggle dataset for the prediction of whether a Covid-19 patient will be admitted to ICU. It is a spreadsheet of 1925 encounter records of 231 columns of vital signs and observations, with the last column of "ICU" being 1 for Yes or 0 for No. The task is to predict whether a patient will be admitted to ICU based on known data.

Open Exchange app
2 1
1 818

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

4 2
0 440

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

Open Exchange app
2 0
0 798

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

9 11
1 10.7K

Introduction

In the first article in this series, we’ll take a look at the entity–attribute–value (EAV) model in relational databases to see how it’s used and what it’s good for. Then we'll compare the EAV model concepts to globals.

Open Exchange app
3 0
4 3.8K

InterSystems IRIS 2020.1 brings a broad set of improved and new capabilities to help build important applications. In addition to the many significant performance improvements accrued through 2019.1 and 2020.1, we are introducing one of our biggest changes in recent SQL history: the Universal Query Cache. This article provides more context on its impact to SQL-based applications at a technical level.

14 0
0 830
Article
· Mar 2, 2020 2m read
SQL -99 error while viewing a listing

This error is sometimes seen while viewing a listing in InterSystems IRIS Business Intelligence:
ERROR #5540: SQLCODE: -99 Message: User <USERNAME> is not privileged for the operation (4)

As the error suggests, this is due to a permission error. To figure out which permissions are missing/needed, we can take a look at the SQL query that is generated. We will use a query from SAMPLES as an example.

1 0
0 947

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

6 0
1 594

Our team is reworking an application to use REST services that use the same database as our current ZEN application. One of the new REST endpoints uses a query that ran very slowly when first implemented. After some analysis, we found that an index on one of the fields in the table greatly improved performance (a query that took 35 seconds was now taking a fraction of a second).

3 4
0 450
Article
· Jul 18, 2017 2m read
Old/New Dynamic SQL Cheat Sheet

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

8 35
4 2.3K
Article
· Jul 26, 2019 3m read
Dynamic SQL to Dynamic Object

Hello community! I have to work with queries using all kinds of methods like embedded sql and class queries. But my favorite is dynamic sql, simply because of how easy it is to manipulate them at runtime. The downside to writing a lot of these is the maintenance of the code and interacting with the output in a meaningful way.

Open Exchange app
7 7
1 1K

Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!

As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.

22 11
2 2.6K