upgrading IIF-9.21-UC2 to IIF-9.21-UC3 and select became 
Author Message
 upgrading IIF-9.21-UC2 to IIF-9.21-UC3 and select became

Have you tried you query without the directives? A seq. scan might be faster.

Or, (and this may not help) ...
If you always filter on UPPER(nickname), then why not create a functional index ?

You'll have to trick ids, because UPPER is defined as variant (why ???)

CREATE PROCEDURE upper_idx(in CHAR(xx))
RETURNING CHAR(xx) WITH (NOT VARIANT);
        RETURN UPPER(in);
END PROCEDURE;

CREATE INDEX test_idx ON consumer (upper_idx(nickname));

SELECT ...
WHERE upper_idx(nickname) = upper_idx('slontch')

Quote:

> Dmitry

> SET EXPLAIN ON;
> RUN THE QUERY;

> post sqexplain.out

> Regards

> Brett Randall


> > Hi, all
> > We, have upgraded IIF-9.21.UC2 to IIF-9.21.UC3. No config files were
> > changed, UPDATE STATISTICS HIGH was made. Informix now is very slow -
select
> > * from consumer where UPPER(nickname) = UPPER('asdf'); now takes from 10
to
> > 200 sec while it took less then a second on UC2. Table has 80000 rows
only.
> > What could be cause of this problem?

This is output of UC2 (it takes less then a second):

QUERY:
------
select {+ EXPLAIN, INDEX ( consumer ix_consumer_login ) } pwd, id_Consumer,
forbid, email, email_checked, club_status from Consumer
where UPPER(nickname)=UPPER('slontch')

DIRECTIVES FOLLOWED:
EXPLAIN
INDEX ( consumer ix_consumer_login )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 79403
Estimated # of Rows Returned: 8033

  1) informix.consumer: INDEX PATH

        Filters: UPPER(informix.consumer.nickname ) = 'SLONTCH'

    (1) Index Keys: nickname   (Serial, fragments: ALL)

This is output of UC3 (this takes at least 6 seconds, right after restart,
then it could take up to 300 sec):

QUERY:
------
select {+ EXPLAIN, INDEX ( consumer ix_consumer_login ) } pwd, id_Consumer,
forbid, email, email_checked, club_status from Consumer
where UPPER(nickname)=UPPER('Kazarov')

DIRECTIVES FOLLOWED:
EXPLAIN
INDEX ( consumer ix_consumer_login )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 78896
Estimated # of Rows Returned: 7983

  1) informix.consumer: INDEX PATH

        Filters: UPPER(informix.consumer.nickname ) = 'KAZAROV'

    (1) Index Keys: nickname   (Serial, fragments: ALL)

Dmitry



Tue, 02 Sep 2003 20:09:01 GMT
 upgrading IIF-9.21-UC2 to IIF-9.21-UC3 and select became



Quote:

> Have you tried you query without the directives? A seq. scan might be

faster.

There was no directives in this select originally - I've inserted it as part
of my optimisation efforts.
With no success.

Quote:

> Or, (and this may not help) ...
> If you always filter on UPPER(nickname), then why not create a functional
index ?

> You'll have to trick ids, because UPPER is defined as variant (why ???)

> CREATE PROCEDURE upper_idx(in CHAR(xx))
> RETURNING CHAR(xx) WITH (NOT VARIANT);
> RETURN UPPER(in);
> END PROCEDURE;

> CREATE INDEX test_idx ON consumer (upper_idx(nickname));

> SELECT ...
> WHERE upper_idx(nickname) = upper_idx('slontch')

Thank you, I'll try this solution.

Sincerely Yours
Dmitry



Fri, 05 Sep 2003 22:50:23 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. upgrading IIF-9.21-UC2 to IIF-9.21-UC3 and select became very slow.

2. Diff between 9.21.UC3-1 and 9.21.HC3-1

3. FW: Diff between 9.21.UC3-1 and 9.21.HC3-1

4. JDBC for IIF.2000 9.21.UC5

5. IIF.20009.21 on Linux and shadow password/PAM

6. -9753 procedure execution error with 9.21.UC3 upgrade

7. I can't find Release Notes for Informix Dynamic Server.2000 9.21.UC3

8. Raw device problem (Informix Dynamic Server 2000 V9.21.UC2 on Red Hat 7.3)

9. Raw device problem (Informix Dynamic Server 2000 V9.21.UC2 on Red

10. oninit -i failing on Informix Dynamic Server 2000 Version 9.21.UC2

11. IDS9.21.UC2/Redhat7.1: NETTYPE soctcp,2,,NET not listening

12. Datetime err in SPL in Ver 9.21.UC2


 
Powered by phpBB® Forum Software