| Performance problem [message #132210] |
Thu, 11 August 2005 06:23  |
Manav
Messages: 8
Registered: July 2005
|
Junior Member |
|
|
Hi,
I have executed the following query
SELECT COUNT(*) PERS, REGION FROM EMPHRDATA Emp
WHERE EXISTS ( SELECT Emp_no FROM Timecard TIME
WHERE Week_End_date = '21-MAY-2005' AND TIME.Emp_no = Emp.Emp_No )
GROUP BY REGION
It will take 13 minutes to fetch the data.
I am using 3 tables
EMPHRDATA,EMPLOYEE, TIMECARD
EMPHRDATA contains 21000 records, EMPLOYEE-5000 records and
TIMECARD - 3000 records.
How can improve performance?
Regards
Naveen
|
|
|
|
|
|
| Re: Performance problem [message #132282 is a reply to message #132210] |
Thu, 11 August 2005 13:44  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yes, check that you are using actual dates and not character strings.
Also, why are you using a where exists, that just does a join back to the first table, rather than using a regular query with normal joins and no exists clause? You are just getting a count of the employees per region who have an end date equal to your date given, right?
And I only see 2 tables not 3. Oh and make sure you have statistics gathered, and it may or may not help to have an index on your date field used in the comparison.
|
|
|
|
| Re: Performance problem [message #132327 is a reply to message #132282] |
Thu, 11 August 2005 22:44 |
Manav
Messages: 8
Registered: July 2005
|
Junior Member |
|
|
Hi,
How can i write this query without exists clause?
SELECT COUNT(*) PERS, REGION FROM EMPHRDATA Emp
WHERE EXISTS ( SELECT Emp_no FROM Timecard TIME
WHERE Week_End_date = '21-MAY-2005' AND TIME.Emp_no = Emp.Emp_No )
GROUP BY REGION
Regards
Naveen
|
|
|
|
| Re: Performance problem [message #132416 is a reply to message #132210] |
Fri, 12 August 2005 11:14 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I don't know enough about your situation to know for sure, so this is just a guess:
select region, count(*)
from emp, time
where time.emp_no = emp.emp_no
and time.week_end_date = to_date('21-MAY-2005')
group by region;
|
|
|
|
| Re: Performance problem [message #132532 is a reply to message #132416] |
Sun, 14 August 2005 02:03 |
Manav
Messages: 8
Registered: July 2005
|
Junior Member |
|
|
Hi,
My requirement is to search the number of employees who has entered their timecard in the current week(week end date) in each region.
I will get week end date field from timecard table and region from emphrdata table. Join based on the emp_no in both tables.
here emp_no is primary key.
Regards
Naveen
|
|
|
|
| Re: Performance problem [message #132551 is a reply to message #132532] |
Sun, 14 August 2005 12:20 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SELECT COUNT (DISTINCT Emp_no) PERS, REGION
FROM EMPHRDATA Emp, Timecard TIME
WHERE TIME.Emp_no = Emp.Emp_No
AND Week_End_date = TO_DATE ('21-MAY-2005', 'DD-MON-YYYY')
GROUP BY REGION;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
