Home » RDBMS Server » Performance Tuning » Group By taking more time (merged)
Group By taking more time (merged) [message #480622] Tue, 26 October 2010 02:35 Go to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Experts,

If i am writing the code using group by and aggregator function it is taking 6 minutes.But, If i am not using aggregator and group by it is running in milli secs..

Can you please let me know why group by its taking more time. Is any thing wrong in the query ?

Below is the query:

SELECT
C_NUM,
C_L_NUM,
PRO_ID,
F_CODE,
ACC_PERIOD,
FISCAL_YEAR,
ACCOUNT,
Ana_Group,
SUM(RES_AMT) AS RP_RES FROM ( Select C_NUM, C_L_NUM, PRO_ID, F_CODE, O.ACC_PERIOD, O.FISCAL_YEAR, RES_AMT, ACCOUNT, ANA_TYPE from PROJ_RES A1, DETP_TBL O where A1.ACC_DT between O.BEGIN_DT AND O.END_DT AND O.CALENDAR_ID= 'FY'
)M1,
GRP_MAP N1
WHERE
M1.ANA_TYPE=N1.ANA_TYPE
AND  N1.ANA_GROUP = 'RP'
GROUP BY
C_NUM,
C_L_NUM,
F_CODE,
PRO_ID,
ACC_PERIOD,
FISCAL_YEAR,
ACCOUNT,
Ana_Group


Thanks,
Sri

Report message to a moderator

Re: Group By taking more time [message #480632 is a reply to message #480622] Tue, 26 October 2010 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think you have to do the same thing to:
1/ get the first one
2/ aggregate all rows
How much time it takes to return ALL rows?

Regards
Michel

Report message to a moderator

Re: Group By taking more time [message #480636 is a reply to message #480632] Tue, 26 October 2010 02:54 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Michel,

Thanks for you quick reply!!!

If i am using the below code without group by for all rows. I am getting the output in 903 milli secs.
SELECT
C_NUM,
C_L_NUM,
PRO_ID,
F_CODE,
ACC_PERIOD,
FISCAL_YEAR,
ACCOUNT,
Ana_Group
FROM ( Select C_NUM, C_L_NUM, PRO_ID, F_CODE, O.ACC_PERIOD, O.FISCAL_YEAR, RES_AMT, ACCOUNT, ANA_TYPE from PROJ_RES A1, DETP_TBL O where A1.ACC_DT between O.BEGIN_DT AND O.END_DT AND O.CALENDAR_ID= 'FY'
)M1,
GRP_MAP N1
WHERE
M1.ANA_TYPE=N1.ANA_TYPE
AND  N1.ANA_GROUP = 'RP'


--
Thanks,
Sri

Report message to a moderator

Re: Group By taking more time [message #480638 is a reply to message #480636] Tue, 26 October 2010 02:58 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Michel,

You are correct i am not checking all the rows. I am executing the code in toad. Sorry for troubling you.

--
Thanks,
Sri

Report message to a moderator

Re: Group By taking more time [message #480639 is a reply to message #480636] Tue, 26 October 2010 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting the output in 903 milli secs.

ALL the output, ALL the rows?
Note that I can't read what is after "from PROJ_RES A1" as it exceeds my window width.

Regards
Michel

Report message to a moderator

T-SQL taking much time [message #480772 is a reply to message #480622] Wed, 27 October 2010 01:55 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Experts,

If i am writing the code using group by and aggregator function it is taking 13 minutes.But, If i am not using aggregator and group by it is running in 4 minutes.

Can you please let me know why group by its taking more time. Is any thing wrong in the query ?

Below is the query:
SELECT
C_NUM,
C_L_NUM,
PRO_ID,
F_CODE,
ACC_PERIOD,
FISCAL_YEAR,
ACCOUNT,
Ana_Group,
SUM(RES_AMT) AS RP_RES
from PROJ_RES A1, 
DETP_TBL O
A1.ACC_DT between O.BEGIN_DT AND O.END_DT AND O.CALENDAR_ID= 'FY'
AND A1.ANA_TYPE IN (SELECT N1.ANA_TYPE FROM GRP_MAP N1 WHERE   N1.ANA_GROUP = 'RP')
GROUP BY
C_NUM,
C_L_NUM,
F_CODE,
PRO_ID,
ACC_PERIOD,
FISCAL_YEAR,
ACCOUNT,
Ana_Group


--
Thanks,
Sri

Report message to a moderator

Re: T-SQL taking much time [message #480775 is a reply to message #480772] Wed, 27 October 2010 02:32 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just to make sure: topic title suggests that you are using a Microsoft product (T-SQL), not an Oracle one. So, to save your (and our) time, what do you really use?

[EDIT] Also, it seems that query you posted isn't valid. It missese the WHERE keyword.

Furthermore, try to post a FORMATTED code. This is difficult to read.

[Updated on: Wed, 27 October 2010 02:35]

Report message to a moderator

Re: T-SQL taking much time [message #480776 is a reply to message #480775] Wed, 27 October 2010 02:34 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Sorry, I am using Oracle 10g( Sqlplus)

--
Thanks,
Sri

Report message to a moderator

Re: T-SQL taking much time [message #480782 is a reply to message #480776] Wed, 27 October 2010 02:53 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi,

Below is the code:
SELECT c_num, 
       c_l_num, 
       pro_id, 
       f_code, 
       acc_period, 
       fiscal_year, 
       ACCOUNT, 
       ana_group, 
       SUM(res_amt) AS rp_res 
FROM   proj_res a1, 
       detp_tbl o 
WHERE  a1.acc_dt BETWEEN o.begin_dt AND o.end_dt 
       AND o.calendar_id = 'FY' 
       AND a1.ana_type IN (SELECT n1.ana_type 
                           FROM   grp_map n1 
                           WHERE  n1.ana_group = 'RP') 
GROUP  BY c_num, 
          c_l_num, 
          f_code, 
          pro_id, 
          acc_period, 
          fiscal_year, 
          ACCOUNT, 
          ana_group


--
Thanks,
Surya

Report message to a moderator

Re: T-SQL taking much time [message #480785 is a reply to message #480782] Wed, 27 October 2010 03:04 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That looks MUCH better, thank you.

Did you read a sticky topic? I guess that you should provide some more information (except the query itself), as we know absolutely nothing about tables, indexes, amount of data, etc. Execution plan might be useful. Use TKPROF to gather some more information.

Report message to a moderator

Re: T-SQL taking much time [message #480796 is a reply to message #480772] Wed, 27 October 2010 04:38 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same question as in your previous topic (http://www.orafaq.com/forum/m/480622/102589/#msg_480622) so same answer.
Why do you think the answers will be different?
Why don't you INSTEAD answer the questions we posted you?

Regards
Michel

Report message to a moderator

Previous Topic: DBMS Stats gathering
Next Topic: Oracle 11g r2 result_cache is Not working
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 30 03:16:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.