This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Subselect runs fast, outer query doesn't Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Subselect runs fast, outer query doesn Watch "Subselect runs fast, outer query doesn New topic
Author

Subselect runs fast, outer query doesn't

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10

I killed this query after 543 seconds. However, the inner select (select #2) runs in about 7 seconds. It returns 836 records and the RU_METRIC_DET table contains 2597374 records where DETAIL_ID is a primary key.

What I really want to do is a delete from RU_METRIC_DET (i.e. replace select #1 with a delete), which is why I need the outer select.

Can someone help me out with a suggestion?
[ October 18, 2008: Message edited by: Marilyn de Queiroz ]

JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

does DETAIL_ID on the outer table have an index?
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
Oops. I found the problem. I was using the wrong table on the outer select

Sorry to have bothered you all.
 
 
subject: Subselect runs fast, outer query doesn't
 
Similar Threads
SQL to HQL (Having issue in Left Outer Join)
How to show results as zero if no record exists?
a general doubt in using Left Join Fetch
Joining entities for a simple full text search
JDBC template outer join and cast(@myValue AS date)