Possible performance increase for Visual LANSA on Sybase databases
|Date:||18 December 2008|
|Product/Release:||Visual LANSA (All Versions)|
|Abstract:||Changing the default page size from 2048 to 4096 may increase performance in VL IDE and runtime applications|
|Submitted By:||LANSA Technical Support|
Prior to V10, Visual LANSA databases created on Sybase were created with a default pagesize of 2048. This was appropriate for the version of Sybase that was included at that time, but currently it may be too low. Since Version 10, the default pagesize for new databases is 4096.
If Visual LANSA, or a VL runtime application has been continuously upgraded from an older version, the database may still be set to 2096. Changing it to 4096 may offer improvements in all aspects involving database access.
Confirming the current page size:
Start Sybase Central from the Sybase SQL Anywhere, or SQL Anywhere start menu folder. In Sybase Central, use File, Connect and then specify the User/Password and then select your LANSA database from the ODBC Datasource dropdown.
Now right click on the database on the left window and select properties. The resulting dialog box should show the page size:
If this shows 2048 or 1024, you may benefit from changing to 4096.
Changing the page size:
Note that this is an advanced procedure and should ideally be done only by a database administrator. The specific steps to change the page size vary between Sybase versions but general instructions are as follows:
- Back up the original database
- Unload the original database data and structure to file (SQL commands)
- Delete original database
- Create a new database with the same name and attributes as the original database (with the exception of Page size).
- Once created, open Interactive SQL and run the unload script created above
The unload script may require some editing to load the data correctly into the new database (in some versions of Sybase, the script is created with some minor syntax errors). You may also need to change the ODBC DSN to use the new page size as per the following example: