Skip to main content

[Migrated content. Thread originally posted on 29 November 2011]

Hi,

I was just wondering if any one can advise on how to improve on SQL performance.

As the SQL database gets bigger then the number of records returned increases and the Visual Cobol App appears to slow down.

I use the OpenESQL Assistant to create my SQL cobol and embed in the program, see sample below:-

EXEC SQL
DECLARE CSR110 readonly CURSOR FOR SELECT
A.Co
,A.Code
,A.Type
,A.CodeDesc
,A.DateChanged
,A.Param
,A.NominalCode
FROM Codes A WITH (NOLOCK)
WHERE ( A.Co = :Codes-Co )
AND ( A.Type = :Codes-Type )
AND ( A.Code LIKE :ws-top-search )
ORDER BY A.CodeDesc DESC
END-EXEC
EXEC SQL OPEN CSR110 END-EXEC
PERFORM UNTIL SQLSTATE >= "02000"
EXEC SQL
FETCH CSR110 INTO
:Codes-Co
,:Codes-Code
,:Codes-Type
,:Codes-CodeDesc:Codes-CodeDesc-NULL
,:Codes-DateChanged:Codes-DateChanged-NULL
,:Codes-Param:Codes-Param-NULL
,:Codes-NominalCode:Codes-NominalCode-NULL
END-EXEC
*> Process data from FETCH
IF SQLSTATE
set Codes-Code to Codes-Code
END-IF
END-PERFORM
EXEC SQL CLOSE CSR110 END-EXEC

Any tricks or tips would be much appreciated.

Thanks

Neil.

[Migrated content. Thread originally posted on 29 November 2011]

Hi,

I was just wondering if any one can advise on how to improve on SQL performance.

As the SQL database gets bigger then the number of records returned increases and the Visual Cobol App appears to slow down.

I use the OpenESQL Assistant to create my SQL cobol and embed in the program, see sample below:-

EXEC SQL
DECLARE CSR110 readonly CURSOR FOR SELECT
A.Co
,A.Code
,A.Type
,A.CodeDesc
,A.DateChanged
,A.Param
,A.NominalCode
FROM Codes A WITH (NOLOCK)
WHERE ( A.Co = :Codes-Co )
AND ( A.Type = :Codes-Type )
AND ( A.Code LIKE :ws-top-search )
ORDER BY A.CodeDesc DESC
END-EXEC
EXEC SQL OPEN CSR110 END-EXEC
PERFORM UNTIL SQLSTATE >= "02000"
EXEC SQL
FETCH CSR110 INTO
:Codes-Co
,:Codes-Code
,:Codes-Type
,:Codes-CodeDesc:Codes-CodeDesc-NULL
,:Codes-DateChanged:Codes-DateChanged-NULL
,:Codes-Param:Codes-Param-NULL
,:Codes-NominalCode:Codes-NominalCode-NULL
END-EXEC
*> Process data from FETCH
IF SQLSTATE
set Codes-Code to Codes-Code
END-IF
END-PERFORM
EXEC SQL CLOSE CSR110 END-EXEC

Any tricks or tips would be much appreciated.

Thanks

Neil.
You should look at the following directives in the documentation for OpenESQL Database Access:

BEHAVIOR: setting this to the type of behavior that your application is designed for can provide significant performance enhancements.

STMTCACHE: set between 1 and 500

OpenESQL always uses prepare and execute statements rather than direct execution so that any statement executed multiple times will execute faster on the 2nd and subsequent executions. Only statements that cause server side execution are cached (ie open cursor, insert, delete, etc are cached since they cause execution of a SQL statement at the server)

You can also look at using stored procedures and array fetches and array inserts.

I hope that this helps...

[Migrated content. Thread originally posted on 29 November 2011]

Hi,

I was just wondering if any one can advise on how to improve on SQL performance.

As the SQL database gets bigger then the number of records returned increases and the Visual Cobol App appears to slow down.

I use the OpenESQL Assistant to create my SQL cobol and embed in the program, see sample below:-

EXEC SQL
DECLARE CSR110 readonly CURSOR FOR SELECT
A.Co
,A.Code
,A.Type
,A.CodeDesc
,A.DateChanged
,A.Param
,A.NominalCode
FROM Codes A WITH (NOLOCK)
WHERE ( A.Co = :Codes-Co )
AND ( A.Type = :Codes-Type )
AND ( A.Code LIKE :ws-top-search )
ORDER BY A.CodeDesc DESC
END-EXEC
EXEC SQL OPEN CSR110 END-EXEC
PERFORM UNTIL SQLSTATE >= "02000"
EXEC SQL
FETCH CSR110 INTO
:Codes-Co
,:Codes-Code
,:Codes-Type
,:Codes-CodeDesc:Codes-CodeDesc-NULL
,:Codes-DateChanged:Codes-DateChanged-NULL
,:Codes-Param:Codes-Param-NULL
,:Codes-NominalCode:Codes-NominalCode-NULL
END-EXEC
*> Process data from FETCH
IF SQLSTATE
set Codes-Code to Codes-Code
END-IF
END-PERFORM
EXEC SQL CLOSE CSR110 END-EXEC

Any tricks or tips would be much appreciated.

Thanks

Neil.
Hi Neil,

To add to what Chris has said, reducing number of round trips to the server should improve performance.

The help topic Host Arrays provides some examples of how to perform array fetches.

Knowledge Base Article 33321 also provides further specific examples of OpenESQL directives and features - see the "Code Optimization" section. If you're using SQL Server, then the "Performance Monitoring" section may be of use too.


SimonT.
Terms & ConditionsAccessibility statement