wood burning stoves*
The moose likes JDBC and the fly likes Designing a multiple Join query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Designing a multiple Join query" Watch "Designing a multiple Join query" New topic
Author

Designing a multiple Join query

s khosa
Ranch Hand

Joined: May 15, 2003
Posts: 72
Hi,
Just wanted to have some opinion on the fact that what will be the best way to design a query that is joining around five tables with around 80,000 rows and returns a resultset of around 170 thousand rows. I have ruled out outer joins as they are too slow.
The problem is that when i run the query on SQL prompt on oracle server, its returns results pretty fast. But running it through JDBC on middle tier makes it slow. The query is:
SELECT REL.RL_ID, REL.CONTENT_ID, REL.WEIGHT, REL.PASS, DC.DC_ID, DC.DC_DISPLAYNAME, MAP.MP_ID, MAP.MP_DISPLAYNAME, MAP.MP_NODECOUNT, MAP.FLASHMAP_RELPATH, MAP.MP_RELEVANCE, NODE.ND_ID, NODE.ND_DISPLAYNAME, NODE.ND_RELEVANCE, ARTICLE.ART_ID, ARTICLE.ART_URL, ARTICLE.ART_TITLE, CHANNEL.CH_ID, CHANNEL.CH_NAME, CHANNEL.IMAGE_PATH, CHANNEL.DESCRIPTION, CHANNEL.CATEGORY, CHANNEL.MORELINK_TEXT, MISC_CHANNEL.HOSPITAL_ID, MISC_CHANNEL.URL_KEYWORDS, MISC_CHANNEL.DESCRIPTION AS MISC_DESCRIPTION, MISC_CHANNEL.RESULTS, MISC_CHANNEL.SEARCH_RESULTS, MISC_CHANNEL.RELATION, MISC_CHANNEL.XML_FILE_PATH FROM REL, DC, MAP, ARTICLE, CHANNEL, NODE, MISC_CHANNEL WHERE CHANNEL.CH_ID <> 4 AND ARTICLE.ART_ID = REL.ART_ID AND ARTICLE.CH_ID = CHANNEL.CH_ID AND REL.ND_ID = NODE.ND_ID AND MAP.MP_ID = NODE.MP_ID AND MAP.DC_ID = DC.DC_ID AND NODE.ACTIVATION_STATUS > 0 AND NODE.CODING_STATUS > 0 AND ARTICLE.ART_ID = MISC_CHANNEL.ART_ID(+)
Any criticism in the query design is welcome. I dont want to use a view of all these tables joined offline and run my query off this unnormalised view.
Thanks,
Sumeer.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31069
    
232

You could probably make the actual database processing time faster by rearranging you where clause. It is generally better to put the joins last (unless your status codes are almost always positive numbers.) Also, you can look into adding appropriate indexes to make the query faster. The majority of your problem is not coming from the database execution time if it performs at an acceptable speed from the command line. But every little bit helps!
If the query runs at an acceptable speed at the command line, the problem is due to the amount of data you are sending across the network. Why do you need so much data in your java program? 170,000 rows * 30 columns is more than a million data fields! I suspect you don't really need the id of the table as it is for the database. To sum it up, make sure you are using every single field you are returning.


[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
s khosa
Ranch Hand

Joined: May 15, 2003
Posts: 72
I am trying to write a search engine. For the user to be able search across entire database, i need create search document indexes of all the five tables. I agree that putting any kind of filters before hand in 'where' clause does help and so does having indexes on columns being fetched.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Designing a multiple Join query