Hello,
we are having some performance problems with some kind of queries. This is a
simplified example of what is happening in our production environment.
We have an application with jboss and maxdb 7.5.00.23 (but same I've tested
7.5.00.31, and the behaviour is the same)
I've created a database with default parameters. Let's say we have a table,
with a field (HEADER_LINE_ID) is a foreign key of the same table, and
indexed.
CREATE TABLE LINE
(
ID Fixed (19,0) NOT NULL,
TYPE Integer,
CODE Char (20) ASCII,
HEADER_LINE_ID Fixed (19,0),
CREATION_DATE Date,
PRIMARY KEY (ID)
)
ALTER TABLE LINE ADD CONSTRAINT FK_HEADER_LINE FOREIGN KEY (HEADER_LINE_ID)
REFERENCES LINE (ID)
)
//
CREATE INDEX IDX_LINE_HEADER_LINE ON LINE (HEADER_LINE_ID)
//
I've done this test inserting 100.000 registers.
Under some circumstances (when jboss knows IDs for registers it wants to
load), it creates a query like this
SELECT ID, TYPE, CODE, HEADER_LINE_ID, CREATION_DATE
FROM LINE
WHERE (ID=2294752131926180503)
OR (ID=2294763105441405458)
.
. (n times)
.
OR (ID=2302419071075259925)
When the query has 50 or terms or less, the explain we get is like this:
LINE DIFFERENT STRATEGIES FOR OR-TERMS 1010
EQUAL CONDITION FOR KEY
ID (USED KEY COLUMN)
EQUAL CONDITION FOR KEY
.
. ( n times)
.
ID (USED KEY COLUMN)
EQUAL CONDITION FOR KEY
ID (USED KEY COLUMN)
RESULT IS COPIED , COSTVALUE IS 50
but, for 51 or terms of more, we get a table scan:
LINE TABLE SCAN 1010
RESULT IS NOT COPIED , COSTVALUE IS 1010
this seems strange, because for 50 or terms, cost is 50, and for 1 term
more, cost goes to 1010 (in production, cost changes from 50 to 18340).
Moreover, for field HEADER_LINE_ID, something similar happens.
For example, we get a query like this:
SELECT ID, TYPE, CODE, HEADER_LINE_ID, CREATION_DATE
FROM LINE
WHERE (HEADER_LINE_ID=2294752131926180503)
OR (HEADER_LINE_ID=2294763105441405458)
.
. (n times)
.
OR (HEADER_LINE_ID=2294478368382782110)
For 40 or terms or less, query is made with strategy "DIFFERENT STRATEGIES
FOR OR-TERMS" using index defined over HEADER_LINE_ID
LINE DIFFERENT STRATEGIES FOR OR-TERMS 298
IDX_LINE_HEADER_LINE EQUAL CONDITION FOR INDEX
.
. (n times)
.
IDX_LINE_HEADER_LINE EQUAL CONDITION FOR INDEX
RESULT IS COPIED , COSTVALUE IS 160
But for 41 terms, we get also a table scan
LINE TABLE SCAN 1010
RESULT IS NOT COPIED , COSTVALUE IS 1010
So, for more than 50 terms if table is accesed by primary key, or for more
than 40 terms if table is accesed by and indexed column, strategy "DIFFERENT
STRATEGIES FOR OR-TERMS" seems to be discarted. So, I'm changing
configuration of jboss to get queries with a limited number of
or terms, but I'd like to know if quantity of or-terms to discard or-terms
strategy is fixed, or is related to some database parameters, or maybe
depends of size of table accesed.
Best regards,
Jose Dillet
_________________________________________________________________
Dale rienda suelta a tu tiempo libre. Encuentra mil ideas para exprimir tu
ocio con MSN Entretenimiento. http://entretenimiento.msn.es/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
