Informix

 View Only
Expand all | Collapse all

RFE to Server Studio

  • 1.  RFE to Server Studio

    Posted 13 days ago

    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
    ------------------------------


  • 2.  RE: RFE to Server Studio

    Posted 11 days ago
    Edited by Doug Lawry 9 days ago

    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
    ------------------------------





  • 3.  RE: RFE to Server Studio

    Posted 11 days ago

    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
    ------------------------------



  • 4.  RE: RFE to Server Studio

    Posted 11 days ago

    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
    ------------------------------



  • 5.  RE: RFE to Server Studio

    Posted 11 days ago

    Thanks, Mike. I'll update AGS on Monday.



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



  • 6.  RE: RFE to Server Studio

    Posted 9 days ago
    Edited by Dennis Melnikov 9 days ago

    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
    ------------------------------



  • 7.  RE: RFE to Server Studio

    Posted 9 days ago
    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!






  • 8.  RE: RFE to Server Studio

    Posted 7 days ago

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



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



  • 9.  RE: RFE to Server Studio

    Posted 5 days ago

    The patch is here:

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



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



  • 10.  RE: RFE to Server Studio

    Posted 5 days ago

    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
    ------------------------------



  • 11.  RE: RFE to Server Studio

    Posted 5 days ago

    Will pass that back.



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



  • 12.  RE: RFE to Server Studio

    Posted 13 hours ago

    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
    ------------------------------



  • 13.  RE: RFE to Server Studio

    Posted 9 days ago

    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
    ------------------------------