Re: Need help in tuning a set of query.

From: <rtproffitt_at_my-deja.com>
Date: Mon, 27 Mar 2000 17:37:47 GMT
Message-ID: <8bo68j$e2g$1_at_nnrp1.deja.com>

Kenny,

Kenny,

Here are some suggestions...

1. Since the optimizer is CHOOSE, do you know if ANALYZE was run recently? Are the statistics up to date? I added an index to a table, only to have it not be used, until I ran ANALYZE for the table.
2. Are there indexes on employees.emp_seq time_sheets.proj_seq projects.name sal_history.emp_seq ????
3. You have written a correlated subquery, some 3 levels deep. The inner queries are forced to execute for EACH row of outer EMPLOYEES. While sometimes a correlated query can still be fast, generally they are slower... better to find a way to ask the question without this type of query.
4. I am not exactly certain the question you are solving...It would be helpful to add a paragraph which states the question, like... "...find all the employees who worked on Debugger, who...etc etc etc"

Here are some alternative queries
5. In-line table approach... idea,
keep memory small until the end, then
[Quoted] get detailed data.

a. Gather the Max(effective_dates)
b. Find all the employees who worked on debugger
c. See which employees dates match rpt_date.
d. Add on all the detail (I am assuming Employee

has a lot of detail columns)

[Select *... better: Select a,b,c,d,e...etc]

Select * from EMPLOYEES
Where Emp_Seq IN
(Select EmpProj.EmpSeq
From
(Select

      Emp_Seq  EmpSeq,
      Max(Effective_Date) EffDate

   From Sal_History
   Group by Emp_Seq
  ) EmpDates,
(Select

      ts.Emp_Seq  EmpSeq,
      pj.Proj_Seq ProjSeq,
      pj.Rpt_Date RptDate
   From Projects pj,
      Time_Sheets ts

   Where pj.Name = 'Debugger'
   And pj.Proj_Seq = ts.Proj_Seq
  ) EmpProj
Where EmpDates.EffDate = EmpProj.RptDate )

5b. One big JOIN approach....

Select e.A, e.B, e.C....etc,

    p.Rpt_Date, Max(sh.Effective_date)
From
  Projects p,
  Time_Sheets ts,
  Sal_History sh,
  Employees e

Where p.Proj_seq = ts.Proj_seq
And   ts.Emp_Seq = sh.Emp_Seq
And   sh.Emp_Seq = e.Emp_Seq

Group By
  e.A, e.B, e.C....etc,
  p.Rpt_Date
Having p.Rpt_date = Max(sh.Effective_Date)

Good Luck...
Comments welcome...anyone?

Robert Proffitt
Beckman Coulter
RTProffitt AT beckman DOT com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 27 2000 - 19:37:47 CEST