Informix SQL Usage Tips

Speed up the execution of SQL

Use sort, or join in a 1.select statement

If you have a sort and join operation, you can select the data into a temporary table before processing the temporary table. Because temporary tables are built in memory, they are much faster than the table operations that are built on disk.

Such as:

SELECT time_records.*, case_name　
FROM time_records, OUTER cases　
WHERE time_records.client = "AA1000"　
AND time_records.case_no = cases.case_no　
ORDER BY time_records.case_no

This statement returns 34 sorted records that cost 5分钟42秒. and

SELECT time_records.*, case_name　
FROM time_records, OUTER cases　
WHERE time_records.client = "AA1000"　
AND time_records.case_no = cases.case_no　
INTO temp foo;　
SELECT * from foo ORDER BY case_no　
返回34条记录，只花费了59秒。

2. Use not in or not EXISTS statement

The following statement does not seem to have any problems, but it may be very slow to execute:

SELECT code FROM table1　
WHERE code NOT IN ( SELECT code FROM table2
如果使用下面的方法：
SELECT code, 0 flag　
FROM table1　
INTO TEMP tflag;　
然后：
UPDATE tflag SET flag = 1
WHERE code IN ( SELECT code　FROM table2　
WHERE tflag.code = table2.code ;
然后：
SELECT * FROM　
tflag　
WHERE flag = 0;

It may take a long time to look, but you'll find that it's not.

In fact, this approach is more efficient. It is possible that the first method will also be quick, in the case of indexing each of the related fields, but that is obviously not a good note.

3. Avoid excessive use of "or"

If possible, avoid excessive use of or:where a = "B" OR a = "C"

Slower than WHERE A in ("B", "C"). Sometimes even union will be faster than or.

4. Use index

An index is established on the fields of all join and order by. Index most of the fields in the Where.

WHERE datecol >= "this/date" AND datecol
<= "that/date"　要比　WHERE datecol BETWEEN
"this/date" AND "that/date" 慢。