- Use indexes where possible and verify this with _Sql_Qplan (See Solution 20007). You can eventually run UPDATE STATISTICS to improve performance (See Solution 20952).
- Make sure there is enough disk space available where the temp tables are being created. You can specify the location with the environment variable WRKDIR (See Solution 19920).
- Do not use ORDER BY if not needed. Depending on the query plan, it might create huge temp tables for sorting.
- If you are hitting the 500MB limit for the temp tables (See Solution 21206), then increase the limit by:
1) specifying the TPE_MM_SWAPSIZE environment variable if you are using Progress 9.1D05 or earlier. The default value is 500000 (500MB).
Note: This method should be used as last resort as there is limited support for it. The database needs to be restarted to take this change into account.
- OR -
2) specifying the -SQLTempDisk database startup parameter if you are using Progress 9.1D06 or later. The default value is 500000 (500MB).
