Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help - Lot of joins are slowing down my query

 
Mansi Mishra
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is nothing other than indexing. Doing it without indexing makes absolutely no sense. That's exactly where they are for.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Leandro Coutinho
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can cache the result
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Leandro Coutinho wrote:you can cache the result
With other words, apply indexes.
 
Leandro Coutinho
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mansi,
Posting the query here might give you more useful comments.
 
Leandro Coutinho
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I dont think so, these columns were never indexed
Are they part of the primary key?
 
Mansi Mishra
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sure Jan
Thanks once again

Mansi
 
Leandro Coutinho
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do you really need to use decode inside decode?
couldn' you use somth like this:



?
 
Mansi Mishra
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This works!!!
with an execution time of 1.2 seconds...

Thanks Leandro
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic