Toad World® Forums

Query refinement


#1

#2

Cathy,

It doesn’t really matter which tool you use. The performance of the query
will be dependent on the quality of the query to a large degree. If you want to
increase performance, you need to get comfortable with Execution Plans.

Here’s a whitepaper I wrote on the subject and it shows how you can use
Toad for help.

http://www.toadworld.com/Portals/0/ToadTechPapers/Explain%20This%20-%20Toad%20Whi
te%20Paper.pdf

Jeff


#3

you need to get comfortable with Execution Plans

Not just that, Cathy has a lot of learning to do.

Hey Cathy, code performance is a rather lengthy topic. A lot of the stuff you
will pick up will be experience over time. I don’t mean this statement to
be frustrating to you but to be a realization that what you just asked for help
on is a very complex subject. I think the only solid rule anyone can say about
performance without knowing your specific situation is:

“Everything is situational.”

Even reading an execution plan to decipher where you went wrong is going to be
situational.

For example, if your query returns a rather large set of the available data, 90%
for sure, you should almost definitely avoid the use of indexes and do at least
one full table scan. On the other hand, if your query returns a rather small
subset of the data, say 2%, then you should almost definitely use an index and
avoid full table scans.

Note the use of the “almost” portion of that :wink:

That example should tell you something beyond the rule that everything is
situational: understanding your data is very important to authoring high
performance queries. The more complicated the query, the more
“situational” type recommendations you are likely to receive.

Is there perhaps a fellow software developer you work with who is an old hand
with regards SQL optimization who would be willing to be a “performance
mentor”?

RAS


#4

Morning all,

Not just that, Cathy has a lot of learning to do.
True, but if I may be so bold, when she does know how to code SQL, I
would advise getting a copy of Guy Harrison’s book “Oracle SQL High
Performance Tuning”.

It’s not for absolute beginners, but once you can write SQL, read it and
learn from it.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk