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
ITIL foundation
Leandro Coutinho
Ranch Hand
Joined: Mar 04, 2009
Posts: 415
posted
0
you can cache the result
Bauke Scholtz
Ranch Hand
Joined: Oct 08, 2006
Posts: 2458
posted
0
Leandro Coutinho wrote:you can cache the result
With other words, apply indexes.
Leandro Coutinho
Ranch Hand
Joined: Mar 04, 2009
Posts: 415
posted
0
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
posted
0
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: 132
posted
0
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.
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
posted
0
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: 132
posted
0
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.
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
posted
0
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???
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
posted
0
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.
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...
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.
0
