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:
Sent: Thu November 09, 2023 03:09 AM
From: Mark Tyrer
Subject: Ugly Query - It ran in 12.10FC10 but doesn't finish in 14.10FC9 due to memory issues.
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
------------------------------