Informix

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

I tried this on a 14.10.FC3 system (AIX) with over 644,000 rows in systabnames, and 232 objectsin the chosen space.  With the query that scans systabnames, it takes just over 20 seconds to complete.  With the query that uses the suggested clause, it returns in a fraction of a second.

When using a different space with 17,000 objects, the original query takes 21 seconds, and the modified query takes 5 seconds.

A space with 340,000 objects, the original query takes 23 seconds, and the modified query takes 5 seconds.

So, the suggestion does result in a significant performance improvement in this environment because of the indexed read instead of the scan.   I haven't tested in other environments.

Mike

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Also tried on a small system running 10.00 on Linux, and both versions ran fine, returning the same results, with the suggested change using the index.  Performance was the same because it's a small system.

I tried this on a 14.10.FC3 system (AIX) with over 644,000 rows in systabnames, and 232 objectsin the chosen space.  With the query that scans systabnames, it takes just over 20 seconds to complete.  With the query that uses the suggested clause, it returns in a fraction of a second.

When using a different space with 17,000 objects, the original query takes 21 seconds, and the modified query takes 5 seconds.

A space with 340,000 objects, the original query takes 23 seconds, and the modified query takes 5 seconds.

So, the suggestion does result in a significant performance improvement in this environment because of the indexed read instead of the scan.   I haven't tested in other environments.

Mike

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis
------------------------------

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

AGS Support have passed this to Development. Will let you know when the patched version is available.

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

The patch is here:

https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21190.zip

AGS Support have passed this to Development. Will let you know when the patched version is available.

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Doug,

It executes the query pretty fast, but now before it runs

select count(*) from sysmaster:'informix'.sysptnext a

extremely long, a query that I didn't see earlier.

The patch is here:

https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21190.zip

AGS Support have passed this to Development. Will let you know when the patched version is available.

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Will pass that back.

Doug,

It executes the query pretty fast, but now before it runs

select count(*) from sysmaster:'informix'.sysptnext a

extremely long, a query that I didn't see earlier.

The patch is here:

https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21190.zip

AGS Support have passed this to Development. Will let you know when the patched version is available.

Doug,

In my test environment on 11.70.FC5XE having 19837 rows in sysmaster:systabnames I run 4 benchmarks, both queries on cold and then hot cache.

'Enhanced', cold: Exec Time: 00:00:25,088; Rows retrieved: 14478
Original, cold: Exec Time: 00:04:02,135; Rows retrieved: 14478
'Enhanced', hot: Exec Time: 00:00:02,389; Rows retrieved: 14478
Original, hot: Exec Time: 00:00:02,791; Rows retrieved: 14478

I.e. the enhanced one wins significantly on cold cache.

------------------------------
Sincerely,
Dennis

Original Message:
Sent: Sat February 15, 2025 03:57 AM
From: Doug Lawry
Subject: RFE to Server Studio

Hi Dennis.

I passed that to AGS, and they responded as below. Does everyone agree that the fix is scalable and reliable on all systems and versions?

From AGS Support on 2025-02-14:

------------------------------
Doug Lawry
Oninit Consulting

Original Message:
Sent: Thu February 13, 2025 05:19 AM
From: Dennis Melnikov
Subject: RFE to Server Studio

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1

------------------------------
Sincerely,
Dennis
------------------------------

Hi,

That query captured while opening a DB Objects tab at a DBSpace properties,

SELECT a.tabname, a.dbsname, a.owner, ti_nextns, ti_nptotal, ti_npused, 
        ti_nptotal/26214400*100 percinspace, ti_nptotal * ti_pagesize, ti_npused * ti_pagesize,
    CASE
        WHEN(((ti_nkeys >0
                 AND ti_nrows > 0
                 AND ti_ncols > 0)
                 OR ti_nkeys=0
                 OR ti_ncols > 0
                 OR(ti_nkeys >0
                     AND ti_nrows > 50))
                 OR(tabname[1, 3]= 'sys'
                     AND dbsname[1, 3]= 'sys')
                 OR tabname = 'sysdirectives'
                 OR tabname = 'sysextdfiles') THEN
             't'
        ELSE
             'f'
    END, ti_flags
FROM sysmaster:'informix'.systabnames a, sysmaster:'informix'.systabinfo i
WHERE partnum = ti_partnum
     AND sysmaster:'informix'.partdbsnum(a.partnum) = ${dbsnum}
     AND a.tabname != 'TBLSpace'

I noticed that the query executes much faster if a condition with partdbsnum() replaced with this one,

     AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1