Home » RDBMS Server » Performance Tuning » Performance problem
Performance problem [message #132210] Thu, 11 August 2005 06:23 Go to next message
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 #132221 is a reply to message #132210] Thu, 11 August 2005 07:27 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
What happens if you change the bit
WHERE Week_End_date = '21-MAY-2005'
to
WHERE week_end_date = TO_DATE('21-MAY-2005','DD-MON-YYYY')
?
Re: Performance problem [message #132282 is a reply to message #132210] Thu, 11 August 2005 13:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8922
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;
Previous Topic: open connection memory overhead
Next Topic: Row chaining and Local Tablespace
Goto Forum:
  


Current Time: Fri May 27 01:28:20 CDT 2022