• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query taking forever

 
Jehan Jaleel
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jehan Jaleel
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should be getting both right now. Try adding a where clause to filter the ones you want.
 
Jehan Jaleel
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jehan Jaleel
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jehan,
Great to know it helped!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic