Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

More effecient SQL statement

Status
Not open for further replies.

wgechter

MIS
Jul 24, 2002
85
US
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';
 
If there is a solutions for this problem, there are more people able to assist you in the forum220
 
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
 
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';
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top