RFE to Server Studio
Dennis Melnikov found a Server Studio query on the DB Objects tab ran much faster with partnum BETWEEN ranges instead of partdbsnum(). Doug Lawry relayed it to AGS. Mike Walker and Dennis confirmed big gains, especially on cold cache. AGS issued patch 10.21190, and after Dennis found a slow sysptnext count, a second fix, 10.21192. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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: We ran both queries in our SQL Tuner and there is a difference in Estimated Cost: 92 vs 16. As it explained, it is because partdbsnum function does not use index and uses scan (see the side-by-side comparison) But actual execution of these 2 queries on our test systems has produced no difference. It takes less than 1 second to execute (without data fetching time) and time difference is within milliseconds and actually fluctuates. We ran both queries in SQL Tuner 1000 times each and the average execution time was identical. Obviously it might be completely different result on a large system. Our test systems are relatively small. We would appreciate if you could test both queries on large installations when this query takes more than a few seconds to retrieve data in Server Studio. Also, even though both queries appear to produce identical results, can you confirm that replacement of partdnsnum with AND a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1 is scalable and works on all Informix systems? We can test it with our IDS 12/14/15 test servers but it potentially might be some special case that could lead to different results based on system configuration parameters, OS versions, etc., which might see right away? If it proves to provide a significantly better performance on large systems and you do not see any potential issues with this approach, we can create a patch that turns on this version of data retrieval SQL based on a configuration switch so that you could test it in real-life scenarios before releasing it. ------------------------------ Doug Lawry Oninit Consulting ------------------------------
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 ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
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. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
Thanks, Mike. I'll update AGS on Monday. ------------------------------ Doug Lawry Oninit Consulting ------------------------------
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 ------------------------------
It is very evident that the new query performs better than the old one, especially in the case of a cold cache: Cold Cache: Enhanced: 25.088 seconds Original: 4 minutes, 2.135 seconds Hot Cache: Enhanced: 2.389 seconds Original: 2.791 seconds The improved performance, especially with cold cache, shows substantial optimizations in the enhanced query. Wonderful work on benchmarking!
Hi Sir,
It's great that you've identified a way to improve the performance of your query! It seems like using the
BETWEEN
condition provides a more efficient filtering mechanism compared to the
partdbsnum()
function.
By replacing the
partdbsnum()
function with the
BETWEEN
condition, you are directly.
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 a.partnum BETWEEN ${dbsnum}*1048576 AND (${dbsnum}+1)*1048576-1
AND a.tabname != 'TBLSpace'
------------------------------
suman suhag
------------------------------
AGS Support have passed this to Development. Will let you know when the patched version is available. ------------------------------ Doug Lawry Oninit Consulting ------------------------------
The patch is here: https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21190.zip ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Doug,
It executes the query pretty fast, but now before it runs
select count(*) from sysmaster:'informix'.sysptnext aextremely long, a query that I didn't see earlier.
------------------------------
Sincerely,
Dennis
------------------------------
Will pass that back. ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Hi Dennis. AGS reproduced the second problem and have provided this fix: https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21192.zip ------------------------------ Doug Lawry Oninit Consulting ------------------------------