Speed up SQL query for Informix?

Question

On Informix, is it possible speed up this query?

SELECT FIRST 1 HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1  as dec,
           HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1) as hex,
   SUBSTR((HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1))::char(10), 6) as str
FROM informix.coordman as l
WHERE (SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU')
AND
NOT EXISTS (SELECT r.cm_code FROM informix.coordman r
            WHERE (SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') AND
                  (SUBSTR(NVL(r.cm_code, ' '), 1, 2) = 'MU') AND
                  (HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1 =
                   HEX('0x'||SUBSTR(LPAD(NVL(r.cm_code, '0'), 7, '0') , 3))::INT) )
ORDER BY 1 ASC;

I have already tried a version with LEFT OUTER JOIN,

SELECT FIRST 1 HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1 as dec,
HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1) as hex,
SUBSTR((HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1))::char(10), 6) as str,
r.cm_code
FROM informix.coordman as l
LEFT OUTER JOIN informix.coordman r ON
(SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') AND
(SUBSTR(NVL(r.cm_code, ' '), 1, 2) = 'MU') AND
(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1 =
HEX('0x'||SUBSTR(LPAD(NVL(r.cm_code, '0'), 7, '0') , 3))::INT)
WHERE (SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') and
r.cm_code IS NULL
ORDER BY 1 ASC;

but I have worsened the speed.

Update The purpose of the queries is to find holes in a sequence like the one shown below:

cm_code
MU00001
MU00001
MU00002
MU00002
...
MU0000B
MU0000B
MU0000D
MU0000D
...
MU00010
MU00010
MU00011

the type of the cm_code field is:

Column name          Type                                    Nulls
cm_code              char(8)                                 yes

In order to speed up, the previously reported query has been replaced by:

SELECT FIRST 1 HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1 as dec, 
HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1) as hex, 
SUBSTR((HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1))::char(10), 6) as str 
FROM 
informix.coordman as l WHERE 
(SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') 
ORDER BY 1 ASC

Now I need to speed up the query below :

SELECT FIRST 1 HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT + 1 as dec, 
HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT + 1) as hex, 
SUBSTR((HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT + 1))::char(10), 6) as str 
FROM 
informix.coordman as l WHERE 
(SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') AND 
NOT EXISTS (SELECT r.cm_code FROM informix.coordman r WHERE 
(SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') AND 
(SUBSTR(NVL(r.cm_code, ' '), 1, 2) = 'MU') AND 
(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT +1 = HEX('0x'||SUBSTR(LPAD(NVL(r.cm_code, '0'), 7, '0') , 3))::INT)) 
ORDER BY 1 ASC

Answer 1

I've produced two versions of your query, one that requires Informix 14.10 and one that does not. I've benchmarked them against your query, with striking results. I'm using a table with 360 rows in it, generated at random. For one set of data, this lead to 85 rows where the number appeared once, 67 that appeared twice, 26 that appeared thrice, 8 that appeared four times, 5 that appeared five times, and 1 that appeared six times.

Results:

Q0      0.7212 seconds — as in question
Q1      0.0044 seconds — using CTE
Q2      0.0027 seconds — using an explicit temporary table
                        (add 0.0006 seconds if the temp table is dropped explicitly)

I'm not sure about why there is so much difference between Q1 and Q2, but both are negligible by comparison with Q0.

This SQL script, showing what I executed, demands my SQLCMD program, available from the IIUG (International Informix Users Group. It also uses a home-brew random data generator.

!random -n 360 -F "MU%5X" 1 255 > "so-5575-9624.unl"

DROP TABLE IF EXISTS coordman;
CREATE TABLE coordman
(
    cm_code CHAR(8) NOT NULL
);

RELOAD FROM "so-5575-9624.unl" INSERT INTO coordman;
CREATE INDEX i_cm_code ON coordman(cm_code);

types on;
headings on;
trace on;

--SELECT {FIRST 1}   HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1  AS DEC,
SELECT DISTINCT    HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1  AS DEC,
               HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1) AS HEX,
       SUBSTR((HEX(HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1))::CHAR(10), 6) AS str
  FROM coordman AS l
 WHERE (SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU')
   AND NOT EXISTS
       (SELECT r.cm_code FROM coordman AS r
         WHERE (SUBSTR(NVL(l.cm_code, ' '), 1, 2) = 'MU') AND
               (SUBSTR(NVL(r.cm_code, ' '), 1, 2) = 'MU') AND
               (HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT - 1 =
                HEX('0x'||SUBSTR(LPAD(NVL(r.cm_code, '0'), 7, '0') , 3))::INT)
       )
ORDER BY 1 ASC;

WITH cm_numbers AS
     (SELECT DISTINCT cm_code,
            --('0x'||cm_code[3,8])               AS hex_string,
            --('0x'||cm_code[3,8])::INT          AS dec_value,
            --('0x'||SUBSTR(cm_code, 3, 6))      AS hex_substr,
            ('0x'||SUBSTR(cm_code, 3, 6))::INT AS dec_number
       FROM coordman AS l
      WHERE cm_code[1,2] = "MU"
     )
SELECT l.dec_number - 1 AS missing_number, l.cm_code
  FROM cm_numbers AS l
  LEFT JOIN cm_numbers AS r
    ON l.dec_number - 1 = r.dec_number
 WHERE r.dec_number IS NULL
 ORDER BY missing_number;

DROP TABLE IF EXISTS cm_numbers;

SELECT DISTINCT cm_code, ('0x'||SUBSTR(cm_code, 3, 6))::INT AS dec_number
  FROM coordman AS l
 WHERE cm_code[1,2] = "MU"
  INTO TEMP cm_numbers;

SELECT l.dec_number - 1 AS missing_number, l.cm_code
  FROM cm_numbers AS l
  LEFT JOIN cm_numbers AS r
    ON l.dec_number - 1 = r.dec_number
 WHERE r.dec_number IS NULL
 ORDER BY missing_number;

DROP TABLE IF EXISTS cm_numbers;

The primary difference is the sheer number of functions called. Also, as noted in the comments, you protect against nullness with NVL, but you don't make use of what it does return.