Performance problem in 7.6.6.10

[Former Member]

We have a performance problem after doing the update from MaxDB 7.6.6.3 to 7.6.6.10.

The symptom is that querys with the "<>" operator in the WHERE-Clause on a indexed Integer/SmallInteger-column slows down extremly, e.g. "WHERE FILEDNAME <> 1".

On large tables the query is very, very slow.

The dbanalyser shows "DIFFERENT STRATEGIES FOR OR-TERMS".

A way to reproduce the prob:

Create a table with 2 columns

-

---

CREATE TABLE "ADMIN"."TEST"

(

"INTID" Integer NOT NULL,

"FLAG" Smallint,

PRIMARY KEY("INTID")

)

Index on Column FLAG

-

---

CREATE INDEX "IDX_TEST" ON "ADMIN"."TEST"("FLAG" ASC)

Insert about 1000 lines in the TEST

-

---

INSERT INTO TEST (SELECT ROWNO, 1 FROM LARGETABLE WHERE ROWNO <= 1000)

(The easiest way for me to fill the table.)

Call the dbanalyser

-

---

EXPLAIN SELECT * FROM TEST WHERE FLAG <> 1

OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT

ADMIN TEST DIFFERENT STRATEGIES FOR OR-TERMS 8

IDX_TEST RANGE CONDITION FOR INDEX

ONLY INDEX ACCESSED

FLAG (USED INDEX COLUMN)

IDX_TEST RANGE CONDITION FOR INDEX

ONLY INDEX ACCESSED

FLAG (USED INDEX COLUMN)

RESULT IS COPIED , COSTVALUE IS 6

QUERYREWRITE - APPLIED RULES:

DistinctPullUp 1

The statement is fast because of the small table, but I think the startegy is wrong.

[lbreddemann]

> We have a performance problem after doing the update from MaxDB 7.6.6.3 to 7.6.6.10.

> The symptom is that querys with the "<>" operator in the WHERE-Clause on a indexed Integer/SmallInteger-column slows down extremly, e.g. "WHERE FILEDNAME <> 1".

> On large tables the query is very, very slow.

> Index on Column FLAG

> -

---

> CREATE INDEX "IDX_TEST" ON "ADMIN"."TEST"("FLAG" ASC)

> The statement is fast because of the small table, but I think the startegy is wrong.

Hmm.. what other strategy would you propose?

The single table optimizer tries to estimate how many pages would need to be read to find the data required.

It figures that for your statement there won't be many pages required, so a index access might be beneficial.

And to use the index efficiently it transforms your unequality to a "larger then" OR "smaller then" condition.

So you get "DIFFERENT STRATEGIES FOR OR-TERMS".

If you look closely you'll find that both strategies actually are "RANGE CONDITION FOR INDEX" on the IDX_TEST index.

The difference between them both is the range (the start/stop-key combination) used for the index reading.

Anyhow - unquality conditions are always problematic to DBMS.

They are designed to be quick to find data that is equal or like some condition.

regards,

Lars