Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.
Mark Tyrer reported a MATCHES query on a varchar(18000) column that ran in minimal memory on 12.10.FC10 but used gigabytes on 14.10.FC9. David Williams suggested testing 14.10.FC10W1, onstat -g stk and -g afr ralloc, or a support case. Doug Lawry suggested CHARINDEX or BTS_CONTAINS; Art Kagel offered rewrites with combined MATCHES, UNION or regex_match. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
WHEREstd_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
------------------------------
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
------------------------------
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 ------------------------------
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 ------------------------------
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 ------------------------------
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
WHEREstd_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
------------------------------