Article
· Jul 8, 2023 2m read
Character-Slice Index

A recent question from @Vivian Lee reminded me of a rather ancient example.
It was the time when DeepSee's first version was released.
We got Bitmap Index.
And we got BitSlice Index: mapping a numeric value by its binary parts.
So my idea: Why not indexing strings by their characters?
The result of this idea was presented first in June 2008.
IKnow wasn't publicly available at that time.

2 1
1 174

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

11 3
5 401

Hello everyone,

My team is currently developing guidance and best practices for the generation, storage, and deployment of TUNE TABLE statistics across development and production environments. With that in mind, we want to get an idea of what methods teams in the field have developed for handling this data. In particular, we’d like to know the following:

1 2
0 155

Hello IRIS Community,

InterSystems Certification is developing a certification exam for InterSystems IRIS SQL specialists, and if you match the exam candidate description given below, we would like you to beta test the exam. The exam will be available for beta testing on June 9 - 12, 2024 at InterSystems Global Summit 2024, but only for Summit registrants (visit this page to learn more about Certification at GS24). Beta testing will open for all other interested beta testers on June 24, 2024. However, interested beta testers should sign up now by emailing certification@intersystems.com (please let us know if you will be beta testing at Global Summit or in our online proctored environment). The beta testing must be completed by August 2, 2024.

10 5
8 1.3K

InterSystems FAQ rubric

For volatile tables (tables with many INSERTs and DELETEs), storage for bitmap indexes can become inefficient over time.

For example, suppose that there are thousands of data with the following definition, and the operation of bulk deletion with TRUNCATE TABLE after being retained for a certain period of time is repeatedly performed.

3 0
0 266

An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community.

As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be:

7 8
0 1K

Hi!

I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.

It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.

The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.

I'm using Cache 2018.1.

Thanks,

José

0 8
0 308
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 :-)

3 0
0 349

I'm using Cache SQL and want the ability to choose a specific index.

I've boiled the problem down to one table and simplified the query down to

SELECT *
FROM Registration.PatResp
WHERE SchedApptNum=8450022

SchedApptNum is indexed, but instead of using that column, "Show Plan" indicates that it's looping through the entire Registration.PatResp table on Id (the primary key for the table).

I've done a tune-table with no change.

0 6
0 1.1K
Question
· Dec 26, 2019
Performant index on date field

Is there a way to get a good performing index on a date field? I have tried various date property indexes and the query plan is always in a pretty high range. Below are query plan result values I have observed:

StartDate > '2019-12-01' --cost = 699168
StartDate = '2019-12-21' --cost 70666
StartDate between '2019-12-21' and '2019-21-28' --cost = 492058

The query plans above were for type %TimeStamp.

0 7
0 529

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 458

Hello, community!

I've stumbled on some unexpected behavior, and decided to check with you if this is normal. Basically, I'm rebuilding indices and the result is not journaling (which leads to missing indices at shadow server).

The $ZV is "Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2015.2.1 (Build 705U) Mon Aug 31 2015 16:53:38 EDT"

I have an example class

Class tmp.A As %Persistent;

Index IP1 On P1;

Property P1 As %String;

for example there is one object which have P1 = 1, so

0 1
0 298

In the previous parts (1, 2) we talked about globals as trees. In this article, we will look at them as sparse arrays.

A sparse array - is a type of array where most values assume an identical value.

In practice, you will often see sparse arrays so huge that there is no point in occupying memory with identical elements. Therefore, it makes sense to organize sparse arrays in such a way that memory is not wasted on storing duplicate values.

In some programming languages, sparse arrays are part of the language - for example, in J, MATLAB. In other languages, there are special libraries that let you use them. For C++, those would be Eigen and the like.

Globals are good candidates for implementing sparse arrays for the following reasons:

8 3
1 1.4K

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
Question
· Mar 29, 2019
Ensemble as a Data lake

We have been storing raw messages in a MySQL database for DR and ad hoc purposes. We are thinking of using an Ensemble instance as our data lake instead. We could segregate the source data by namespace or by global. But either way we'll want a custom global to index the data for data retrieval performance purposes.

Anyone else taking this approach? Any feedback?

0 2
0 482
Question
· Mar 5, 2019
PrimaryKey vs Idkey

Just wondering an Insight in the difference between these two indexes

IdKey / PrimaryKey
=================

Property Identifier As %Integer

Index Index1 on Identifier [Idkey]

Index Index2 on Identifier [PrimaryKey]

What's the difference?

1. If I don't have Index1 and only have Index2, then cache does still make its own id.
So how and why do I ever use the PrimaryKey. In Joins ??

1 4
0 1.1K

Hi,

I know of the existance of (ELEMENTS) to create an index from a list, but I actually would like to index the content of an element of a list. Is it possible?

My scenario:

Class:
Property Test As list of TestList;

Test.List:
Property Name As %String;
Property Surname As %String;

I would like to have an index based on the TestList.Name. If I try using

Index NewIndex On Test(ELEMENTS)

it will create an index with Name and Surname in it, but I just want to have an index with the name. Is it possible?

0 3
1 312

I have a persistent class that represents cities across the United States. It is below, but basically has a City Id, Name, Lat, Lon and a few other unimportant fields for this issue. Anytime I attempt to query on the Latitude or Longitude it immediately returns no results. My first thought was that it was a casting issue so I tried casting both sides to floats, ints, even strings and in all cases it immediately comes back with no results. I then decided to cast it to a string and attempt a like statement thinking it might be something about how floats are handled, but still no joy. Any

0 2
0 378
Question
· Apr 15, 2018
Indexes in Cache Objects

Hi-

I have the following objects

Class A

Property P1 As B

Property P2 As %String

Property P3 As %String

Class B

Property P1 As %String

Can I create an index in Class A based on P1.P1. Basically I want an index of class A by property P1 in class B

I tried creating the following but got a compile error

Index I1 On P1.P1

Thanks

0 2
0 511