Home » SQL & PL/SQL » SQL & PL/SQL » sql query - taking long time (oracla, 10g, windows xp)
sql query - taking long time [message #355928] |
Tue, 28 October 2008 10:23  |
dezign
Messages: 3 Registered: October 2008 Location: NJ
|
Junior Member |
|
|
gurus,
i have below query, taking long time to get the result. kindly fine tune if possible..
SELECT
TRIM(TO_CHAR(CS.PREVIOUS_BALANCE_AMT, '99999999999990.00')),
TRIM(TO_CHAR(CS.ENDING_BALANCE_AMT, '99999999990.00')),
TRIM(TO_CHAR(CS.PAYMENTS, '999999999999 90.00')),
TO_CHAR(CS.PAYMENT_DUE_DT, :B2 ),
TO_CHAR(CS.STATEMENT_DT, 'YYYY MONTH'),
CS.CARD_ID
FROM
TB_CARD_STATEMENT CS
WHERE CS.CARD_ID IN (
SELECT
DISTINCT CARD.CARD_ID
FROM
TB_LOGIN_MASTER LOGINMASTER,
TB_CARD CARD,
TB_REF_BILL_TYPE REFBILLTYPE,
TB_USER_CARDS USERCARDS,
TB_USER_HIERARCHY USERHIERARCHY
WHERE
LOGINMASTER.PK_TB_LOGIN_MASTER = USERCARDS.FK_TB_LOGIN_MASTER
AND (USERCARDS.SSO_LOGIN_ID IS NULL OR USERCARDS.SSO_LOGIN_ID = '')
AND USERCARDS.CARD_NO = CARD.CARD_NBR AND CARD.ACCOUNT_TYPE_ID = 3 AND CARD.BILL_TYPE_ID = 1
AND CARD.BILL_TYPE_ID = REFBILLTYPE.BILL_TYPE_ID AND LOGINMASTER.REC_STATUS = 'AC'
AND USERCARDS.REC_STATUS = 'AC'
AND USERHIERARCHY.FK_TB_LOGIN_MASTER = LOGINMASTER.PK_TB_LOGIN_MASTER
AND USERHIERARCHY.SSO_LOGIN_ID IS NULL
AND USERHIERARCHY.REC_STATUS = 'AC'
AND CARD.COMPANY_NBR = :B3
UNION
SELECT
CARD.CARD_ID
FROM
TB_CARD CARD, TB_REF_BILL_TYPE REFBILLTYPE
WHERE
CARD.ACCOUNT_TYPE_ID =3
AND CARD.BILL_TYPE_ID = 1
AND REF BILLTYPE.BILL_TYPE_ID = CARD.BILL_TYPE_ID
AND CARD.COMPANY_NBR = :B3
UNION
SELECT :B4 FROM TB_CARD )
AND TO_CHAR(CS.STATEMENT_DT , 'YYYY MON') = :B1 ORDER BY CS.CARD_ID
|
|
|
Re: sql query - taking long time [message #355929 is a reply to message #355928] |
Tue, 28 October 2008 10:41   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Welcome to the forum. I don't think anybody will be able to help you without you following the forum guidelines mentioned in the performance tuning section.
Regards
Raj
|
|
|
|
Re: sql query - taking long time [message #355948 is a reply to message #355928] |
Tue, 28 October 2008 12:33   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Couple of thoughts.
First there are guidelines posted somewhere on how to post code to this forum. One of the guidelines is to format your code. I have done it for you here. Maybe when Michel comes on he will give a link or instructions on where to go for this.
SELECT TRIM(To_char(cs.Previous_Balance_Amt,'99999999999990.00')),
TRIM(To_char(cs.Ending_Balance_Amt,'99999999990.00')),
TRIM(To_char(cs.Payments,'999999999999 90.00')),
To_char(cs.Payment_Due_dt,:B2),
To_char(cs.Statement_dt,'YYYY MONTH'),
cs.Card_Id
FROM Tb_Card_Statement cs
WHERE cs.Card_Id IN (SELECT Card.Card_Id
FROM Tb_LogIn_Master LogInMaster,
Tb_Card Card,
Tb_ref_Bill_Type refBillType,
Tb_User_Cards UserCards,
Tb_User_Hierarchy UserHierarchy
WHERE LogInMaster.pk_Tb_LogIn_Master = UserCards.fk_Tb_LogIn_Master
AND (UserCards.sSo_LogIn_Id IS NULL
OR UserCards.sSo_LogIn_Id = '')
AND UserCards.Card_No = Card.Card_nbr
AND Card.Account_Type_Id = 3
AND Card.Bill_Type_Id = 1
AND Card.Bill_Type_Id = refBillType.Bill_Type_Id
AND LogInMaster.rec_Status = 'AC'
AND UserCards.rec_Status = 'AC'
AND UserHierarchy.fk_Tb_LogIn_Master = LogInMaster.pk_Tb_LogIn_Master
AND UserHierarchy.sSo_LogIn_Id IS NULL
AND UserHierarchy.rec_Status = 'AC'
AND Card.Company_nbr = :B3
UNION
SELECT Card.Card_Id
FROM Tb_Card Card,
Tb_ref_Bill_Type refBillType
WHERE Card.Account_Type_Id = 3
AND Card.Bill_Type_Id = 1
AND refBillType.Bill_Type_Id = Card.Bill_Type_Id
AND Card.Company_nbr = :B3
UNION
SELECT :B4
FROM Tb_Card)
AND To_char(cs.Statement_dt,'YYYY MON') = :B1
ORDER BY cs.Card_Id
Next, when ever I am faced with a multipart query that performs slow, I try to break up the pieces and time each one individually. Indeed, most of the time I have an error in the code which pops out in this step. So for your case you would need to do the following:
How long do each of these take? Each of these is a small part of your query but one of them may be consuming most of the resources of your query.
SELECT Card.Card_Id
FROM Tb_Card Card,
Tb_ref_Bill_Type refBillType
WHERE Card.Account_Type_Id = 3
AND Card.Bill_Type_Id = 1
AND refBillType.Bill_Type_Id = Card.Bill_Type_Id
AND Card.Company_nbr = :B3
SELECT Card.Card_Id
FROM Tb_LogIn_Master LogInMaster,
Tb_Card Card,
Tb_ref_Bill_Type refBillType,
Tb_User_Cards UserCards,
Tb_User_Hierarchy UserHierarchy
WHERE LogInMaster.pk_Tb_LogIn_Master = UserCards.fk_Tb_LogIn_Master
AND (UserCards.sSo_LogIn_Id IS NULL
OR UserCards.sSo_LogIn_Id = '')
AND UserCards.Card_No = Card.Card_nbr
AND Card.Account_Type_Id = 3
AND Card.Bill_Type_Id = 1
AND Card.Bill_Type_Id = refBillType.Bill_Type_Id
AND LogInMaster.rec_Status = 'AC'
AND UserCards.rec_Status = 'AC'
AND UserHierarchy.fk_Tb_LogIn_Master = LogInMaster.pk_Tb_LogIn_Master
AND UserHierarchy.sSo_LogIn_Id IS NULL
AND UserHierarchy.rec_Status = 'AC'
AND Card.Company_nbr = :B3
Lastly, do you really mean:
Maybe you want to select :B4 once for every row in tb_card? Or do you may mean
Get us timings for the entire query, and each of these parts.
Good luck, Kevin
[Updated on: Tue, 28 October 2008 12:34] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:17:19 CST 2025
|