Handling large data sets in SalesLogix data grids

In the LAN based SalesLogix client (version 7.0+), data grids can potentially contain hundreds or thousands of rows.  We recently ran into an issue where a standard account based tab containing a datagrid was to display over 200,000 rows of data.  When the account tab was activated, the client appeared to freeze.  The SalesLogix client appeared to handle the SQL call out and retrieval of the data set OK, however when rendering the data set to the grid control the client froze up for minutes.  During this time the SalesLogix process consumed oodles of memory and spiked CPU utilization, virtually bringing the workstation to a halt.

By modifying the account tab in the architect, and simply setting the data grid’s property of Use VSSC = True, the client immediately loaded the data and there was no apparent delay of lock up of the workstation.  If you have large data sets to display in a SalesLogix grid control, it is a good idea to turn this property of your data grid on.

 

Here is an explanation of VSSC (Virtual server side cursors) as explained in Sage’s SalesLogix Developer’s Reference Guide:

SalesLogix Virtual Server Side Cursors
Virtual Server-Side Cursors (VSSC) are targeted at improving large group performance and
memory usage in the SalesLogix network Client Groups / List views. Virtual server-side
cursors can be used anywhere large, read-only datasets are being used. For small datasets,
VSSCs can potentially degrade performance as client side cursors may be more efficient. For
editable datasets, a client-side cursor is required.

The SalesLogix Provider provides a server cursor implementation such that the client can
request data on an as-needed basis. These are not true server-side cursors. In real serverside
cursors, the process creates a cursor on the database server and keeps it open for the
duration of the query. For Groups, this generally means the life of the application. If you have
many Groups, you would have many server-side cursors active on the database server. Due
to memory requirements of the permanent connection and numerous cursors active at any
given time, database server performance and scalability will be affected. This benefit and
liability was present in SalesLogix v5.2.

On the client-side, server-side cursors benefit by providing pages of data continuously as you
scroll back and forth through the result set. Client memory usage and initial results
(assuming an efficient query) are significantly better in large Groups (thousands to millions of
rows). The disadvantage is that scalability of the server is reduced because of the multiple
client memory requirements on the server.

In the SalesLogix Provider, VSSC is a read-only, bidirectional scrolling server-side cursor
implementation. You will not be able to use an editable recordset to update the data, but you
will be able to scroll forward and backwards through the data. Just like a server-side cursor, it
requests pages of data from the server as needed. However, unlike server-side cursors,
SalesLogix will cache the data locally to save round trips if pages are revisited. This cache is
dynamic in nature, so old pages will get thrown away to reclaim memory as necessary.

VSSCs will be available by a change to the adUseServer in the ADO connection. This means
opening a new connection object as the standard SalesLogix connection will be client-side.

Technically, VSSC is similar to the Microsoft client cursor engine (CCE) used for client-side
recordsets (adUseClient in ADO).

If a user attempts to sort on a column that is not indexed, the query will execute slowly. The
best approach is to prevent the user from sorting on non-indexed columns when using large
groups that are in server-side mode. This is an admin-configurable feature which can be
disabled if desired.

VSSCs significantly reduce the memory footprint on a client computer (tens of times
depending on table size) and improve performance for large tables with thousands of rows.
VSSCs always work in read-only mode. However, VSSCs should be avoided when using small
tables because of overhead associated with caching and additional round-trips to the
Microsoft SQL Server. VSSCs do not work with tables that do not have the SLX PRIMARY ID
column.”

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Your error is coming from an included VBScript on the form with the grid you are modifying. The VB Script is called attachment support under the system family. Looking at line 73 of that script in a 7.5.2 system it shows:
    grdAttach.BindID = “INVALID_ID”
    Not sure why that would cause a problem and I am not sure if that is what in on line 73 of your system.

    Reply

Leave a Reply to Kris Halsrud Cancel reply

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!