This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Query taking forever Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query taking forever" Watch "Query taking forever" New topic
Author

Query taking forever

Jehan Jaleel
Ranch Hand

Joined: Apr 30, 2002
Posts: 196
Hi all,

This is more a SQL/DB2 question, but this is my favorite forum so I will ask here...

The following query is taking forever to run on DB2...


It looks simple. I have heard that NOT IN should not be used, is this true. Can someone help me rewrite this query?

Thanks much,
Jehan
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
Can you quantify "taking forever"?
How many rows are in SONG_KNOWLEDGE_BASE and SONG?
Do you have any indexes on SONG_CD?
What is SONG_CD's type?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Jehan,
SQL queries are perfectly welcome in the JDBC forum.

A left outer join will likely perform better than "not in" assuming the song table is large. That way it only needs to be scanned through once.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jehan Jaleel
Ranch Hand

Joined: Apr 30, 2002
Posts: 196
Jeanne Boyarsky wrote:Jehan,
SQL queries are perfectly welcome in the JDBC forum.

A left outer join will likely perform better than "not in" assuming the song table is large. That way it only needs to be scanned through once.


Jeanne,

I rewrote the query to use left outer join like so....



I am not sure if this right because I am getting values in SONG_KNOWLEDGE_BASE that are also in SONG. But this is not what I need. I need those entries in SONG_KNOWLEDGE_BASE which are not in SONG.

Thanks again for your help,
Jehan
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

You should be getting both right now. Try adding a where clause to filter the ones you want.
Jehan Jaleel
Ranch Hand

Joined: Apr 30, 2002
Posts: 196
Tom Reilly wrote:Can you quantify "taking forever"?
How many rows are in SONG_KNOWLEDGE_BASE and SONG?
Do you have any indexes on SONG_CD?
What is SONG_CD's type?


Tom,

Taking forever means like over an hour. The song table has 3 million records and the Song Knowledge Base has 2 million.

Thanks again,
Jehan
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

As Jeanne pointed out, joins are often preferable to nested queries, not only in MySQL but in many other database. Also, have you defined foreign keys and indexes on the two tables?


My Blog: Down Home Country Coding with Scott Selikoff
Jehan Jaleel
Ranch Hand

Joined: Apr 30, 2002
Posts: 196
I wrote the following query and it came back right away with just the data I wanted...



Thanks guys!!
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Jehan,
Great to know it helped!
 
 
subject: Query taking forever