Bugs item #532734, was opened at 2002-03-20 16:17
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=532734&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Closed
Resolution: Rejected
Priority: 5
Submitted By: Marius Kotsbak (mkotsbak)
Assigned to: Dain Sundstrom (dsundstrom)
Summary: Ineffective SQL-code generated
Initial Comment:
I found this in the log. It seems like it is generating
ineffective sql. Wouldn't it be more effective (easier
for postgresql to parse) by using possibleValueID IN
(3,5,6,9, etc) for example?
It also seems to read repeatly from the base a entity
bean marked as read only, time-out=0, and read-ahead.
(it shows generated sql of it several times)
2002-03-20 20:11:28,480 DEBUG
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.PossibleValue]
Executing SQL: SELECT possibleValueID,stringValue,
priority FROM PossibleValue WHERE (possibleValueID=?)
OR (poss
ibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleV
alueID=?) OR (possibleValueID=?) OR (possibleValueID=?)
OR (possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueI
D=?) OR (possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?)
OR (possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (p
ossibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possib
leValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleValueID=?) OR
(possibleValueID=?) OR (possibleVal
ueID=?)
----------------------------------------------------------------------
Comment By: Wellie Chao (wchao)
Date: 2002-04-14 10:57
Message:
Logged In: YES
user_id=433887
I should also mention that I'm using PostgreSQL 7.2 with
the stable 7.2 JDBC drivers for PostgreSQL from Feb. 9. I'm
running the JBoss 3.0 beta from Feb. 21/22.
----------------------------------------------------------------------
Comment By: Wellie Chao (wchao)
Date: 2002-04-14 10:48
Message:
Logged In: YES
user_id=433887
I am having the same issue. I use a simple Integer primary
key in a table called job_tbl. I also have a column of type
Integer called employerId. When I do
jobHome.findByEmployerId(employerId) and iterate through
the resulting Collection so that I can copy a subset of the
properties to value objects, I get the horribly inefficient
code described in the original bug report. If there are {n}
resulting rows, JBoss has to do n*(n-1)/2 queries (plus one
query to find all rows where employerId matches)! On the
first query, it does "select column1, column2, ... from
job_tbl where ((id = 1) or (id = 2) or ... or (id = n))".
On the second query, it does "select column1, column2, ...
from job_tbl where ((id = 1) or (id = 2) or ... or (id = n -
1))". It repeats until it gets to "select column1,
column2, ... from job_tbl where ((id = 1))".
Why does it do this? Couldn't it just find out what the
primary key values are for rows with a matching employerId,
then get them one by one with select column1, column2, ...
from job_tbl where (id = {x}) where {x} takes on the value
of each primary key value? This wouldn't be quite as fast
as getting everything in just one query, but {n} queries is
much better than n*(n-1)/2 queries!
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-04-13 22:57
Message:
Logged In: YES
user_id=251431
I tried changing the read-ahead code to use IN for PKs that
map to a single column, but Hypersonic choaked.
This seems like a db specific optimization, and those are
for another version.
----------------------------------------------------------------------
Comment By: Marius Kotsbak (mkotsbak)
Date: 2002-03-23 05:10
Message:
Logged In: YES
user_id=366650
>What happens when there is more then one pk column?
Do you ask me? In my example, it is just one "long" PK
column (CMP-beans). I don't think I am using pk-objects in
this part either.
To me it looks like jboss is doing some sort of manual
joining too (wich postgres supports presumably much faster).
If I had written the sql statements for my example, I
wouldn't have needed any IN(2,4,6 etc), just JOINing. Also
this part of my application is slow, even if I try to tune
it with "on-load", "on-find".
>Please don't adjust bug priorities.
I'm sorry. Didn't know I was allowed to do it....
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-03-22 16:44
Message:
Logged In: YES
user_id=251431
What happens when there is more then one pk column?
Please don't adjust bug priorities.
----------------------------------------------------------------------
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=532734&group_id=22866
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
