Performance With 7.21 and HSTools
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.
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'