Informix

 View Only

  • 1.  Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    Posted Thu November 09, 2023 03:28 PM

    Hi Guys,

    I have a very simple but ugly query that would run in minimal memory in Informix 12.10FC10 but takes gigabytes (mostly in ralloc) in Informix 14.10FC9 

    This is fine when we run it on the production host as that has plenty of capacity - but it does not play well in our UAT environments.

    What makes the table ugly is that there is a field which is being searched called std_text; this is a varchar(18000)

    The query plan from informix 12 and informix 14 look pretty identical.  Any suggestions to reduce the memory footprint would be helpful.  

    Here is the sqexplain

    QUERY: (OPTIMIZATION TIMESTAMP: 11-09-2023 20:47:47)
    ------
    SELECT TMT.ttm_id, TMT.curr_hist_flag, TMT.std_text as std_text
    FROM crs_title_mem_text TMT
    WHERE
    std_text matches '*gross*' and
    (
    std_text matches '*Piako-District*'
    or std_text matches '* hutt County Council*')
    into temp mt


    Estimated Cost: 17586428
    Estimated # of Rows Returned: 3242695

      1) informix.tmt: SEQUENTIAL SCAN

            Filters: (informix.tmt.std_text MATCHES '*gross*' AND (informix.tmt.std_text MATCHES '*Piako-District*' OR informix.tmt.std_text MATCHES '* hutt County Cou
    ncil*' ) )


    Query statistics:
    -----------------

      Table map :
      ----------------------------
      Internal name     Table name
      ----------------------------
      t1                tmt
      t2                mt

      type     table  rows_prod  est_rows  rows_scan  time       est_cost
      -------------------------------------------------------------------
      scan     t1     10         3242695   45115121   07:43.76   17586428

      type     table  rows_ins   time
      -----------------------------------
      insert   t2     10         07:43.76



    ------------------------------
    Mark Tyrer
    ------------------------------


  • 2.  RE: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    IBM Champion
    Posted Thu November 09, 2023 04:59 PM

    Hi,

    Can you test this against 14.10.FC10W1?

    Whilst the query is running what do you see from onstat -g stk <thread-id> for Version 12 and Version 14?

    Run onstat -g afr ralloc and total values by source file file and line_number, is there a common place these allocations are occuring?

    Once you have this either upgrade if it fixes it or open a case!


    Regards,
    David.



    ------------------------------
    David Williams
    ------------------------------

    Original Message


  • 3.  RE: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    Posted Fri November 10, 2023 02:16 AM

    Obviously a defect to raise with IBM, but try this work-around:

    ... WHERE

    CHARINDEX('gross',std_text) > 0 AND (

    CHARINDEX('Piako-District',std_text) > 0 OR

    CHARINDEX(' hutt County Council',std_text) > 0 ) )

    You could also try LIKE and % instead of MATCHES and *, but I expect the above would be lighter and faster anyway.



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

    Original Message


  • 4.  RE: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    Posted Fri November 10, 2023 02:28 AM

    See also:

    https://www.ibm.com/docs/en/informix-servers/14.10?topic=guide-basic-text-search

    That requires significant setup, but you could then use BTS_CONTAINS instead of CHAR_INDEX which would be quicker still.



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

    Original Message


  • 5.  RE: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    IBM Champion
    Posted Fri November 10, 2023 08:39 AM

    I'd also say something to bring to support's attention: such a simple (though probably expensive) query, with no choice but taking a sequential scan path, should not have a huge memory footprint.

    Just for clarity: does the query take a similarly long time in 12.10?



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 6.  RE: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.

    IBM Champion
    Posted Fri November 10, 2023 11:30 AM

    Mark:

    Is it known and consistent whether the substring "gross" precedes or follows the substrings "Piako-District" and " hutt County Council"? because I believe that the one of the following might perform better:

    Option 1:

    SELECT TMT.ttm_id, TMT.curr_hist_flag, TMT.std_text as std_text
    FROM crs_title_mem_text TMT
    WHERE std_text matches '*gross*Piako-District'
    OR  std_text matches '*gross* hutt County Council*'
    INTO temp mt WITH NO LOG;

    Option 2:

    SELECT TMT.ttm_id, TMT.curr_hist_flag, TMT.std_text as std_text
    FROM crs_title_mem_text TMT
    WHERE std_text matches '*gross* hutt County Council*'
    UNION
    SELECT TMT.ttm_id, TMT.curr_hist_flag, TMT.std_text as std_text
    FROM crs_title_mem_text TMT
    WHERE std_text matches '*gross*Piako-District'
    INTO temp mt WITH NO LOG;

    Option 3 using the Regular Expressin datablade:

    SELECT TMT.ttm_id, TMT.curr_hist_flag, TMT.std_text as std_text
    FROM crs_title_mem_text TMT
    WHERE regex_match( std_text, 'gross(.)Piako-District|gross(.)* hutt County Council', 1 )
    INTO temp mt WITH NO LOG;



    ------------------------------
    Art Kagel
    ------------------------------

    Original Message