db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christian Lipp" <c.l...@xion.at>
Subject selective parameter markers
Date Wed, 04 Jun 2008 09:36:15 GMT
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


Mime
View raw message