• Admin-Scout-small-Banner
           
    CURSOR Admin-Scout
    get the ultimate tool for Informix
    pfeil  
invispix 10x10
invispix 10x10
Latest versionsfixlist
14.10.xC5 FixList
12.10.xC14.X5 FixList
11.70.xC9.XB FixList
11.50.xC9.X2 FixList
11.10.xC3.W5 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

Informix - Problem description

Problem IT29971 Status: Closed

PERFORMANCE PROBLEM WITH LVARCHAR INDEX WITH SELECT FIRST QUERY

product:
INFORMIX SERVER / 5725A3900 / E10 - 
Problem description:
When doing a "select first n" query using a lvarchar index and
like or matches compariosn.
the query will take a long time when n is greater then the
number of results returns

in this example:
select  search_name, regcode from companies where search_name
like 'CAA%' order by search_name asc
we will get 5 results


select first 5 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc
will return quick:
select first 5 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

Estimated Cost: 170
Estimated # of Rows Returned: 200000

  1) informix.companies: INDEX PATH

        Filters: informix.companies.search_name LIKE 'CAA%'

    (1) Index Name: informix.idx__companies__search_name
        Index Keys: search_name   (Serial, fragments: ALL)

UDRs in query:
--------------
    UDR id  :   -722
    UDR name:   compare

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

The final cost of the plan is reduced because of the FIRST n
specification in
 the query.

 Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                companies

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     5          200000    34709      00:00.70   170



select first 10 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

QUERY: (OPTIMIZATION TIMESTAMP: 07-17-2019 15:09:36)
------
select first 10 search_name, regcode from companies where
search_name like 'CAA%' order by search_name asc

Estimated Cost: 340
Estimated # of Rows Returned: 200000

  1) informix.companies: INDEX PATH

        Filters: informix.companies.search_name LIKE 'CAA%'

    (1) Index Name: informix.idx__companies__search_name
        Index Keys: search_name   (Serial, fragments: ALL)

UDRs in query:
--------------
    UDR id  :   -722
    UDR name:   compare

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

The final cost of the plan is reduced because of the FIRST n
specification in
 the query.

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                companies

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     5          200000    1000000    00:28.29   341




If we use > and < syntax, it will be fast again, as index
filters will be applied.
Problem Summary:
****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS 14.10.xC1.                                      *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* When doing a "select first n" query using a lvarchar index   *
* and                                                          *
* like or matches comparison,                                  *
* the query will take a long time when n is greater then the   *
* number of results returned.                                  *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************
Local Fix:
Solution
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
12.08.2019
19.09.2019
19.09.2019
Problem solved at the following versions (IBM BugInfos)
14.10.xC2
Problem solved according to the fixlist(s) of the following version(s)
invispix 10x10
invispix 10x10
invispix 10x10
Technical InfoTechnical Info

Technical Info

DocumentationDocumentation

Documentation

IBM NewsletterIBM Newsletter

IBM Newsletter

Current BugsCurrent Bugs

Current Bugs

Bug ResearchBug Research

Bug Research

Bug FixlistsBug Fixlists

Bug Fixlists

Release NotesRelease Notes

Release Notes

Machine NotesMachine Notes

Machine Notes

Release NewsRelease News

Release News

Product LifecycleProduct Lifecycle

Lifecycle

Media DownloadMedia Download

Media Download

invispix 10x10
Service und SupportService und Support

Service and Support

Informix MonitoringInformix Monitoring

Informix Monitoring

Admin-Scout forInformixAdmin-Scout forInformix

Admin-Scout for Informix

Informix News & InfosInformix News & Infos

Informix News & Infos

This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.