Need help - Lot of joins are slowing down my query

[Mansi Mishra]

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]

There is nothing other than indexing. Doing it without indexing makes absolutely no sense. That's exactly where they are for.

[Jan Cumps]

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]

you can cache the result

[Bauke Scholtz]

Leandro Coutinho wrote:you can cache the result

With other words, apply indexes.

[Leandro Coutinho]

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]

That would only be slower and more memory hogging.
Why would you take over the work of the DB in Java?

[John Bengler]

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]

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

[Leandro Coutinho]

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]

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...

  select distinct   ent_link.id AS ENT_ID,
			   userInfo.ID,
			   userInfo.user_sid,
			   userInfo.FIRST_NAME,
			   userInfo.LAST_NAME,   
			   nvl(userInfo.TEAM_PRODUCT_LINK,' ') as TEAM_PRODUCT_LINK, 


			   ( case when teamProduct.teamAtLevel = 6 then nvl(dimTeam.TEAM_LEVEL_6_id,-1) else -1 end ) as TEAM_LEVEL_6_id,
			   ( case when teamProduct.teamAtLevel = 5 then nvl(dimTeam.TEAM_LEVEL_5_id,-1) else -1 end ) as TEAM_LEVEL_5_id,
			   ( case when teamProduct.teamAtLevel = 4 then nvl(dimTeam.TEAM_LEVEL_4_id,-1) else -1 end ) as TEAM_LEVEL_4_id,
			   ( case when teamProduct.teamAtLevel = 3 then nvl(dimTeam.TEAM_LEVEL_3_id,-1) else -1 end ) as TEAM_LEVEL_3_id,
			   ( case when teamProduct.teamAtLevel = 2 then nvl(dimTeam.TEAM_LEVEL_2_id,-1) else -1 end ) as TEAM_LEVEL_2_id,
			   ( case when teamProduct.teamAtLevel = 1 then nvl(dimTeam.TEAM_LEVEL_1_id,-1) else -1 end ) as TEAM_LEVEL_1_id,
			   
 			   ( case when teamProduct.teamAtLevel = 6 then nvl(TEAM_LEVEL_6_nm,' ') else ' ' end ) as TEAM_LEVEL_6_nm,
			   ( case when teamProduct.teamAtLevel = 5 then nvl(TEAM_LEVEL_5_nm,' ') else ' '  end ) as TEAM_LEVEL_5_nm,
			   ( case when teamProduct.teamAtLevel = 4 then nvl(TEAM_LEVEL_4_nm,' ') else ' ' end ) as TEAM_LEVEL_4_nm,
			   ( case when teamProduct.teamAtLevel = 3 then nvl(TEAM_LEVEL_3_nm,' ') else ' ' end ) as TEAM_LEVEL_3_nm,
			   ( case when teamProduct.teamAtLevel = 2 then nvl(TEAM_LEVEL_2_nm,' ') else ' ' end ) as TEAM_LEVEL_2_nm,
			   ( case when teamProduct.teamAtLevel = 1 then nvl(TEAM_LEVEL_1_nm,' ') else ' ' end ) as TEAM_LEVEL_1_nm,
  
			   ( case when teamProduct.PRODUCTATLEVEL = 6 then nvl(dimProduct.PRODUCT_LEVEL_6_ID, -1) else -1 end ) as PRODUCT_LEVEL_6_ID,
			   ( case when teamProduct.PRODUCTATLEVEL = 5 then nvl(dimProduct.PRODUCT_LEVEL_5_ID, -1) else -1 end ) as PRODUCT_LEVEL_5_ID,
			   ( case when teamProduct.PRODUCTATLEVEL = 4 then nvl(dimProduct.PRODUCT_LEVEL_4_ID, -1) else -1 end ) as PRODUCT_LEVEL_4_ID,
			   ( case when teamProduct.PRODUCTATLEVEL = 3 then nvl(dimProduct.PRODUCT_LEVEL_3_ID, -1) else -1 end ) as PRODUCT_LEVEL_3_ID,
			   ( case when teamProduct.PRODUCTATLEVEL = 2 then nvl(dimProduct.PRODUCT_LEVEL_2_ID, -1) else -1 end ) as PRODUCT_LEVEL_2_ID,
			   ( case when teamProduct.PRODUCTATLEVEL = 1 then nvl(dimProduct.PRODUCT_LEVEL_1_ID, -1) else -1 end ) as PRODUCT_LEVEL_1_ID,
			   
			   ( case when teamProduct.productAtLevel = 6 then nvl(PRODUCT_LEVEL_6_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_6_NM,
			   ( case when teamProduct.productAtLevel = 5 then nvl(PRODUCT_LEVEL_5_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_5_NM,
			   ( case when teamProduct.productAtLevel = 4 then nvl(PRODUCT_LEVEL_4_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_4_NM,
			   ( case when teamProduct.productAtLevel = 3 then nvl(PRODUCT_LEVEL_3_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_3_NM,
			   ( case when teamProduct.productAtLevel = 2 then nvl(PRODUCT_LEVEL_2_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_2_NM,
			   ( case when teamProduct.productAtLevel = 1 then nvl(PRODUCT_LEVEL_1_NM, ' ') else ' ' end ) as PRODUCT_LEVEL_1_NM
 

 from  USER_INFO userInfo 
 left outer join 
ENTITLEMENT ent_link
 on userInfo.user_sid=ent_link.team_product_link 

LEFT OUTER join
(
 	
 	select ent_link.ID AS entId,decode(ent_link.TEAM_LEVEL_6_ID,null, decode(ent_link.TEAM_LEVEL_5_ID,null,decode(ent_link.TEAM_LEVEL_4_ID,null, decode(ent_link.TEAM_LEVEL_3_ID
	,null, decode(ent_link.TEAM_LEVEL_2_ID,null, decode(ent_link.TEAM_LEVEL_1_ID,null,-1,1),2),3),4),5),6) as teamAtLevel,
	nvl(ent_link.TEAM_LEVEL_6_ID, nvl(ent_link.TEAM_LEVEL_5_ID,nvl(ent_link.TEAM_LEVEL_4_ID,(
	nvl(ent_link.TEAM_LEVEL_3_ID,nvl(ent_link.TEAM_LEVEL_2_ID,nvl(ent_link.TEAM_LEVEL_1_ID,-1))))))) as teamId,
	decode(ent_link.PRODUCT_LEVEL_6_ID,null, decode(ent_link.PRODUCT_LEVEL_5_ID,null,decode(ent_link.PRODUCT_LEVEL_4_ID,null, decode(ent_link.PRODUCT_LEVEL_3_ID
	,null, decode(ent_link.PRODUCT_LEVEL_2_ID,null, decode(ent_link.PRODUCT_LEVEL_1_ID,null,-1,1),2),3),4),5),6) as productAtLevel,
	nvl(ent_link.PRODUCT_LEVEL_6_ID, nvl(ent_link.PRODUCT_LEVEL_5_ID,nvl(ent_link.PRODUCT_LEVEL_4_ID,(
	nvl(ent_link.PRODUCT_LEVEL_3_ID,nvl(ent_link.PRODUCT_LEVEL_2_ID,nvl(ent_link.PRODUCT_LEVEL_1_ID,-1))))))) as productId
	from  ENTITLEMENT ent_link  where ent_link.team_product_link =:USER_SID
) teamProduct on teamProduct.entId=ent_link.ID
  


 left outer join PRODUCT_HIERARCHY dimProduct ON 		

decode(teamProduct.PRODUCTATLEVEL,6,ent_link.PRODUCT_LEVEL_6_id,
decode(teamProduct.PRODUCTATLEVEL,5,ent_link.PRODUCT_LEVEL_5_id,
decode(teamProduct.PRODUCTATLEVEL,4,ent_link.PRODUCT_LEVEL_4_id,
decode(teamProduct.PRODUCTATLEVEL,3,ent_link.PRODUCT_LEVEL_3_id,
decode(teamProduct.PRODUCTATLEVEL,2,ent_link.PRODUCT_LEVEL_2_id,
decode(teamProduct.PRODUCTATLEVEL,1,ent_link.PRODUCT_LEVEL_1_id,-1))))))
  	
   =
decode(teamProduct.PRODUCTATLEVEL,6,dimProduct.PRODUCT_LEVEL_6_id,
decode(teamProduct.PRODUCTATLEVEL,5,dimProduct.PRODUCT_LEVEL_5_id,
decode(teamProduct.PRODUCTATLEVEL,4,dimProduct.PRODUCT_LEVEL_4_id,
decode(teamProduct.PRODUCTATLEVEL,3,dimProduct.PRODUCT_LEVEL_3_id,
decode(teamProduct.PRODUCTATLEVEL,2,dimProduct.PRODUCT_LEVEL_2_id,
decode(teamProduct.PRODUCTATLEVEL,1,dimProduct.PRODUCT_LEVEL_1_id,-1))))))

LEFT OUTER JOIN TEAM dimTeam ON 
 
decode(teamProduct.teamAtLevel,6,ent_link.TEAM_LEVEL_6_id,
decode(teamProduct.teamAtLevel,5,ent_link.TEAM_LEVEL_5_id,decode(teamProduct.teamAtLevel,4,ent_link.TEAM_LEVEL_4_id,decode(teamProduct.teamAtLevel,3,ent_link.TEAM_LEVEL_3_id,decode(teamProduct.teamAtLevel,2,ent_link.TEAM_LEVEL_2_id,decode(teamProduct.teamAtLevel,1,ent_link.TEAM_LEVEL_1_id,-1))))))
  	
   =
decode(teamProduct.teamAtLevel,6,dimTeam.TEAM_LEVEL_6_id,
decode(teamProduct.teamAtLevel,5,dimTeam.TEAM_LEVEL_5_id,
decode(teamProduct.teamAtLevel,4,dimTeam.TEAM_LEVEL_4_id,
decode(teamProduct.teamAtLevel,3,dimTeam.TEAM_LEVEL_3_id,
decode(teamProduct.teamAtLevel,2,dimTeam.TEAM_LEVEL_2_id,
decode(teamProduct.teamAtLevel,1,dimTeam.TEAM_LEVEL_1_id,-1))))))
  	
  
WHERE  userInfo.TEAM_PRODUCT_LINK ='Y' AND userInfo.logical_delete is null and ent_link.LOGICAL_DELETE IS NULL AND ent_link.TEAM_PRODUCT_LINK IS NOT NULL
AND userInfo.user_sid=:USER_SID  

[John Bengler]

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]

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]

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]

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]

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

left outer join PRODUCT_HIERARCHY dimProduct ON           
  
decode(teamProduct.PRODUCTATLEVEL,6,ent_link.PRODUCT_LEVEL_6_id,   
decode(teamProduct.PRODUCTATLEVEL,5,ent_link.PRODUCT_LEVEL_5_id,   
decode(teamProduct.PRODUCTATLEVEL,4,ent_link.PRODUCT_LEVEL_4_id,   
decode(teamProduct.PRODUCTATLEVEL,3,ent_link.PRODUCT_LEVEL_3_id,   
decode(teamProduct.PRODUCTATLEVEL,2,ent_link.PRODUCT_LEVEL_2_id,   
decode(teamProduct.PRODUCTATLEVEL,1,ent_link.PRODUCT_LEVEL_1_id,-1))))))   
       
   =   
decode(teamProduct.PRODUCTATLEVEL,6,dimProduct.PRODUCT_LEVEL_6_id,   
decode(teamProduct.PRODUCTATLEVEL,5,dimProduct.PRODUCT_LEVEL_5_id,   
decode(teamProduct.PRODUCTATLEVEL,4,dimProduct.PRODUCT_LEVEL_4_id,   
decode(teamProduct.PRODUCTATLEVEL,3,dimProduct.PRODUCT_LEVEL_3_id,   
decode(teamProduct.PRODUCTATLEVEL,2,dimProduct.PRODUCT_LEVEL_2_id,   
decode(teamProduct.PRODUCTATLEVEL,1,dimProduct.PRODUCT_LEVEL_1_id,-1))))))   

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]

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

 left outer join PRODUCT_HIERARCHY dimProduct ON 		

teamProduct.PRODUCTID
=
decode(teamProduct.PRODUCTATLEVEL,6,dimProduct.PRODUCT_LEVEL_6_id,
decode(teamProduct.PRODUCTATLEVEL,5,dimProduct.PRODUCT_LEVEL_5_id,
decode(teamProduct.PRODUCTATLEVEL,4,dimProduct.PRODUCT_LEVEL_4_id,
decode(teamProduct.PRODUCTATLEVEL,3,dimProduct.PRODUCT_LEVEL_3_id,
decode(teamProduct.PRODUCTATLEVEL,2,dimProduct.PRODUCT_LEVEL_2_id,
decode(teamProduct.PRODUCTATLEVEL,1,dimProduct.PRODUCT_LEVEL_1_id,[b]null[/b]))))))

LEFT OUTER JOIN TEAM dimTeam ON 
 
teamProduct.TEAMID
   =
decode(teamProduct.teamAtLevel,6,dimTeam.TEAM_LEVEL_6_id,
decode(teamProduct.teamAtLevel,5,dimTeam.TEAM_LEVEL_5_id,
decode(teamProduct.teamAtLevel,4,dimTeam.TEAM_LEVEL_4_id,
decode(teamProduct.teamAtLevel,3,dimTeam.TEAM_LEVEL_3_id,
decode(teamProduct.teamAtLevel,2,dimTeam.TEAM_LEVEL_2_id,
decode(teamProduct.teamAtLevel,1,dimTeam.TEAM_LEVEL_1_id,[b]null[/b]))))))   

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

[Jan Cumps]

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]

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]

I dont think so, these columns were never indexed

Are they part of the primary key?

[Mansi Mishra]

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...

 
(
 	
 	select ent_link.ID AS entId,decode(ent_link.TEAM_LEVEL_6_ID,null, decode(ent_link.TEAM_LEVEL_5_ID,null,decode(ent_link.TEAM_LEVEL_4_ID,null, decode(ent_link.TEAM_LEVEL_3_ID
	,null, decode(ent_link.TEAM_LEVEL_2_ID,null, decode(ent_link.TEAM_LEVEL_1_ID,null,-1,1),2),3),4),5),6) as teamAtLevel,
	nvl(ent_link.TEAM_LEVEL_6_ID, nvl(ent_link.TEAM_LEVEL_5_ID,nvl(ent_link.TEAM_LEVEL_4_ID,(
	nvl(ent_link.TEAM_LEVEL_3_ID,nvl(ent_link.TEAM_LEVEL_2_ID,nvl(ent_link.TEAM_LEVEL_1_ID,-1))))))) as teamId,
	decode(ent_link.PRODUCT_LEVEL_6_ID,null, decode(ent_link.PRODUCT_LEVEL_5_ID,null,decode(ent_link.PRODUCT_LEVEL_4_ID,null, decode(ent_link.PRODUCT_LEVEL_3_ID
	,null, decode(ent_link.PRODUCT_LEVEL_2_ID,null, decode(ent_link.PRODUCT_LEVEL_1_ID,null,-1,1),2),3),4),5),6) as productAtLevel,
	nvl(ent_link.PRODUCT_LEVEL_6_ID, nvl(ent_link.PRODUCT_LEVEL_5_ID,nvl(ent_link.PRODUCT_LEVEL_4_ID,(
	nvl(ent_link.PRODUCT_LEVEL_3_ID,nvl(ent_link.PRODUCT_LEVEL_2_ID,nvl(ent_link.PRODUCT_LEVEL_1_ID,-1))))))) as productId
	from  ENTITLEMENT ent_link  where ent_link.team_product_link =:USER_SID
)

  

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]

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]

Sure Jan
Thanks once again

Mansi

[Leandro Coutinho]

do you really need to use decode inside decode?
couldn' you use somth like this:

decode(teamProduct.PRODUCTATLEVEL,6,dimProduct.PRODUCT_LEVEL_6_id, 5, dimProduct.PRODUCT_LEVEL_5_id,  
	4, dimProduct.PRODUCT_LEVEL_4_id, 3, dimProduct.PRODUCT_LEVEL_3_id,  
	2, dimProduct.PRODUCT_LEVEL_2_id, 1, dimProduct.PRODUCT_LEVEL_1_id, null)

?

[Mansi Mishra]

Leandro Coutinho wrote:do you really need to use decode inside decode?
couldn' you use somth like this:

decode(teamProduct.PRODUCTATLEVEL,6,dimProduct.PRODUCT_LEVEL_6_id, 5, dimProduct.PRODUCT_LEVEL_5_id,  
	4, dimProduct.PRODUCT_LEVEL_4_id, 3, dimProduct.PRODUCT_LEVEL_3_id,  
	2, dimProduct.PRODUCT_LEVEL_2_id, 1, dimProduct.PRODUCT_LEVEL_1_id, null)

?

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

[Mansi Mishra]

This works!!!
with an execution time of 1.2 seconds...

Thanks Leandro