query tuning

Here is a silly question. Why are you doing so many outer joins? :confused:

I'm guessing that Jeff is trying to make it clear that the query is slow because of the number of full table scans and nested loops. Therefore reducing the number of outer joins would reduce the number of nested loops and full table scans. Which will make the query faster. Or you can jack up you sort_area_size. But even then, this query is going to be slow.

IMHO, there must be something wrong with your schema, if you have to write sql like this.

Quote:

---

Originally posted by gandolf989
I'm guessing that Jeff is trying to make it clear that the query is slow because

---

I just posted the attachment inline, that's all....

C-I-L-L

The Develop ers.

Quote:

---

Originally posted by Mr.Hanky
C-I-L-L

The Develop ers.

---

Flush the poo!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
:D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D

Nothing much can be done on nested of outer joins .Secondly Since it is not seeking indexes . We need to create function based indexes based on where clause. Even if they have indexes no use.. on filters since he is using functions like Upper etc.

I agree with raghud. The other thing that stands out is that most of the data is coming from other views. Which may or may not be tuned well. Maybe if he wrote a query to get exactly what he wants based on the underlying tables he will get faster query. Without knowing the schema it is hard to guess as to what would make this query faster.

This reminds me of a database I dealt with previously. Power Builder developers would join views and tables from the underlying views together, so the same table might get referenced 5 times in the same query from different views. I rewrote some of that to simplify the joins and that helped.

And don't even start Mr. Hanky. ;)

Thanks for the input.

If the developer can use some way to put the data originally
from the view into some temporary tables and
then does query from them, is this helpful?

[GUESS]Is this a case where the views present sub-sets of the data and this query is putting them back together again ! ? [/GUESS]
I suspect it requires rethinking, not tuning - and in this case, rethinking in terms of the "base" tables (as gandolf suggests).