SQLServer Performance Optimization

Starting with CGI Advantage 2025 FS1, the default parameter value of the Versata parameter SelectMethod2 has been changed from cursor to direct. The Versata parameter SelectMethod2 adjusts the SQL Server JDBC selectMethod connection string parameter. When the parameter is set to direct, the database server sends the complete result set in a single response to the driver when responding to a query and no longer creates, executes, fetches, closes, or deallocate cursors for every query. This change improves the overall performance for sites that are using Microsoft SQL Server databases for their application OLTP database.

Disabling SelectMethod  

To disable or change the value back to cursor, the AppDataSourceTypeAttribute_Template.xml file can be modified by overriding the default AppDataSourceTypeAttribute_Template.xml from VLSAutomation folder. Please find the steps as below:

  1. Navigate to /RTFiles/<app>/Custom/Configuration/AppConfig/VLSAutomation/src/Templates directory. If the folder structure does not exist, create it. 

  2. Copy the AppDataSourceTypeAttribute_Template.xml file from the /RTFiles/<app>/Custom/Configuration/VLSConsole/Output/advantage directory and modify SelectMethod2 as cursor in the file.

<DSTypeAttributeID>84</DSTypeAttributeID>
<DSType>2</DSType>
<DSTypeAttributeName>SelectMethod</DSTypeAttributeName>
<DSModifiableAttribute>true</DSModifiableAttribute>
<DSTypeAttrNameKey>SelectMethod2</DSTypeAttrNameKey>
<DSAttrDefaultValue>cursor</DSAttrDefaultValue>

  1. Save the file and bounce the application.