Problem with subquery performance !!!

[Benouche]

Hi Folks !

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 RECETTES(T01_CODE_PAIEMENT_MODE, T02_TOTAL_AMOUNT, T03_DAY)
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_INDIVIDUAL.CT_PISTE_ISO2,2,17),19,'0')
BETWEEN TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE)
GROUP BY CT_DAY;


Waiting for reading from you,
Benouche.

 

[DaPi]

Please let us know which DBMS and version you are using -optimisation depends on those details. (The ANSI answer is that it works, what more do you want?!)

 

[angiole]

You might try to change the 'Select *' for the subquery to 'Select 'x''.
AA 8~)

 

[tlbroadbent]

How many rows does the CT_PAIEMENT_INDIVIDUAL table contain? The subquery will be performed once for every row in that table where CT_PAIEMENT_MODE ='07'. Another factor in the performance slowdown is the execution of the lpad and sunstr functions for every row selected from the table.

Does the table CT_PAIEMENT_INDIVIDUAL have an index on CT_PAIEMENT_MODE? Are the Begin_Range and End_Range columns indexed?

You may get better performance from a JOIN query than you get from the subquery. Try the following.

INSERT INTO RECETTES
(T01_CODE_PAIEMENT_MODE, T02_TOTAL_AMOUNT, T03_DAY)
SELECT 'CB', sum(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_INDIVIDUAL.CT_PISTE_ISO2,2,17),19,'0')
BETWEEN TITLE_RANGES.BEGIN_RANGE AND TITLE_RANGES.END_RANGE
GROUP BY CT_DAY; Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.