More effecient SQL statement

[wgechter]

The following is my select statement. How can I make this more effecient? My temp datafile is growing out of control and was curious how I can get this query to run better. Thanks, Wendi

SELECT DISTINCT A.PAT_EXT_ID, A.PAT_FIRST_NAME, A.PAT_LAST_NAME, B.RX_MED_NAME, B.RX_SIZE, B.RX_AMT, (ascii(B.RX_TYPE)), B.RX_ROUTE, B.D_RX, B.D_DC, C.PROBLEM_NAME, C.D_PROBLEM, C.PROBLEM_CODE1, C.PROBLEM_TYPE
FROM MRPA99 A, MRRX99 B, MRPL99 C, MRSC99 D
WHERE A.PTID(+)=B.PTID AND B.PTID(+)=C.PTID AND C.PTID(+)=D.PTID and A.PAT_EXT_ID = '69910.0';

 

[nagornyi]

If there is a solutions for this problem, there are more people able to assist you in the forum220

 

[sem]

I don't think that ANSI SQL forum may help to resolve performance issues as SQL itself doesn't define any way the data is processed.
If you suppose that you have all necessary indexes then try to gather statistics or otherwice your optimizer is completely blind.

Regards, Dima

http://seminihin.narod.ru

 

[ameima]

IT depends what you mean with more efficient

what you are doing is querying a mulitple joined set of 4 tables a, b,c, d, with three outer joins and one inner join

If you want it to run faster, you should use indices on the keys.

If you have a buffer problem, you should increase you buffer

Unless the order in which you make the joins matter, that could be the case if one table is significantly bigger than the other perhpas (ask on an sql forum), there is nothing that you can do about the sql statement

You could try and make a view first adn then query the view, that might make more efficient use of your memory.

I think that your query is basic, thus cannot be made more efficient. So that is my thought, please do refer to the SQl forum

;-)


SELECT DISTINCT A.PAT_EXT_ID, A.PAT_FIRST_NAME, A.PAT_LAST_NAME, B.RX_MED_NAME, B.RX_SIZE, B.RX_AMT, (ascii(B.RX_TYPE)), B.RX_ROUTE, B.D_RX, B.D_DC, C.PROBLEM_NAME, C.D_PROBLEM, C.PROBLEM_CODE1, C.PROBLEM_TYPE
FROM MRPA99 A, MRRX99 B, MRPL99 C, MRSC99 D
WHERE A.PTID(+)=B.PTID AND B.PTID(+)=C.PTID AND C.PTID(+)=D.PTID and A.PAT_EXT_ID = '69910.0';