Passing results from an SQL statement Options

[Richard Bassett]

> Hi I think you need to use the DYNAMIC keyword

I'm sure Chris has a reason for saying that but right now I can't really see what it could be?
The only variability in your statement seems to be a single host variable value, which certainly should not need dynamic SQL (and in fact making it dynamic could hurt performance).

Are you posting exactly what you have in your logic?
Have you checked for any dodgy characters in the text of your logic?
Can you switch trace on and see the actual SQL passed to Oracle?

[DARREN]

The other thing to try is the removal of the explicit request column names as PROIV will work out the column names that you need. Ensure the the column names defined in the file definition match those of on the table.

[chuckl]

Thanks for all the responses, I think I finally got it to work. I was originally attempting this in a many time update cycle, when I changed to a Selection Cycle(file) it worked fine. Is it possible that this only works for Selection cycles?

[Chris Mackenzie]

The error message relates to a malformed sql statement. Seems p4 is not parsing it correctly.

Moving to dynamic should sort it. Yes there is a performance hit on reparsing statements and the main reason to use dynamic is when varying column names etc.

also with this join can't use p4 type 1 sql - need to define the cols. Not sure how pro4 is meant to work out the requirement in this case!

[chuckl]

This is what I have so far. Any words of wisdom on performance? The tables involved used to be local pro-isam tables, I converted them to Oracle and the database is now centrally located. The expected number of rows retrieved is usually in the 200 to 500 range but can get as high as 1000, what I'm seeing is the more rows retrieved the the slower it gets.

this is in the entry logic of a Selection Cycle(file) for a list box.

IF $QUEUE_TYPE = '5' THEN
$QT = "('1','2','3','4')"
ELSE
$QT = "('" + $QUEUE_TYPE + "')"
ENDIF
//
CASE $SORT_BY
WHEN '1' : $STATEMENT = "PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'YYYYMMDD')"
WHEN '2' : $STATEMENT = "PATQUEUE_URGENT, PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'YYYYMMDD')"
WHEN '3' : $STATEMENT = "TO_CHAR(A.CLAIM_DOS,'YYYYMMDD'), PATQUEUE_TYPE, ACCOUNT_NO"
WHEN '4' : $STATEMENT = "PM_P_NAME, PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'YYYYMMDD')"
WHEN '5' : $STATEMENT = "PATQUEUE_READ, PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'YYYYMMDD')"
OTHERWISE : $STATEMENT = "PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'YYYYMMDD')"
ENDCASE
//
SQL DYNAMIC
SELECT A.ACCOUNT_NO, A.TICK_SHEET_NO, A.PATQUEUE_TYPE, TO_CHAR(A.CLAIM_DOS,'MM/DD/YYYY'),
A.CLINIC_CODE, A.CLAIM_NO, A.BATCH_ID, A.ACCOUNT_TYPE, A.PATQUEUE_REVIEW,
A.PATQUEUE_URGENT, A.UPDATE_TIME, TO_CHAR(A.UPDATE_DATE,'MM/DD/YYYY'), A.UPDATE_USER,
A.PATQUEUE_READ, B.PM_P_NAME, B.PM_P_FNAME, B.PM_P_MNAME
FROM PATQUEUE A, PMPAT B
WHERE A.ACCOUNT_NO = B.PM_R_CODE AND A.CLINIC_CODE = :$CLINIC_ID
AND A.PATQUEUE_TYPE IN :$QT
ORDER BY :$STATEMENT
ENDSQL

[Chris Mackenzie]

Hi,

this doesn't need to be in a selection cycle. Normally use updates.

Need to see the execution plan of your query and know what indices exist etc.

Post it up and am sure someone will have some suggestions.

[chuckl]

Here is my Query Plan. I have been working with our DBA and have applied any applicable indexes. Testing still shows that the more rows there are the slower it is. Our application and the database will now be separate (connected by a WAN), could this hinder performance, we have plenty of bandwith? Any knowledge greatly appreciated.

CODE

PLAN STEP COST ROWS BYTES PARTITION PARTITION STOP

SELECT STATEMENT 900 443 50502 0 0
SORT (ORDER BY) 900 443 50502 0 0
NESTED LOOPS 899 443 50502 0 0
TABLE ACCESS (BY INDEX ROWID)--PATQUEUE (TABLE) 12 443 37655 0 0
INDEX (RANGE SCAN)--IND2_PATQUEUE (INDEX) 1 443 0 0 0
TABLE ACCESS (BY INDEX ROWID)--PMPAT (TABLE) 2 1 29 0 0
INDEX (UNIQUE SCAN)--IND_PMPAT (INDEX (UNIQUE)) 1 1 0 0 0

