Home » RDBMS Server » Performance Tuning » sql query too slow -maybe needs indexes?
sql query too slow -maybe needs indexes? [message #236891] Fri, 11 May 2007 03:53 Go to next message
eb222
Messages: 7
Registered: May 2007
Junior Member
Hi - can anyone help me with below query as it takes all day to run -any advise will be appreciated.

thanks

SELECT "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE" "C0",
"T2"."Customer_Nr_And_Name" "C1",
"T2"."Bill_To_Site_Nr_And_Name_" "C2",
"Facts_Receivables_AsOfMonth"."INVOICE_DATE" "C3",
"Facts_Receivables_AsOfMonth"."DUE_DATE" "C4",
"Facts_Receivables_AsOfMonth"."INVOICE_NUMBER" "C5",
SUM ("Facts_Receivables_AsOfMonth"."AMOUNT_DUE_ORIGINAL_IN_EUR") OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C6",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE" =
'EUR'
THEN NULL
ELSE "Facts_Receivables_AsOfMonth"."AR_BALANCE"
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C7",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."AR_BALANCE" > 0
THEN "Facts_Receivables_AsOfMonth"."AR_BALANCE"
ELSE NULL
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C8",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."AR_BALANCE" < 0
THEN "Facts_Receivables_AsOfMonth"."AR_BALANCE"
ELSE NULL
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C9",
SUM ("Facts_Receivables_AsOfMonth"."AR_BALANCE") OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C10"
FROM (SELECT "F_AR_BALANCE_ASOFMONTH"."INVOICE_NUMBER" "INVOICE_NUMBER",
"F_AR_BALANCE_ASOFMONTH"."INVOICE_CURRENCY_CODE"
"INVOICE_CURRENCY_CODE",
"F_AR_BALANCE_ASOFMONTH"."INVOICE_DATE" "INVOICE_DATE",
"F_AR_BALANCE_ASOFMONTH"."DUE_DATE" "DUE_DATE",
"F_AR_BALANCE_ASOFMONTH"."AMOUNT_DUE_ORIGINAL_IN_EUR"
"AMOUNT_DUE_ORIGINAL_IN_EUR",
"F_AR_BALANCE_ASOFMONTH"."AR_BALANCE" "AR_BALANCE",
"F_AR_BALANCE_ASOFMONTH"."SOURCE_OPER_UNIT_ID"
"SOURCE_OPER_UNIT_ID",
"F_AR_BALANCE_ASOFMONTH"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID"
FROM (SELECT *
FROM "DWADMIN"."F_AR_BALANCE_ASOFMONTH"
WHERE "ASOFMONTH" = '200704') "F_AR_BALANCE_ASOFMONTH"
WHERE "F_AR_BALANCE_ASOFMONTH"."SOURCE_OPER_UNIT_ID" IN
('EBS87', 'EBS88', 'EBS89')) "Facts_Receivables_AsOfMonth",
(SELECT "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID1",
CASE
WHEN (CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
|| ' '
)
END IS NULL
)
OR ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME" IS NULL)
THEN NULL
ELSE ( CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
|| ' '
)
END
|| "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME"
)
END "Customer_Nr_And_Name",
CASE
WHEN (CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
|| ' '
)
END IS NULL
)
OR ("D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME" IS NULL)
THEN NULL
ELSE ( CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
|| ' '
)
END
|| "D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME"
)
END "Bill_To_Site_Nr_And_Name_",
"D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID"
"SOURCE_BILL_TO_OU_ID"
FROM (SELECT "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID",
"D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID"
"SOURCE_BILL_TO_OU_ID",
"D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME"
"BILL_TO_PARTY_NAME",
"D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
"BILL_TO_PARTY_SITE_NUMBER",
"D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
"BILL_TO_ACCOUNT_NUMBER",
"D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME"
"BILL_TO_SITE_NAME"
FROM "DWADMIN"."D_CUST_BILL_TO_ALL" "D_CUST_BILL_TO_ALL"
WHERE "D_CUST_BILL_TO_ALL"."CURRENT_FLAG" = 'Y') "D_CUST_BILL_TO_ALL"
WHERE "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID" IN
('EBS87', 'EBS88', 'EBS89')) "T2"
WHERE "Facts_Receivables_AsOfMonth"."SOURCE_OPER_UNIT_ID" = 'EBS88'
AND "T2"."SOURCE_BILL_TO_SITE_USE_ID1" =
"Facts_Receivables_AsOfMonth"."SOURCE_BILL_TO_SITE_USE_ID"

Report message to a moderator

Re: sql query too slow -maybe needs indexes? [message #236912 is a reply to message #236891] Fri, 11 May 2007 05:36 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Can you please format your code.

Also please send us the create scripts for the tables so we can see what the query is querying in.

Report message to a moderator

Re: sql query too slow -maybe needs indexes? [message #236915 is a reply to message #236891] Fri, 11 May 2007 05:45 Go to previous messageGo to next message
eb222
Messages: 7
Registered: May 2007
Junior Member
have uploaded it in notepad format -will try to get the table scripts for you
  • Attachment: sqlcode.txt
    (Size: 8.05KB, Downloaded 1327 times)

Report message to a moderator

Re: sql query too slow -maybe needs indexes? [message #237007 is a reply to message #236915] Fri, 11 May 2007 10:30 Go to previous messageGo to next message
eb222
Messages: 7
Registered: May 2007
Junior Member
unfortunately i cannot get the table creation scripts for you -the developer who would know has left company -is there any other way you can help?

i can run any scripts or explain plans for you if its helpful?

Report message to a moderator

Re: sql query too slow -maybe needs indexes? [message #237051 is a reply to message #237007] Fri, 11 May 2007 13:48 Go to previous message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
That is a migthy big sql.

An explain plan would be nice yes.

Seeing the complexity of the sql I'm not sure whether I can help you. The explain plan is a nice help though.

Report message to a moderator

Previous Topic: Regarding partitions
Next Topic: Explain plan Cost vs time elapsed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 08:04:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.