File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Need help - Lot of joins are slowing down my query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need help - Lot of joins are slowing down my query" Watch "Need help - Lot of joins are slowing down my query" New topic
Author

Need help - Lot of joins are slowing down my query

Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
I have a complex SQL query with a lot of joins...
As a result it takes a lot of time to fetch the data from the tables...

Any performance tuning tips...other than indexing....

Thanks in advance
Mansi

http://splashpress.blogspot.com
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
There is nothing other than indexing. Doing it without indexing makes absolutely no sense. That's exactly where they are for.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

There are some good books on Query Optimization.
They give you tips on what to index, and on how to write performant joins.
It is possible to write performant queries with tens of joins, if done properly.

OCUP UML fundamental and ITIL foundation
youtube channel
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
you can cache the result
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Leandro Coutinho wrote:you can cache the result
With other words, apply indexes.
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
Bauke Scholtz wrote:
Leandro Coutinho wrote:you can cache the result
With other words, apply indexes.


I meant using a variable in an app
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
That would only be slower and more memory hogging.
Why would you take over the work of the DB in Java?
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Mansi,

you did not tell against which DB your query is executed - and not too many details about the query / tables, etc.

Well, I'm only used to Oracle, so maybe most of my hints won't be applicable to your situation.

I agree with my pre-posters that indexes may be one answer. But creating indexes is just one part of the solution, the optimizer has to use them, too...

With a rule based optimizer in the old Oracle versions (before version 8 or 9) it was a lot more work to force the optimizer to do what you want, but when you got it the execution plan was stable and good as long as the "structure" of the data (the distribution of the amount of data between the tables) stayed the same.

With the now commonly used cost based optimizer the execution plan is auto tuned by Oracle, which sometimes goes terribly wrong.
The Optimizer uses statistics and histograms, etc. for this.. As I said, I don't know anything about your data, but e.g. one common problem if you have very dynamic data may be that the optimizer considers your statistics to be stale - in this case a default assumption about the quality of your indexes is used - and this usually causes very bad execution plans (I had this case not so long ago where a query which usually takes 1 or two seconds took several minutes).


Other things which prevent the use of indexes are for instance selects like
select xx from yyy where y_executed != 'Y'
instead of
select xx from yyy where y_executed = 'N'

What I want to say is that if possible always =, like, etc. should be preferred over !=, not like, etc.


Another idea may be to change the query itself.. There is often more than one way to the same result.
Sometimes it is also possible to split one large select into some smaller ones, because the execution time may increase dramatically, if you includes lots of large tables into one select.



I hope some of these tips may help you a little.


Regards,

John
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Mansi,
Posting the query here might give you more useful comments.


[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
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
John Ben wrote:
Other things which prevent the use of indexes are for instance selects like
select xx from yyy where y_executed != 'Y'
instead of
select xx from yyy where y_executed = 'N'

What I want to say is that if possible always =, like, etc. should be preferred over !=, not like, etc.

makes sense. good to know it!
what's your source (have some link)?
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
Thanks guys for the replies...

Let me post my query which might help getting more useful solutions...
I have four tables namely USER_INFO,ENTITLEMENT ,TEAM and PRODUCT_HIERARCHY
I need to fetch user information from the USER_INFO table, his entitlements from the ENTITLEMENT table(teams and products at any level from 1-6)...and since the ENTITLEMENT table only has ID's for team and products, I need to get the respective names from the TEAM and PRODUCT_HIERARCHY tables.

The below query works well, just that it takes good 90 seconds to display the results.
I understand that the tables are not normalized well and there are no adequate no. of foreign keys, but that's all we have.


Here is the query...



John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Leandro,

unfortunately I can't provide a link for this, it's more a "lessions learned" thing and it is true primarily for indexed columns.
But I think this will also have some effect for non indexed columns...

If != or not like or something like this is used the index will not be used, another way a issue may not be used is to perform an operation with the column in the where criterion, e.g. select xx from yy where upper(x_a) = 'SOME_VALUE' will typically prevent the use of an index on column x_a.

By the way you can use this intentionally if the optimizer always tries to use an index which is not appropriate for your query. Then "hiding" a column from the optimizer by performing an operation on it has sometimes helped.

I found just one link which may give you some other hints how to tune your statements. This is basically a description of the tool Oracle Tuning Pack, but there is a section "Understanding Rules-of-Thumb" which contains some rules which may help.

http://download.oracle.com/docs/cd/B10501_01/em.920/a86647/vmqtune.htm


John

John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Mansi,

after I saw your query my first guess was that your problem may result from the use of many outer joins.

But you may need them, so it's very hard to give you a good advice.

If it is possible I would start to rearrange my query to get rid of some of the outer joins and try to use some inner joins instead.

At least you have a working select where you can test if your changed statements will give you the same result..


John
Vedhas Pitkar
Ranch Hand

Joined: Jan 27, 2001
Posts: 445
hi Mansi,

have you run an explain plan to find out if there's any full table scan?

Profile & see if any tables are getting locked
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50

Thanks John...
I guess i would have to rearrange the query....
will keep your tips in mind...

Thanks again



John Ben wrote:Hi Mansi,

after I saw your query my first guess was that your problem may result from the use of many outer joins.

But you may need them, so it's very hard to give you a good advice.

If it is possible I would start to rearrange my query to get rid of some of the outer joins and try to use some inner joins instead.

At least you have a working select where you can test if your changed statements will give you the same result..


John
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

The number of outer joins is not the problem (although they all have their cost).
But your joins contain functions, like decode:

I am not convinced that you will ever get this performant, even if it would be the only outer join.

So I remain at my stance: it's not the number of joins that make a query not performant, but
the quality of your join conditions, and the right indexes.
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50

Thanks people

I tried many ways...and finally succeeded in getting it right.
The join was the problem...keeping the rest of the query the same, i just changed this part




the running time is now just 7 seconds...
replacing the -1 by null made most of the difference...though I wonder why???


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Could it be that teamProduct.PRODUCTID and teamProduct.TEAMID are indexed?
You removed the function around it, and now your database is able to use these indexes.
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
Jan Cumps wrote:Could it be that teamProduct.PRODUCTID and teamProduct.TEAMID are indexed?
You removed the function around it, and now your database is able to use these indexes.


I dont think so, these columns were never indexed
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

I dont think so, these columns were never indexed
Are they part of the primary key?
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
Jan Cumps wrote:
I dont think so, these columns were never indexed
Are they part of the primary key?


The columns teamProduct.PRODUCTID and teamProduct.TEAMID do not belong to any of the existing tables...
rather I create a table called teamProduct using the below mentioned query within the main query...




This little piece of query returns me 5 columns namely ENTID,TEAMATLEVEL,TEAMID,PRODUCTATLEVEL and PRODUCTID...
Then I use these columns to find out to which level of team or product the user is mapped to...

Hence they are not primary keys either
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Oh.

What you can do, is ask the database for the cost analyzis of your old and new query (look for explain plan).
Oracle will show how it's searching the tables, and how much each part of the search costs.
It should be possible to see then what makes the new query perform better than the old one.

Here is some info on optimization

Regards, Jan
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
Sure Jan
Thanks once again

Mansi
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
do you really need to use decode inside decode?
couldn' you use somth like this:



?
Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
Leandro Coutinho wrote:do you really need to use decode inside decode?
couldn' you use somth like this:



?


Damn!!!
This is interesting...lemme try this one

Mansi Mishra
Ranch Hand

Joined: Dec 26, 2008
Posts: 50
This works!!!
with an execution time of 1.2 seconds...

Thanks Leandro
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help - Lot of joins are slowing down my query