Hello !
I have a very huge problem of performance with the following query (containing a subquery).
The table TITLE_RANGES is not very big (~ 50 rows) and my query runs for 10 minutes. Whithout the subquery an analog subquery turns less than 1 second.
Any help will be greatly appreciated and hotly welcome !
INSERT INTO CREDIT(T01_CODE_PAIEMENT_M
SELECT 'CB', sum(CT_PRICE_FF), CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL
WHERE CT_PAIEMENT_MODE ='07' AND EXISTS
(SELECT *
FROM TITLE_RANGES
WHERE TITLE_RANGES.MNEMONIQUE = 'CB'
AND (lpad(substr(CT_PAIEMENT_I
BETWEEN TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE)
GROUP BY CT_DAY;
Waiting for reading from you,
Benouche.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Let's see, you are trying to write records to table CREDIT. The records you are writing are coming from the CT_PAIEMENT_INDIVIDUAL Table, filtered by CT_PAIEMENT_MODE = '07'. You are then saying only write the records if you return any row from TITLE_RANGES where MNEMONIQUE = 'CB' AND your substring function on CT_PAIEMENT_INDIVIDUAL.PIS
INSERT INTO CREDIT(T01_CODE_PAIEMENT_M
SELECT 'CB', sum(CPI.CT_PRICE_FF), CPI.CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL CPI, TITLE_RANGES TR
WHERE CPI.CT_PAIEMENT_MODE ='07' AND
TR.MNEMONIQUE = 'CB'
AND (lpad(substr(CPI.CT_PISTE_
BETWEEN TR.BEGIN_RANGE AND TR.END_RANGE)
GROUP BY CPI.CT_DAY;
If not, try this. I have aliased the table names and the problem may be it is not correlating your subquery CPI field to the outer driving query.
Total Guess:
INSERT INTO CREDIT(T01_CODE_PAIEMENT_M
SELECT 'CB', sum(CPI.CT_PRICE_FF), CPI.CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL CPI
WHERE CPI.CT_PAIEMENT_MODE ='07' AND EXISTS
(SELECT ROWID
FROM TITLE_RANGES TR
WHERE TR.MNEMONIQUE = 'CB'
AND (lpad(substr(CPI.CT_PISTE_
BETWEEN TR.BEGIN_RANGE AND TR.END_RANGE)
GROUP BY CPI.CT_DAY;
If this doesn't work, then run AUTOTRACE on theproblem query, but it looks tome like you are doing a full table scan on CPI for every in TR.
Please try this query, if that doesn't work, would you please post the table structures and indexes of CT_PAIEMENT_INDIVIDUAL and TITLE_RANGES. You should be able to get rid of the distinct and just do a sum in the middle if there can only be one row in TITLE_RANGES where MNEMONIQUE = 'CB'
INSERT INTO CREDIT(T01_CODE_PAIEMENT_M
SELECT TST, SUM(CT_PRICE_FF), CT_DAY
FROM
(
SELECT DISTINCT 'CB' as "TST" , CT_PRICE_FF, CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL, TITLE_RANGES
WHERE CT_PAIEMENT_MODE ='07'
AND TITLE_RANGES.MNEMONIQUE = 'CB'
AND (lpad(substr(CT_PAIEMENT_I
)
GROUP BY CT_DAY
Sorry, left out something, please use this query, above one will not run, the group by is incorrect.
INSERT INTO CREDIT(T01_CODE_PAIEMENT_M
SELECT TST, SUM(CT_PRICE_FF), CT_DAY
FROM
(
SELECT DISTINCT 'CB' as "TST" , CT_PRICE_FF, CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL, TITLE_RANGES
WHERE CT_PAIEMENT_MODE ='07'
AND TITLE_RANGES.MNEMONIQUE = 'CB'
AND (lpad(substr(CT_PAIEMENT_I
)
GROUP BY TST, CT_DAY
If TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE are of number type then you should convert to number.
and try to create index on these columns
SELECT 'CB', sum(CPI.CT_PRICE_FF), CPI.CT_DAY
FROM CT_PAIEMENT_INDIVIDUAL CPI
WHERE CPI.CT_PAIEMENT_MODE ='07' AND EXISTS
(SELECT 0
FROM TITLE_RANGES TR
WHERE TR.MNEMONIQUE = 'CB'
AND to_number(substr(CPI.CT_PI
BETWEEN TR.BEGIN_RANGE AND TR.END_RANGE)
GROUP BY CPI.CT_DAY;
good luck,
Kanan
Business Accounts
Answer for Membership
by: dash420Posted on 2002-11-26 at 05:37:28ID: 7498728
is there any column common between CT_PAIEMENT_INDIVIDUAL
and TITLE_RANGES. so that instead of using the subquery you can do it in making join.
also
(SELECT *
FROM TITLE_RANGES
WHERE TITLE_RANGES.MNEMONIQUE = 'CB'
AND (lpad(substr(CT_PAIEMENT_I
BETWEEN TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE)
you are selecting all the column from TITLE_RANGES. instead f that u just pick key item of TITLE_RANGES
(SELECT <key_column>
FROM TITLE_RANGES
WHERE TITLE_RANGES.MNEMONIQUE = 'CB'
AND (lpad(substr(CT_PAIEMENT_I
BETWEEN TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE)
it will reduce query time some extents.