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 Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Group By taking more time [message #480636 is a reply to message #480632] |
Tue, 26 October 2010 02:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
T-SQL taking much time [message #480772 is a reply to message #480622] |
Wed, 27 October 2010 01:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: T-SQL taking much time [message #480775 is a reply to message #480772] |
Wed, 27 October 2010 02:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 #480782 is a reply to message #480776] |
Wed, 27 October 2010 02:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:09:34 CDT 2024
|