Hi Dennis.
Original Message:
Sent: Fri February 21, 2025 02:59 AM
From: Doug Lawry
Subject: RFE to Server Studio
Will pass that back.
------------------------------
Doug Lawry
Oninit Consulting
Original Message:
Sent: Fri February 21, 2025 02:53 AM
From: Dennis Melnikov
Subject: RFE to Server Studio
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.
------------------------------
Sincerely,
Dennis
Original Message:
Sent: Fri February 21, 2025 02:10 AM
From: Doug Lawry
Subject: RFE to Server Studio
The patch is here:
https://www.serverstudio.com/downloads/ags-software-repository/serverstudio_w_sentinel_patch_10.21190.zip
------------------------------
Doug Lawry
Oninit Consulting
Original Message:
Sent: Wed February 19, 2025 06:05 AM
From: Doug Lawry
Subject: RFE to Server Studio
AGS Support have passed this to Development. Will let you know when the patched version is available.
------------------------------
Doug Lawry
Oninit Consulting
Original Message:
Sent: Mon February 17, 2025 03:34 AM
From: Dennis Melnikov
Subject: RFE to Server Studio
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:
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
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
------------------------------