OraFAQ Forum: Performance Tuning » Performance problem

Home » RDBMS Server » Performance Tuning » Performance problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Performance problem [message #132221 is a reply to message #132210]

Thu, 11 August 2005 07:27

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

What happens if you change the bit

Code: [Select all] [Show/ hide]

WHERE Week_End_date = '21-MAY-2005'

Code: [Select all] [Show/ hide]

WHERE week_end_date = TO_DATE('21-MAY-2005','DD-MON-YYYY')

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

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;

Report message to a moderator

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

Report message to a moderator

Re: Performance problem [message #132551 is a reply to message #132532]

Sun, 14 August 2005 12:20

Barbara Boehmer
Messages: 9105
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;

Report message to a moderator

Previous Topic:	open connection memory overhead
Next Topic:	Row chaining and Local Tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 21:25:40 CDT 2025