Re: Performance With 7.21 and HSTools
In article <01bd2b16$988b1ad0$52a09384@ohmi2>, Pascal PIZEINE
<ppizeine@sword.fr> writes
>We are using for our developments the following configuration':
>
>Client':
>Hyperscript Tools 1.10
>I-NET 5.01
>Windows 3.11
>
>Server':
>Informix 7.10
>Solaris 2.4
>
>Since we have upgraded informix 7.10 to 7.21, we have performance problems.
>Some queries do not use index but sequential scan. This problem occurs only
>with index on CHAR type and it depends on the query writing (see samples
>below). It seems to be an informix problem but we are not sure.
>We have tried UPDATE STATISTICS then dbexport/dbimport our database; it
>does not work.
>Does anybody have a solution.
>
Standard Engine or Online?
IF Online check inyour ONCONFIG file that OPTCOMPIND is still 0.
>Thanks,
>Pascal PIZEINE
>SWORD <http://www.sword.fr/>;
>
>Table
>----------------------------------------------------------------------------
>---------------
>
>create table "euroadm".inscri
> (
> idinscri char(12) not null constraint "euroadm".n598_1946,
> idrubric smallint not null constraint "euroadm".n598_1947,
> primary key (idinscri) constraint "euroadm".pk_inscri
> );
>revoke all on "euroadm".inscri from "public";>
>Hyperscript Program
>-------------------------------------------------------------------------
>
>execute immediate "set explain on"
>idinscri="000000001"
>
>DECLARE IMMEDIATE CURSOR "c_tl" FOR
>"SELECT idrubric,idinscri FROM inscri WHERE (idinscri = ?) "
>OPEN CURSOR "c_tl" USING idinscri
>TYPES "STRING"
>FETCH ALL "c_tl" INTO tab_tl
>sqlresult = SQLERRNO()
>FREE "c_tl"
>
>DECLARE IMMEDIATE CURSOR "c_tl" FOR
>"SELECT idinscri FROM inscri WHERE (idinscri = ?) "
>OPEN CURSOR "c_tl" USING idinscri
>TYPES "STRING"
>FETCH ALL "c_tl" INTO tab_tl
>sqlresult = SQLERRNO()
>FREE "c_tl"
>
>DECLARE IMMEDIATE CURSOR "c_tl" FOR
>"SELECT idrubric FROM inscri WHERE (idinscri = """&idinscri&""") "
>OPEN CURSOR "c_tl"
>FETCH ALL "c_tl" INTO tab_tl
>sqlresult = SQLERRNO()
>FREE "c_tl"
>execute immediate "set explain off"
>
>set explain with informix 7.10
>---------------------------------------------------------->
>SELECT idrubric,idinscri FROM inscri WHERE (idinscri = ?)>
>Estimated Cost: 1
>Estimated # of Rows Returned: 1
>
>1) asia.inscri: INDEX PATH
>
> (1) Index Keys: idinscri
> Lower Index Filter: asia.inscri.idinscri = '000000001'
>
>
>SELECT idinscri FROM inscri WHERE (idinscri = ?)>
>Estimated Cost: 1
>Estimated # of Rows Returned: 1
>
>1) asia.inscri: INDEX PATH
>
> (1) Index Keys: idinscri (Key-Only)
> Lower Index Filter: asia.inscri.idinscri = '000000001'
>
>
>SELECT idrubric FROM inscri WHERE (idinscri = "000000001")>
>Estimated Cost: 1
>Estimated # of Rows Returned: 1
>
>1) asia.inscri: INDEX PATH
>
> (1) Index Keys: idinscri
> Lower Index Filter: asia.inscri.idinscri = '000000001'
>
>
>set explain with informix 7.21
>---------------------------------------------------------->
>SELECT idrubric,idinscri FROM inscri WHERE (idinscri = ?)>
>Estimated Cost: 2
>Estimated # of Rows Returned: 1
>
>1) euroadm.inscri: SEQUENTIAL SCAN
>
> Filters: euroadm.inscri.idinscri = '000000001'
>
>
>SELECT idinscri FROM inscri WHERE (idinscri = ?)>
>Estimated Cost: 2
>Estimated # of Rows Returned: 1
>
>1) euroadm.inscri: INDEX PATH
>
> Filters: euroadm.inscri.idinscri = '000000001'
>
> (1) Index Keys: idinscri (Key-Only)
>
>
>SELECT idrubric FROM inscri WHERE (idinscri = "000000001")>
>Estimated Cost: 2
>Estimated # of Rows Returned: 1
>
>1) euroadm.inscri: INDEX PATH
>
> (1) Index Keys: idinscri
> Lower Index Filter: euroadm.inscri.idinscri = '000000001'
>
>
>
--
David Williams
Maintainer of the Informix FAQ
Primary site (Beta Version) http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html
I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care