no maintenance or update

Prerequisites

Make sure you have git and Docker desktop installed.

Installation

Clone/git pull the repo into any local directory

git clone https://github.com/rcemper/character-slice-index.git

Run the IRIS container with your project:

docker-compose up -d --build

How to Test it

docker-compose exec iris iris session iris

There is a populate procedure that you can run from SQL Shell
The reply is the last ID generated
The default line size is 37 char.
You can change it by passing some different size

USER>do $system.SQL.Shell()
[SQL]USER>>select rcc_slice.pop()
38
[SQL]USER>>select rcc_slice.pop(15)
130
[SQL]USER>>select rcc_slice.pop(50)
158
[SQL]USER>>select documentid,count(*) lines from rcc_slice.data group by documentid
documentid lines
28 28
43 92
62 38

Next we look for lines containing ol
Classic approach:

[SQL]USER>>
SELECT ID, documentid, line, length(line), lineid
FROM rcc_slice.data WHERE line [ ‘ol’
27 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0841s/42,502/225,481/0ms
execute time(s)/globals/cmds/disk: 0.0010s/ 159 /4,444/0ms
cached query class: %sqlcq.USER.cls

Character Slice approach:

[SQL]USER>>
SELECT ID, documentid, line, length(line), lineid
FROM rcc_slice.data WHERE line %CONTAINSTERM (‘ol’)
27 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0847s/42,849/238,674/0ms
execute time(s)/globals/cmds/disk: 0.0011s/ 34 /3,900/0ms
cached query class: %sqlcq.USER.cls8

Even with this small data sample we see only 34 Global Access instead of 159

Now let’s look for 2 independent values ol and w
Classic approach:

[SQL]USER>>
SELECT ID, documentid, line, length(line), lineid
FROM rcc_slice.data WHERE line [ ‘ol’ and line [ ‘w’
4 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0805s/42,670/233,791/0ms
execute time(s)/globals/cmds/disk: 0.0004s/ 159 /2,340/0ms
cached query class: %sqlcq.USER.cls9

Character Slice approach:

[SQL]USER>>
SELECT ID, documentid, line, length(line), lineid
FROM rcc_slice.data WHERE line %CONTAINSTERM (‘ol’,‘w’)
4 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0901s/43,417/265,856/0ms
execute time(s)/globals/cmds/disk: 0.0005s/ 15 / 1,632/0ms
cached query class: %sqlcq.USER.cls11

It’s getting dramatic: less than 10% !
Only 15 instead of 159 Global Access
You are invited to extrapolation of these results

Article in DC

Video

character-slice-index Works