Skip to main content

developerWorks >  WebSphere  >  Forums  >  WebSphere Extended Deployment (XD)  >  developerWorks

ObjectQuery execution taking too long.    Point your RSS reader here for a feed of the latest messages in this thread


Tags for this thread: 

     

 
 

My developerWorks
 Welcome, Guest
Sign in or register
Permlink Replies: 3 - Pages: 1 - Last Post: Nov 17, 2009 5:58 PM Last Post By: Chris.D.Johnson
sriradix

Posts: 18
Registered: Oct 21, 2009 04:23:11 PM
ObjectQuery execution taking too long.
Posted: Nov 12, 2009 05:04:45 PM
Click to report abuse...   Click to reply to this thread Reply
The ObjectQuery is taking too long.
Following is the QueryPlan for the actual Query. Please suggest any improvements that can be made.

group input collection temp collection defined as

for q2 in (Users ObjectMap using UNIQUE INDEX key=( ?1) AND_Collection Users ObjectMap using INDEX on DELETED = (false))

for q3 in userroles ObjectMap using INDEX on USER_ID = ( q2.id)

for q4 in ( (roleaccounts ObjectMap using INDEX on ROLE_ID = ( q3.getRoleId()) AND_Collection roleaccounts ObjectMap using INDEX on ACCOUNT_ID = ( ?2))

AND_Collection roleaccounts ObjectMap using RANGE INDEX on ACCOUNT_PERMISSION_ID with range(,999))

filter ((((( q3.getUserId() = q2.id ) AND ( q4.getRoleId() = q3.getRoleId() ) ) AND ( q4.getAccountId() = ?2 ) ) AND ( q2.deleted = false ) ) AND (

q4.getAccountPermissionId() < 999 ) )

returning new Tuple( q4.getAccountPermissionId() )

group output collection s1 (MAX( q1.c[1]) )

for q1 in s[1]
returning new Tuple( q1.c[1]
Chris.D.Johnson

Posts: 216
Registered: Oct 14, 2005 04:31:08 PM
Re: ObjectQuery execution taking too long.
Posted: Nov 13, 2009 09:29:50 AM   in response to: sriradix in response to: sriradix's post
Click to report abuse...   Click to reply to this thread Reply
Can you include the query string?
sriradix

Posts: 18
Registered: Oct 21, 2009 04:23:11 PM
Re: ObjectQuery execution taking too long.
Posted: Nov 13, 2009 09:50:42 AM   in response to: Chris.D.Johnson in response to: Chris.D.Johnson's post
Click to report abuse...   Click to reply to this thread Reply
Query String:

"select max(ra.accountPermissionId) " +
"from Users u, userroles ur, roleaccounts ra " +
"where u.id = ?1 " +
"and u.id = ur.userId " +
"and ur.roleId = ra.roleId " +
"and ra.accountId = ?2 " +
"and u.deleted = FALSE " +
"and ra.accountPermissionId < 999");
Chris.D.Johnson

Posts: 216
Registered: Oct 14, 2005 04:31:08 PM
Re: ObjectQuery execution taking too long.
Posted: Nov 17, 2009 05:58:29 PM   in response to: sriradix in response to: sriradix's post
Click to report abuse...   Click to reply to this thread Reply
This is going to be slow unless you can establish a relationship between the objects and then use the join keyword with dot path navigation. Using = joins can be expensive. Any JPA or EJB 3 book can help you craft a good join/navpath expression.
 Tags
Help

Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular type of content or application that you're viewing.

My tags shows your tags for this particular type of content or application that you're viewing.

 

MoreLess 


Point your RSS reader here for a feed of the latest messages in all forums