Problem:

The XDB Server settings can have a huge impact on run times - especially for importing large amount of data or querying large tables.  See the information below to determine how to configure your server settings.

Resolution:

Sort space - This should be very large for single user applications like creating a very large index.  For multi-user servers, this value will need to be more conservative because a sort buffer will be allocated for every query requiring a sort.  This includes queries with ORDER BY, DISTINCT, GROUP BY, etc.  The number of users and the amount of available memory on the server need to be factored in.  For very large indices, a value of 32000-64000 (32MB to 64MB)  is not unreasonable.  For a server with 20 users, a value around 4096 (4MB) would be more reasonable.

Max open files - Indices are comprised of two files.  Tables are a single file.  The limit on the size of physical files is 2GB.  Tables or indices that go over 2GB get split into multiple files.

Lock table size - 1024 K is usually enough.  The no space for locks error is to be avoided as it makes you start over.

Max open queries - 256 should be fine for individual user servers.  If there are additional users you should increase this as follows:  #  of users x 32.

Cache BP(0) - increased this to max as this is the buffer for all data (and should be set this way if using lots of data)

Cache BP(1) - increased to max as this related to the table index.