Kbase P51024: Improve performance on FOR EACH with ORACLE DataServer with NO-BIND-WHERE
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
Why same SELECT is faster from ORACLE PL/SQL than from Progress DataServer?
GOAL:
Using or not using ORACLE Bind variables
GOAL:
Improve performance on FOR EACH with ORACLE DataServer with NO-BIND-WHERE
FIX:
Checking the dataserv.lg file, you find the SELECT corresponding to a
FOR EACH tableA WHERE char_column = valueX
SELECT /*+ INDEX_ASC(T0 tableA##indexW) */ PROGRESS_RECID
unique_id_0,columnV,U##columnY,columnZ,PROGRESS_RECID
FROM
dbname.tableA T0 WHERE (U##char_column = upper(:1))
You run the following SELECT within a stored procedure from PL/SQL and it is a lot faster.
SELECT /*+ INDEX_ASC(T0 tableA##indexW) */ PROGRESS_RECID
unique_id_0,columnV,U##columnY,columnZ,PROGRESS_RECID
FROM
dbname.tableA T0 WHERE (U##char_column = upper(variableX)
The difference is that Progress DataServer uses BIND VARIABLES as it generates reusable SQL statements, while PL/SQL is not binding variables.
Not binding variable is resulting in faster Execute and Fetch calls.
You may change the default behavior of Progress DataServer either by issuing a
FOR EACH tableA WHERE char_column = valueX QUERY-TUNING(NO-BIND-WHERE)
or by using the connection parameter "-Dsrv qt_no_bind_where"