Re: Query tuning

rajesh_at_solutionsoftware.com wrote in news:aa8abed4.0304072132.65cddb37 @posting.google.com:

> Hi,
> Here is a query that I am executing in my application and it takes
> around 10 minutes to execute. Absolutely no idea how to proceed with
> fine tuning.
> The query is:
> -------------
> select
> distinct Agent.AgentId, Policy.appnumber, Agent.FullName,
> Customer.FullName,
> Address.Address1 ||', '|| Address2 Address, ContactNo.ContactNo,
> Policy.Appdate, MT1.Description Status, GetPolicyStatusDate(
> policy.policyid, 0, 'Issued') EFFECTIVEDATE,
> MT2.Description PaymentMode, Policy.ModalPremium,
> policy.annualpremium
> From
> Agent, Customer, Policy, Address, ContactNo, PolicyCustomer,
> AgentPolicy, MToption MT1, MTOption MT2
> Where
> Agent.AgentId = AgentPolicy.AgentID
> and Policy.PolicyId = Agentpolicy.PolicyId
> and decode(AgentPolicy.IssplitAgent, Null, 0,
> AgentPolicy.IssplitAgent) = 0
> and Policy.StatusId = MT1.optionID and Policy.PaymentModeId =
> MT2.OptionID
> and Policycustomer.policyid = Policy.PolicyID
> and Policycustomer.IsPrimary = 1
> and PolicyCustomer.CustomerID = Customer.CustomerID
> and Customer.CustomerID = Address.PersonID (+)
> and Address.IsPrimary (+) = 1 and Address.Forwhom (+) = 4
> and Customer.CustomerID = ContactNo.PersonID (+) and
> ContactNo.IsPrimary (+) = 1
> and ContactNo.Forwhom (+) = 4 AND (Upper(Agent.LastName) like
> 'ADA%' or Upper(Agent.FirstName) like 'ADA%')
> order by
> Agent.FullName
> Records in each of the table are:
> -----------------------------------
> Agent - Contains around 5000 records
> Customer - Contains around 100000 records
> Policy - Contains around 100000 records
> Address - Contains around 100000 records
> ContactNo - Contains around 100000 records
> PolicyCustomer - Contains around 100000 records
> AgentPolicy - Contains around 100000 records
> MToption MT1 - Contains around 400 records
> MTOption MT2 - Contains around 400 records
>
> At this juncture, I have absolutely no idea as to what is the
> direction in which I should proceed if I have to fine tune the query.
> Please help...
> Regards,
> Rajesh

Hello Rajesh,

Some things you could look into -

1. Get rid of the distinct. If you remove it do you get duplicates and why, it would seem that you shouldn't?
2. Get rid of the decode in the where clause, use (col = 0 or col is null) instead.
3. Ensure agentid, policyid, customerid, statusid etc columns are all indexed.
4. It may not speed up performance but you can reduce the number of outer joins in your where clause by creating two inline views (select ... from address where isprimary = 1 and forwhom = 4) (select ... from contactno where isprimary = 1 and forwhom = 4) It probably has about as much scientific basis as waving a rubber chicken at it, but it makes me feel better by removing 4 (+)'s.
5. You are ordering by full name but looking for the search string in either first or last name, it would be nice if you could search once in full name. Also see if you can use function based indexes here. (BTW does it make sense that the first *or* last name should *begin* with the search string, i.e. it doesn't matter which name its in but it needs to be at the beginning?)
6. Is the GetPolicyStatusDate function slow? Temporarily remove it to find out. Can it be made more efficient? Its not getting data from one of the policy tables already joined is it?

After that you could always try asking your DBA to set fast=true.

:)

Martin Received on Tue Apr 08 2003 - 19:21:27 CDT