RE: selective parameter markers

[Christian Lipp <c....@xion.at>]

Hello!

I have learned more about the reasons for this:

When a statemnt retrievies more than 10% of the entries from a table,
an index scan is inefficient. Instead the database should use a table scan.
Therefor the database should know the values from an index (it does) and
judge for each statement,
if it should use an index or an table scan.

When using parameter markers the database cannot choose on the value (it
doesn't see it)
and therefor it uses always the index scan, which leads to bad performance
for all the statements
which deliver mor than 10% of the entries. In our case we come up to 60% of
50.000.000 rows,
which ruins the performance.

CL


-----Original Message-----
From: Christian Lipp [mailto:c.lipp@xion.at] 
Sent: Mittwoch, 04. Juni 2008 11:36
To: 'OJB Users List'
Subject: selective parameter markers

Hello!

I would like to ask you for your opinion to a certain problem we encountered
and solved via a hardcoded solution.

We have a timestamp-field (the name is "replaced") in some tables which has
the information "is valid until".
When the data entry is valid, the value of this field is "9999-12-31
23:59:59:999999".
When the data entry is invaild, the value of this field is a concrete
timestamp.

Now the table has 50.000.000 entries and 30.000.000 entries have the value
"9999-12-31 23:59:59:999999", which are 60%.
The database is DB2. There is an index on the column, so DB2 knows the n
most used values.

With parameter marker:
When using the column "replaced" in a where-statement with OJB, the value
"9999-12-31 23:59:59:999999" is replaced with a parameter marker. 
DB2 calculates an execution plan which expects a certain (small) amount of
data and gets 30.000.000 rows. The performance is not useable.
Whe using a concrete value, the performance is optimal.

Without parameter marker:
When using the column "replaced" in a where-statement with OJB, the value
"9999-12-31 23:59:59:999999" is NOT replaced with a parameter marker. 
DB2 knows the value and therefor knows that there will be a huge result set.
The performance is ok.
Whe using a concrete value, the statement is not cached, so the performance
is not so good as with parameter marker.

So the ideal strategie is to use a parameter marker for the column replaced,
when there is a concrete value and not to use a parameter marker for default
values ("9999-12-31 23:59:59:999999").
Therefor we coded our own SqlGenerator-Implementation.

We have tested with other columns also and got the best performance when
using parameter markers only on certain columns.

What I would like to know:
Does anyone have similary problems with parameter marker and how do you deal
with it?
Can OJB configure the use of parameter marker on certain columns (usage
on/off)?

Thanks in advance,
CL


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org