Re: Query tuning

rajesh_at_solutionsoftware.com wrote in message news:<aa8abed4.0304072132.65cddb37_at_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

There is an idea: post the execution plan, please. Pretty hard to do anything without it.
Distinct clause is a little bit disturbing. Did you try to run query without it? How many rows do you get without distinct? And if you get much more rows without distinct, there is something wrong with your join condition. Most probably, some conditions are missing.

> 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
Received on Tue Apr 08 2003 - 10:08:50 CDT