Hi,

Below is a sql code which is being used in a sql passthrough. Eventhough the total number of records in the table is 19600. It is taking over 30 mins to execute. You might think "Ok, maybe it's very complex and the DB is taking some time to organize it". Valid point. But, when I run the SQL in SQL developer it takes about 10 secs. Plus, if I run it as a SQL Passthrough in focus with the added "and rownum <50000" filter clause it returns back in 10 secs.

Does anyone have any idea why this might be happening? It doesn't appear to be a database issue. Could webfocus be the culprit?

-Thanks

SELECT t1.szvacur_last_name
||', '
|| t1.szvacur_first_name
||' '
|| t1.szvacur_mi fullname,
t1.szvacur_id FIDN,
t1.szvacur_pidm PIDM,
t1.szvacur_coll_code COLL,
t1.szvacur_degc_code DEGC,
t1.szvacur_majr_code_1 MAJR,
t4.gvbgsed_sdef_seq_num,
SUBSTR(TO_CHAR((
CASE t7.gvvacod_answ
WHEN 'MO'
THEN t5.gvbqcod_desc
WHEN 'RNK'
THEN t5.gvbqcod_desc
||' '
|| t6.gvrpvac_desc
WHEN 'MOC'
THEN t5.gvbqcod_desc
WHEN 'OP'
THEN t5.gvbqcod_desc
WHEN 'MS'
THEN t5.gvbqcod_desc
|| ' '
|| t6.gvrpvac_desc
WHEN 'MSC'
THEN t5.gvbqcod_desc
|| ' '
|| t6.gvrpvac_desc
END )),1,2000)quest ,
SUBSTR(TO_CHAR((
CASE t7.gvvacod_answ
WHEN 'MO'
THEN t6.gvrpvac_desc
WHEN 'RNK'
THEN t4.gvbgsed_open_answer
WHEN 'MOC'
THEN t6.gvrpvac_desc
||' '
|| t4.gvbgsed_open_answer
WHEN 'OP'
THEN t4.gvbgsed_open_answer
WHEN 'MS'
THEN TO_CLOB('Y')
WHEN 'MSC'
THEN TO_CLOB('Y')
||' '
|| t4.gvbgsed_open_answer
END)),1,2000) ans
FROM szvacur t1
INNER JOIN
(SELECT szvacur_pidm,
MAX(szvacur_term_code) maxterm
FROM szvacur
WHERE szvacur_term_code <='&TERM.EVAL'
AND EXISTS
(SELECT gvrsras_spidm
FROM gvrsras
WHERE szvacur.szvacur_pidm = gvrsras.gvrsras_spidm
)
GROUP BY szvacur_pidm
) t2
ON t1.szvacur_pidm = t2.szvacur_pidm
AND t1.szvacur_term_code = t2.maxterm
INNER JOIN gvrsras t3
ON t1.szvacur_pidm = t3.gvrsras_spidm
INNER JOIN gvbgsed t4
ON t3.gvrsras_temp_pidm = t4.gvbgsed_sras_temp_pidm
AND t3.gvrsras_srn = t4.gvbgsed_srn
INNER JOIN gvbqcod t5
ON t5.gvbqcod_code = t4.gvbgsed_qcod_code
INNER JOIN gvvacod t7
ON t4.gvbgsed_acod_code = t7.gvvacod_code
inner join gvrsrvy t8
on t8.gvrsrvy_srn = t3.gvrsras_srn
LEFT OUTER JOIN gvrpvac t6
ON t4.gvbgsed_pvac_seq_num = t6.gvrpvac_seq_num
AND t4.gvbgsed_acod_code = t6.gvrpvac_acod_code
WHERE t1.szvacur_order > 0
&CMTSTAT.EVAL AND t3.gvrsras_status_ind = '&STATUS.EVAL'
AND t8.gvrsrvy_gsrc_code = '&SURVY.EVAL'
-*AND t3.gvrsras_spidm IN ('621405','625989')
ORDER BY t1.szvacur_pidm, t4.gvbgsed_sdef_seq_num;

This message has been edited. Last edited by: Kerry,

I also forgot to mention that if I run the same passthrough with a difference parameter which would output roughly twice the number of records it still comes back in 10 secs.

Which RDBMS is this SQL for? Are you setting the SQL Engine in your WebFOCUS program?

And since you run it using sql passthrough, it should not make any difference in processing or elapsed time, since in both cases the dbms engine is doing all the work.
To be sure that it is indeed actually doing a sql passthrough, swith on traces and inspect them to see if the sql being sent on is correct and as expected.

Axion,

Just to confirm the point and to expand on the inference by Francis, you do have SQL SLQMSS or SQL SQLORA etc. before the "select" don't you?

If you don't then you are not using "passthru" as you think, and the select will be processed against a MAS file and not the RDBMS table itself. A small point but one that some might not realise.

If you do then I would recommend using GamPs suggestion of trapping the SQL trace.

T

Hi All,

Thanks for your input. I did confirm the passthrough with traces and it actually ended up being strange behavior on the Oracle side. It appears that the oracle engine wasn't generating a good execution plan when this particular set of parameters are selected. I have changed the sql statement slightly to include one extrac filter command and it results in 20sec responces. I know it really shouldn't make a difference but somehow it does in this particular case.

I have consulted some of my work buddies and they have experianced similar behavior before.

Thanks

Axion