Home » SQL & PL/SQL » SQL & PL/SQL » Query performance (SQL)
| Query performance [message #661389] |
Thu, 16 March 2017 09:05  |
 |
Jiggu
Messages: 5
Registered: March 2017
|
Junior Member |
|
|
Hi Friends,
Iam very new for this forum. I have one query. Please check the attached query and provide me your valuable suggestions.
In this query RAV.INVO_INVOICENO IN() we are providing 20000 INVO_INVOICENO numbers. performance is very slow..
Kindly check and provide me the suggestions.
Thanks and Regards,
Jiggu
|
|
|
|
|
|
|
|
| Re: Query performance [message #661393 is a reply to message #661391] |
Thu, 16 March 2017 10:00  |
|
Jiggu
Messages: 5
Registered: March 2017
|
Junior Member |
|
|
Hi Joy,
Please check the complete query....
SELECT
CAST(RAV.INVO_NO AS VARCHAR(50)) as Invoice_Number,
RAV.INVO_CLNTNO as Client_Number,
TRIM(RAV.INVO_DEBC_DEBC) as Client_Customer_Reference,
RAV.INVO_DEBC_NO as Debtor_Number,
RAV.INVO_INVDATE as Invoice_Date,
RAV.INVO_CLIENT_DUEDATE as Due_Date,
RAV.INVO_CURRENCY as Currency,
RAV.INVO_AINITIALI as Document_Amount,
CAST(RAV.INVO_MARKCODERISK AS VARCHAR(50)) as Risk_Mark_Code,
CAST(RAV.INVO_COLLECTIONMARKCODE AS VARCHAR(50)) as Collection_Mark_Code,
RAV.INVO_INVOICENO as Document_Number,
RAV.INVO_ADMNO as Admin_Number,
RAV.INVO_DEBH_NO as Heller_Debtor_Number,
RAV.INVO_DISPUTE_YN as Disputed,
DEBH.DEBH_HADR_NAME1 as Heller_Debtor_Name,
RAV.INVO_AACTUALI as Actual_Balance,
CAST(DEBH.DEBH_LEGALOWNER_DEBH AS VARCHAR(50)) as Legal_Owner,
INVA_ACTA_NR_F + INVA_ACTA_NR_NF AS NR_Amount,
INVA_ACTA_R_F + INVA_ACTA_R_NF AS R_Amount,
DT.CODE_DESCRIPTION as Debtor_Type,
CAST(ISFS.ISFS_STATUS AS VARCHAR(50)) as Item_Status,
VIN.EFID_VALUE AS VAT_Invoice_Number,
VID.EFID_VALUE AS VAT_Invoice_Date,
EC.EFID_VALUE AS End_Customer,
CN.EFID_VALUE AS Contract_Number
FROM
RAV
INNER JOIN INVA
ON INVA.INVA_NO = RAV.INVO_NO
AND RAV.INVO_CURRENCY = INVA.INVA_CURRENCY
INNER JOIN DEBH
ON DEBH.DEBH_NO = RAV.INVO_DEBH_NO
LEFT JOIN CWCS DT
ON DT.CODE_NUMBERN = DEBH.DEBH_DEBTORTYPE
AND DT.CODE_TYPE = 135
AND DT.CODE_LANGUAGE = 'GB'
LEFT JOIN ISFS
ON ISFS.ISFS_INVO_NO = RAV.INVO_NO
LEFT JOIN EFID VIN
ON VIN.EFID_INVO_NO = RAV.INVO_NO
AND VIN.EFID_FIELD_NAME = 'VAT INVOICE NUMBER'
LEFT JOIN EFID VID
ON VID.EFID_INVO_NO = RAV.INVO_NO
AND VID.EFID_FIELD_NAME = 'VAT INVOICE DATE'
LEFT JOIN EFID VIA
ON VIA.EFID_INVO_NO = RAV.INVO_NO
AND VIA.EFID_FIELD_NAME = 'VAT INVOICE AMOUNT'
LEFT JOIN EFID EC
ON EC.EFID_INVO_NO = RAV.INVO_NO
AND EC.EFID_FIELD_NAME = 'END CUSTOMER'
LEFT JOIN EFID CN
ON CN.EFID_INVO_NO = RAV.INVO_NO
AND CN.EFID_FIELD_NAME = 'CONTRACT NUMBER'
WHERE
RAV.INVO_AACTUALI > 0
AND RAV.INVO_ACCRUAL <> 'Y'
AND RAV.INVO_MARKCODESPEC <> 943
AND RAV.INVO_MARKCODERISK <> 149
AND (RAV.INVO_CURRENCY = 'USD')
AND RAV.INVO_INVOICENO IN ('24584667',
'24584666','24584665','24584664','24584663','24584662','24584661','24584660','24584659','24584658',
'24584657','24584656','24584655','24584654','24584653','24584652','24584651','24584650','24584649',
'24584648','24584647','24584646','24584645','24584644','24584643')
AND ROWNUM <= 5000 ;
|
|
|
|
|
|
| Re: Query performance [message #661401 is a reply to message #661393] |
Thu, 16 March 2017 12:32 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You say that your IN list has 20000 values, but your final predicate requests just 5000 randomly selected rows. Is that right?
|
|
|
|
|
|
| Re: Query performance [message #661475 is a reply to message #661389] |
Sun, 19 March 2017 19:25 |
 |
mikek
Messages: 29
Registered: January 2017
|
Junior Member |
|
|
Adding Indexes to an Application Database is generally a trade off between
Query Performance and the Additional Transaction Overhead/Additional Disk
Storage space for the Indexes. I would suggest talking it over with your team
before implementing and hopefully you have a test environment to try the changes.
Some suggested Indexing Strategy. The RAV Table appears to be the Primary Table
and you should check if the Column invo_invoiceno in the RAV Table is indexed.
CREATE INDEX rav_ak ON rav (invo_invoiceno)
TABLESPACE ???;
Also the EFID Table appears to be the second primary table in the Query (5 Joins)
and is possibly a detail table of the RAV Table. You may consider a composite index
on the columns efid_invo_no, efid_field_name, efid_value.
Also note the addition of the Column VAT_Invoice_Amount to the Query since there is
join to include the data. I also included a possible approach to De-normalizing the
Queries on the EFID Table to use a single join. It is an addition to possibly generate
some additional discussion.
CREATE INDEX efid_ak ON efid (efid_invo_no, efid_field_name, efid_value)
TABLESPACE ???;
I reformatted the Original SQL for Review. Formatted Queries are generally easier to
understand and diagnose.
SELECT CAST(rav.invo_no AS VARCHAR(50)) AS Invoice_Number,
rav.invo_clntno AS Client_Number,
TRIM(rav.invo_debc_debc) AS Client_Customer_Reference,
rav.invo_debc_no AS Debtor_Number,
rav.invo_invdate AS Invoice_Date,
rav.invo_client_duedate AS Due_Date,
rav.invo_currency AS Currency,
rav.invo_ainitiali AS Document_Amount,
CAST(rav.invo_markcoderisk AS VARCHAR(50)) AS Risk_Mark_Code,
CAST(rav.invo_collectionmarkcode AS VARCHAR(50)) AS Collection_Mark_Code,
rav.invo_invoiceno AS Document_Number,
rav.invo_admno AS Admin_Number,
rav.invo_debh_no AS Heller_Debtor_Number,
rav.invo_dispute_yn AS Disputed,
debh.debh_hadr_name1 AS Heller_Debtor_Name,
rav.invo_aactuali AS Actual_Balance,
CAST(debh.debh_legalowner_debh AS VARCHAR(50)) AS Legal_Owner,
inva_acta_nr_f + inva_acta_nr_nf AS NR_Amount,
inva_acta_r_f + inva_acta_r_nf AS R_Amount,
dt.code_description AS Debtor_Type,
CAST(isfs.isfs_status AS VARCHAR(50)) AS Item_Status,
vin.efid_value AS VAT_Invoice_Number,
vid.efid_value AS VAT_Invoice_Date,
via.efid_value AS VAT_Invoice_Amount, -- Column was missing based on Join Info
ec.efid_value AS End_Customer,
cn.efid_value AS Contract_Number
FROM rav rav
INNER JOIN inva inva
ON inva.inva_no = rav.invo_no
AND rav.invo_currency = inva.inva_currency
INNER JOIN debh debh
ON debh.debh_no = rav.invo_debh_no
LEFT JOIN cwcs dt
ON dt.code_numbern = debh.debh_debtortype
AND dt.code_type = 135
AND dt.code_language = 'GB'
LEFT JOIN isfs isfs
ON isfs.isfs_invo_no = rav.invo_no
LEFT JOIN efid vin
ON vin.efid_invo_no = rav.invo_no
AND vin.efid_field_name = 'VAT INVOICE NUMBER'
LEFT JOIN efid vid
ON vid.efid_invo_no = rav.invo_no
AND vid.efid_field_name = 'VAT INVOICE DATE'
LEFT JOIN efid via
ON via.efid_invo_no = rav.invo_no
AND via.efid_field_name = 'VAT INVOICE AMOUNT'
LEFT JOIN efid ec
ON ec.efid_invo_no = rav.invo_no
AND ec.efid_field_name = 'END CUSTOMER'
LEFT JOIN efid cn
ON cn.efid_invo_no = rav.invo_no
AND cn.efid_field_name = 'CONTRACT NUMBER'
WHERE rav.invo_aactuali > 0
AND rav.invo_accrual <> 'Y'
AND rav.invo_markcodespec <> 943
AND rav.invo_markcoderisk <> 149
AND ( rav.invo_currency = 'USD' )
AND rav.invo_invoiceno IN ( '24584667', '24584666', '24584665',
'24584664', '24584663', '24584662', '24584661',
'24584660', '24584659', '24584658', '24584657',
'24584656', '24584655', '24584654', '24584653',
'24584652', '24584651', '24584650', '24584649',
'24584648', '24584647', '24584646', '24584645',
'24584644', '24584643' )
AND ROWNUM <= 5000;
Possible change to de-normalize the 5 separate LEFT JOIN Connections to the EFID Table by
replacing with a de-normalized in-line view using one Join
SELECT efid.efid_invo_no
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE' , efid_value, NULL)) VAT_Invoice_Date
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
,MAX(DECODE efid.efid_field_name, 'END CUSTOMER' , efid_value, NULL)) End_Customer
,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER' , efid_value, NULL)) Contract_Number
FROM efid
WHERE efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
,'END CUSTOMER','CONTRACT NUMBER' )
GROUP BY efid.efid_invo_no
Possible modified original query. Index suggestions the same. No guarantees since
I was unable to fully test the mod.
SELECT CAST(rav.invo_no AS VARCHAR(50)) AS Invoice_Number,
rav.invo_clntno AS Client_Number,
TRIM(rav.invo_debc_debc) AS Client_Customer_Reference,
rav.invo_debc_no AS Debtor_Number,
rav.invo_invdate AS Invoice_Date,
rav.invo_client_duedate AS Due_Date,
rav.invo_currency AS Currency,
rav.invo_ainitiali AS Document_Amount,
CAST(rav.invo_markcoderisk AS VARCHAR(50)) AS Risk_Mark_Code,
CAST(rav.invo_collectionmarkcode AS VARCHAR(50)) AS Collection_Mark_Code,
rav.invo_invoiceno AS Document_Number,
rav.invo_admno AS Admin_Number,
rav.invo_debh_no AS Heller_Debtor_Number,
rav.invo_dispute_yn AS Disputed,
debh.debh_hadr_name1 AS Heller_Debtor_Name,
rav.invo_aactuali AS Actual_Balance,
CAST(debh.debh_legalowner_debh AS VARCHAR(50)) AS Legal_Owner,
inva_acta_nr_f + inva_acta_nr_nf AS NR_Amount,
inva_acta_r_f + inva_acta_r_nf AS R_Amount,
dt.code_description AS Debtor_Type,
CAST(isfs.isfs_status AS VARCHAR(50)) AS Item_Status,
efid.vat_invoice_number AS VAT_Invoice_Number,
efid.vat_invoice_date AS VAT_Invoice_Date,
efid.vat_invoice_amount AS VAT_Invoice_Amount,
efid.end_customer AS End_Customer,
efid.contract_number AS Contract_Number
FROM rav rav
INNER JOIN inva inva
ON inva.inva_no = rav.invo_no
AND rav.invo_currency = inva.inva_currency
INNER JOIN debh debh
ON debh.debh_no = rav.invo_debh_no
LEFT JOIN cwcs dt
ON dt.code_numbern = debh.debh_debtortype
AND dt.code_type = 135
AND dt.code_language = 'GB'
LEFT JOIN isfs isfs
ON isfs.isfs_invo_no = rav.invo_no
LEFT JOIN
( SELECT efid.efid_invo_no
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE' , efid_value, NULL)) VAT_Invoice_Date
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
,MAX(DECODE efid.efid_field_name, 'END CUSTOMER' , efid_value, NULL)) End_Customer
,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER' , efid_value, NULL)) Contract_Number
FROM efid
WHERE efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
,'END CUSTOMER','CONTRACT NUMBER' )
GROUP BY efid.efid_invo_no
) efid
WHERE rav.invo_aactuali > 0
AND rav.invo_accrual <> 'Y'
AND rav.invo_markcodespec <> 943
AND rav.invo_markcoderisk <> 149
AND ( rav.invo_currency = 'USD' )
AND rav.invo_invoiceno IN ( '24584667', '24584666', '24584665',
'24584664', '24584663', '24584662', '24584661',
'24584660', '24584659', '24584658', '24584657',
'24584656', '24584655', '24584654', '24584653',
'24584652', '24584651', '24584650', '24584649',
'24584648', '24584647', '24584646', '24584645',
'24584644', '24584643' )
AND ROWNUM <= 5000;
|
|
|
|
|
|
| Re: Query performance [message #661488 is a reply to message #661389] |
Mon, 20 March 2017 14:19 |
|
mikek
Messages: 29
Registered: January 2017
|
Junior Member |
|
|
Sorry about that I Had a Cut and Paste Malfunction and forgot the ON Clause
for line 51. Hope that corrects the error.
Also you my want to add a WHERE Clause with the efid.efid_invo_no assigned to some Invoice Number
for the previous de-normalizing "SELECT efid.efid_invo_no ..." Query
LEFT JOIN
( SELECT efid.efid_invo_no
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE' , efid_value, NULL)) VAT_Invoice_Date
,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
,MAX(DECODE efid.efid_field_name, 'END CUSTOMER' , efid_value, NULL)) End_Customer
,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER' , efid_value, NULL)) Contract_Number
FROM efid
WHERE efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
,'END CUSTOMER','CONTRACT NUMBER' )
GROUP BY efid.efid_invo_no
) efid
ON efid.efid_invo_no = rav.invo_no
|
|
|
|
| Re: Query performance [message #661508 is a reply to message #661488] |
Wed, 22 March 2017 03:10 |
|
Jiggu
Messages: 5
Registered: March 2017
|
Junior Member |
|
|
Hi, thank you. Now there is no syntax errors.
But I put the 3000 invoices in 'IN' Claus.Getting the below error message. Please help me how to solve.
Example: AND rav.invo_invoiceno IN ( ( '24584667', '24584666', '24584665','24584664', '24584663', '24584662', '24584661',......................up to 3000))
Error message:
ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 - "maximum number of expressions in a list is 1000"
*Cause:
*Action:
Error at Line: 1,061 Column: 1
|
|
|
|
|
|
| Re: Query performance [message #661514 is a reply to message #661509] |
Wed, 22 March 2017 06:44 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
You may NOT have more then 1000 hardcoded items in an IN list. However if the 5000 or 10000 or any number are in a table then you can simply use
and rav.invo_invoiceno in (select my_key_word from my_table)
and it works fine.
[Updated on: Wed, 22 March 2017 06:44] Report message to a moderator |
|
|
|
| Re: Query performance [message #661518 is a reply to message #661508] |
Wed, 22 March 2017 08:09 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Jiggu wrote on Wed, 22 March 2017 04:10
But I put the 3000 invoices in 'IN' Claus.Getting the below error message. Please help me how to solve.
Example: AND rav.invo_invoiceno IN ( ( '24584667', '24584666', '24584665','24584664', '24584663', '24584662', '24584661',......................up to 3000))
Error message:
ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 - "maximum number of expressions in a list is 1000"
*Cause:
*Action:
Error at Line: 1,061 Column: 1
And this is exactly what Black Swan told you.
Plus, I have a feeling that you are using ROWNUM incorrectly. ROWNUM operates on the complete dataset AFTER it is returned, not before, so if you think you are limiting the query to 5000 rows and then applying all of your conditions, you would be incorrect.
|
|
|
|
| Re: Query performance [message #661524 is a reply to message #661518] |
Wed, 22 March 2017 09:12 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Not sure why anyone would want to use the rownum limit before applying other conditions - it would make the rownum limit a theoretical maximum.
The rownum use is suspect because the query isn't written as a top-n query so which 5000 rows you get is random.
|
|
|
|
|
|
| Re: Query performance [message #661528 is a reply to message #661527] |
Wed, 22 March 2017 10:12 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So you need to tune the query, which I would expect to run as a semi join.
The usual process: capture the exec plan, see if you can come up with a better one. An obvious starting point is what you call your "temp table". Does it have statistics? Indexes?
|
|
|
|
|
|
| Re: Query performance [message #661532 is a reply to message #661524] |
Wed, 22 March 2017 13:29 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Wed, 22 March 2017 10:12Not sure why anyone would want to use the rownum limit before applying other conditions - it would make the rownum limit a theoretical maximum.
The rownum use is suspect because the query isn't written as a top-n query so which 5000 rows you get is random.
Just based on the fact that OP got the same error message as was previously brought to their attention and then asked about it as if they didn't know why they got it, I suspected that using the ROWNUM < 5000 was the OP's intent to speed up the query by using only 5000 rows for their query. Incorrectly assuming that a query against a small dataset would be faster than a query against a large dataset.
|
|
|
|
| Re: Query performance [message #661582 is a reply to message #661532] |
Fri, 24 March 2017 04:40 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
If the data is in a temp table then you have to add an index on the table or it will be as slow as death. Personally I wouldn't use a standard table with an index. I would put my values into an index organized table that way no index is required.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 00:44:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
