Kbase P55344: MM Insertion error occurs with Complex SQL Query that uses L
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/1/2003 |
|
Status: Unverified
SYMPTOM(s):
Complex Left outer join SQL Query fails to complete.
MM- Insertion error (7718)
Increased the values of TPE_MM_BLOCKSIZE, TPE_MM_CACHESIZE, and TPE_MM_SWAPSIZE on the Database Server.
Query plan shows multiple tables are performing table scans.
CAUSE:
The WHERE clause with the key fields referenced is pulling all the records in and not using the proper indexes. The pulling of all records is causing the temp files to fill up and the query to take an exceeding long time.
The table scan occurs because the compound statement is using the following operators.
(1) <>
(2) IS NOT NULL
(3) NOT IN with list (use NOT EXISTS instead)
(4) LIKE with a date or number column
(5) LIKE with a comparison string that starts with a wild card
FIX:
The sql query must be reengineered to use more indexes.