[Richard Bassett]

> what I'm seeing is the more rows retrieved the the slower it gets.

I'm a little confused by that. Is that not exactly what you'd expect? Do you mean the more rows there are in the database or the more rows that are actually selected? Are you saying it gets slower exponentially (eg: twice as many records is four times as slow)?

> Our application and the database will now be separate (connected by a WAN), could this hinder performance, we have plenty of bandwith?

Yes, that can hinder performance, latency can be as much of a problem as bandwidth (although this is more true when writing to the database than with pure retrieval).

[chuckl]

> what I'm seeing is the more rows retrieved the the slower it gets.

I'm a little confused by that. Is that not exactly what you'd expect? Do you mean the more rows there are in the database or the more rows that are actually selected? Are you saying it gets slower exponentially (eg: twice as many records is four times as slow)?

> Our application and the database will now be separate (connected by a WAN), could this hinder performance, we have plenty of bandwith?

Yes, that can hinder performance, latency can be as much of a problem as bandwidth (although this is more true when writing to the database than with pure retrieval).

Sorry for the confusion. It is the number of rows selected. This query will return on average 300 to 400 rows and very rarely, if ever, more than 1000. An example from my testing is it takes 5 to 7 seconds to return approximateley 500 rows. You're right, that is what I should expect but I was hoping for better overall performance.

thanks for your help
chuckl

[matc]

How long does the query take if you run it directly on the Oracle server using SQLPLUS or some other IDE?

That would focus where the problem lay with the SQL, the link to the box or PROIV.

[Richard Bassett]

100 rows a second throughput is lousy performance.
Do what matc just suggested.

[chuckl]

The query runs fine in our development environment, the application server and database server are both in the same room and has no problem with performance. I ran a trace on the SQL and noticed a 7 second jump around line 22. Another piece to the puzzle?

chuckl


105440.355-664:172
105440.355-664:===== qgDbMgrGetQ
105440.355-664:>>>>> dbApiCommit
105440.355-664:>>>>>> OraCommit
105440.355-664:======= OraCommit: Commit OK - rc = 0

105440.355-664:<<<<<< OraCommit
105440.355-664:18167520
105440.355-664:<<<<< dbApiCommit
105440.355-664:18290348
105440.355-664:<<<< qgSqlFCommit
105440.355-664:18570255
105440.355-664:sysfcm: Incremented Transaction Counter 32
105440.355-664:>>>> qgSqlExitLs
105440.355-664:>>>>> qgCmdReset
105440.355-664:>>>>>> dbApiCmdReset
105440.355-664:<<<<<< dbApiCmdReset
105440.355-664:472
105440.355-664:<<<<< qgCmdReset
105440.355-664:107895
105440.355-664:<<<< qgSqlExitLs
105440.355-664:215692
105447.355-664:==== qgEnvFlag
105447.355-664:>>>> qgSqlFProc
105447.355-664:>>>>> qgDbMgrInitErrCB
105447.355-664:<<<<< qgDbMgrInitErrCB
105447.355-664:1080
105447.355-664:>>>>> qgGnGetFStm
105447.355-664:>>>>>> qgGnFnProc
105447.355-664:<<<<<< qgGnFnProc
105447.355-664:457
105447.355-664:<<<<< qgGnGetFStm
105447.355-664:110355
105447.355-664:qgSqlFProc -TIB exists pFcb->frcb.tib = 00DD2890
105447.355-664:qgSqlFProc - FCB = 00D80534, PFCB->FCRB = 00008060
105447.355-664:===== qgCmdParam
105447.355-664:===== qgCmdParam
105447.355-664:===== qgCmdParam
105447.355-664:===== qgPrmCbFAccess
105447.355-664:>>>>> qgPrmInit
105447.355-664:====== qgStmIsJoin
105447.355-664:<<<<< qgPrmInit
105447.355-664:46853
105447.355-664:>>>>> qgPrmPreFSql
105447.355-664:<<<<< qgPrmPreFSql

[DARREN]

Chuck

Are the rows being selected for update intent (updated/changed), or is the selection purely to drive a process. It is is the latter then an experiment you could try is to create a view on the table with your join. Execute this at the SQL level to confirm performance is as expected. Then map a PROIV file definition onto the view (using the alternate file name property in the file definition that references the view).

[Chris Mackenzie]

dunno.

if you're sure it's not network then this link may be of interest

http://www.praetoriate.com/oracle_tips_sor..._operations.htm