Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class
1176 views

Improve SQL performance

Jump to solution

[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.
0 Likes
1 Solution

Accepted Solutions
Absent Member.
Absent Member.
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.

View solution in original post

0 Likes
2 Replies
Micro Focus Expert
Micro Focus Expert
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...
0 Likes
Absent Member.
Absent Member.
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.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.