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 Go to next message
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 Go to previous messageGo to next message
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 #355930 is a reply to message #355928] Tue, 28 October 2008 10:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remove the distinct and change all UNIONs to UNION ALL
I can't tell you anything else, since I have no clue about the distribution of your data, indexes present, etc.
Re: sql query - taking long time [message #355948 is a reply to message #355928] Tue, 28 October 2008 12:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 :B4
                        FROM   Tb_Card

                        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:

                        SELECT :B4
                        FROM   Tb_Card

Maybe you want to select :B4 once for every row in tb_card? Or do you may mean

select :b4 from dual


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

Re: sql query - taking long time [message #355964 is a reply to message #355948] Tue, 28 October 2008 14:22 Go to previous messageGo to next message
dezign
Messages: 3
Registered: October 2008
Location: NJ
Junior Member
Kevin
Thank you very much..

Regards
Re: sql query - taking long time [message #355968 is a reply to message #355928] Tue, 28 October 2008 14:42 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
No sweat Dezign. You will update us when you find something interesting right?

Thanks, Kevin
Previous Topic: oracle view
Next Topic: Autonomous transaction on trigger
Goto Forum:
  


Current Time: Sat Dec 10 10:51:22 CST 2016

Total time taken to generate the page: 0.21448 seconds