Home » Developer & Programmer » Forms » count and sum making a query slow
count and sum making a query slow [message #276735] Fri, 26 October 2007 03:13 Go to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
I want to run a query from D2K,

SELECT COUNT(APP_NO), SUM(AMNT_REIM)
FROM MED_QUERY2
WHERE APP_DATE >= '01-APR-07'
AND APP_DATE <= '30-APR-07'
AND APP_NO IS NOT NULL
AND KSO ='N'....... here MED_QUERY2 is a view ...this count and sum making this query very slow....but when i run the same query with only * then results comes very fast....

I am not able to understand why? plzz help me..
Re: count and sum making a query slow [message #276738 is a reply to message #276735] Fri, 26 October 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read How to Identify Performance Problem and Bottleneck and OraFAQ Forum Guide and provide the requested information.

Regards
Michel

Re: count and sum making a query slow [message #276744 is a reply to message #276738] Fri, 26 October 2007 04:03 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
thanx or ur reply michel.....just tell me what information u need to give the solution..?
Re: count and sum making a query slow [message #276817 is a reply to message #276744] Fri, 26 October 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read some of the topics about SQL tuning and then you see what is needed. In addition, you will learn a lot.

Regards
Michel
Re: count and sum making a query slow [message #277101 is a reply to message #276817] Sun, 28 October 2007 22:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you SELECT *, the FIRST ROW is returned very quickly, but it will take a long time to return EVERY row.

COUNT/SUM require that every row is retrieved from disk before a single row is displayed. It's not actually slower, it just seems that way.

Ross Leishman
Re: count and sum making a query slow [message #277132 is a reply to message #276735] Mon, 29 October 2007 01:20 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
then what is the solution? plzz help me..
Re: count and sum making a query slow [message #277148 is a reply to message #277132] Mon, 29 October 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

then what is the solution? plzz help me..

It will surely not come if you don't read the rules and still use IM speak.

Regards
Michel
Re: count and sum making a query slow [message #277152 is a reply to message #277148] Mon, 29 October 2007 02:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run a SQL Trace and post your TK*Prof output. What is the expected performance? See the Oracle Performance Tuning Guide for instructions.

Ross Leishman
Re: count and sum making a query slow [message #277157 is a reply to message #276735] Mon, 29 October 2007 02:16 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
actually i dont have much knowledge in database...what is "SQL Trace" and "post your TK*Prof output" ??
Re: count and sum making a query slow [message #277162 is a reply to message #277157] Mon, 29 October 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/db102.federated_search

Regards
Michel
Re: count and sum making a query slow [message #277180 is a reply to message #276735] Mon, 29 October 2007 02:54 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
in orace 9i there is one console manager, using it i can get the cost or performance of a query. is there any utility tool in d2k?
Re: count and sum making a query slow [message #277423 is a reply to message #276735] Tue, 30 October 2007 01:13 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
i am now sending you the explain plan of the query that is making the view


SELECT
B.EMP_APP_NO,D.EMP_APP_DATE,B.EMP_APP_REIM_DT,C.EMP_TYPE,C.EMP_DESIG,C.EMP_DEPT,C.SUB_CAT,
C.EMP_PLACE,C.EMP_PROFIT_CENTER,A.EMP_PER_NO,A.EMP_RELATION_NAME,A.EMP_RELATION,
A.EMP_R_GENDER,A.EMP_RELATION_AGE,GET_AILMENTS(NVL(A.EMP_RELATION_ALIMENT,'0')) AILMENTS
,D.EMP_REIM_AMT RIM,A.EMP_HOSPITAL,B.STATUS,B.EXCHL_FLG,A.EMP_DEP_PLACE,
C.KSO,B.INHOUSE,E.HEAD_DESC, E.HEAD_TYPE_DESC,
GET_PROCEDURE(B.EMP_APP_NO,A.EMP_PER_NO,A.EMP_RELATION_NAME,A.EMP_RELATION) PROCEDURE_NM
,B.EUSER
FROM
TTD_EMP_MEDICAL A
, TT_EMP_MEDICAL B
, TM_EMP C
, TTD_EXP_DTL D
, TM_HEAD E
WHERE B.EMP_PER_NO = A.EMP_PER_NO
AND B.EMP_APP_NO = A.EMP_APP_NO
AND C.EMP_PER_NO = B.EMP_PER_NO
AND B.EMP_PER_NO = D.EMP_PER_NO
AND B.EMP_APP_NO = D.EMP_APP_NO
AND B.EMP_APP_DATE = D.EMP_APP_DATE
AND (A.EMP_RELATION_NAME||A.EMP_RELATION) = (D.EMP_RELATION_NAME||D.EMP_RELATION_TYPE)
AND A.EMP_RELATION = D.EMP_RELATION_TYPE
AND B.EMP_APP_STATUS = 'Y'
AND (D.EMP_HEAD||D.EMP_HEAD_TYPE) = (E.HEAD_CODE||E.HEAD_TYPE_CODE)
AND D.EMP_HEAD_TYPE = E.HEAD_TYPE_CODE


explain plan is attached.

  • Attachment: untitled.bmp
    (Size: 364.76KB, Downloaded 191 times)
Re: count and sum making a query slow [message #277540 is a reply to message #277423] Tue, 30 October 2007 08:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You seem to have sent us the second half of the explain plan.

The joins of the type (A.col_1||A.col2) = (B.col_1||B.col_2) will mean that indexes on col_1 or col_2 won't be used.

What does the function GET_PROCEDURE do?
Re: count and sum making a query slow [message #278165 is a reply to message #276735] Fri, 02 November 2007 03:34 Go to previous messageGo to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
actually i am using D2K and im getting following errors,

FRM-41840
FRM-40900
FRM-40507

I also increase the block's "Number of record buffered" value upto 800000. but the errors still showing.

Plzz help me regardng this,

[Updated on: Fri, 02 November 2007 03:38]

Report message to a moderator

Re: count and sum making a query slow [message #278196 is a reply to message #278165] Fri, 02 November 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
These are Forms error, I move the topic to this forum.

Regards
Michel
Re: count and sum making a query slow [message #279522 is a reply to message #278196] Thu, 08 November 2007 19:25 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please post the messages associated with these errors. Have you done a site-wide search on these errors? Have you googled them?

David
Previous Topic: forms
Next Topic: How to send SMS ?
Goto Forum:
  


Current Time: Sat Dec 03 20:30:08 CST 2016

Total time taken to generate the page: 0.09798 seconds