Monday, 6 April 2009

Coping with the 32k view size limit

You are likely to have come across the 32k view size limit for procedure step import and export views unless you are generating for platforms that do not have the limit like pure windows clients, Java or .Net.

When the user needs to scroll through simple lists of data, a paging method that uses a ‘start from’ field is usually adequate, and if you want to display a lot of data on the client, you can still repeatedly call the server to retrieve batches of data, again using a hidden ‘start from’ field.

However there are occasionally situations where the above approach does not work. Examples that we have come across include impact analysis on the server which returns a set of object ids based on a complex trawl through the database. You need to return the complete set of data and the 32k view size limit can then result in truncation of the results.

We have therefore developed some techniques for handling this.

The first technique is to limit the export view to the identifiers of the returned rows. You can then call a different server to retrieve additional details, for example, the first server returns 1000 identifiers and then a second server is used to retrieve the additional details in batches of 100.

The second technique is to reduce the number of attribute views in the export view. For each attribute in a view, Gen adds 1 byte for the state flag and for generated C code there is an additional null terminator byte. Gen also adds an additional 6 bytes for each attribute in the export view. Consider the situation where you need to return ten 1 byte attributes in a group view. For C code, each row in the group view will occupy 90 bytes. However if you were to string the ten bytes together into a single 10 byte field, then each row would occupy 18 bytes, thus allowing a much larger group view size.

The third technique is to first call the server to populate the result set and return the first batch of data. Any remaining data is stored in a temporary location, for example a file. If the results set is incomplete, the client can then call the server again repeatedly to retrieve the remainder of the data from the temporary location.

1 comment:

Anonymous said...

Your first solution is so simple. I never thought about that. Thanks;)