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"
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: 283
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.
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 227 times)
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?
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: 283
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.

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


Current Time: Thu Dec 08 04:20:33 CST 2016

Total time taken to generate the page: 0.12241 seconds