Informix SQL usage tips

1. Accelerate SQL Execution

1. Use sort or join in the select statement
If you have sorting and join operations, you can first select data to a temporary table and then process the temporary table. Because temporary tables are created in the memory, it is much faster than creating them on the disk.
For example:
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, which took 5 minutes and 42 seconds. 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
It takes only 59 seconds to return 34 records.
2. Use the not in OR not exists statement

The following statement does not seem to have any problems, but may be executed very slowly:
SELECT code FROM table1
WHERE code not in (SELECT code FROM table2
If you use the following method:
SELECT code, 0 flag
FROM table1
Into temp tflag;
Then:
UPDATE tflag SET flag = 1
WHERE code IN (SELECT code FROM table2
WHERE tflag. code = table2.code;
Then:
SELECT * FROM
Tflag
WHERE flag = 0;
It may take a longer time, but you will find that this is not the case.
In fact, this method is more efficient. It is possible that the first method will also be very fast. It is when an index is created for each related field, but that is obviously not a good note.
3. Avoid using too many "or"
If possible, avoid excessive use of or: WHERE a = "B" OR a = "C"
It is slower than WHERE a IN ("B", "C. Sometimes UNION is faster than OR.
4. Use Indexes

Create an index on all join and order by fields. Create an index for most fields in the where clause.
WHERE datecol> = "this/date" AND datecol <= "that/date" is slower than WHERE datecol BETWEEN "this/date" AND "that/date"
2. Use an SQL query result in a shell script
The following is a script running under sh/ksh. In online, if you want to update the statistics of a database with many tables. This script is not very good. This script can only process a single table in the database, but cannot process a large number of tables at the same time.
Example:
# Update_em
# Run update statistics on a table by table basis
# DATABASE = $1
If [-z "$ DATABASE"]
Then
Echo "usage: update_em dbname"> & 2
Exit 1
Fi
Isql $ DATABASE-<dev/null | isql $ DATABASE-
Output to pipe "cat" without headings
Select "update statistics for table", tabname ,";"
From orders Ables where tabid> = 100 order by tabname;
EOF
Exit 0
You may have noticed that the return value of exit is not the same for different isql, so this is not very reliable, instead of using $? A better idea is to redirect a standard error to a file and then grep "error" in the file ". For example:
# Generate the data
Isql-qr < Stage. rep 2> $ stage. err
Database $ database;
Select...
!
# Check for errors
If grep-I "error" $ stage. err>/dev/null
Then
... Error_handler...
Fi
3. Create a view for a calculated Field
It should be written as follows:
Create view tst (cout)
SELECT ship_charge-totval
FROM orders WHERE ship_charge> 0;
4. select only part of the data in the database, such as 10%)
Problem: If you want to get a part of the data normally returned by a select statement, for example:
SELECT firstname, lastname, city, state
FROM bigdatabase
WHERE state = "TX"
A: There is a way to return an approximate value. You only need to add: AND rowid = (trunc (rowid/x) * x after where)
X represents 1/x of the total record you want to return. It must be noted that this method can only return an approximate value, and the data in the table is physically distributed continuously.
5. Create a temporary table with the same structure as a permanent table. Example: create temp table mytemp (prodno LIKE product. prodno desc LIKE product. desc)
You can use the following statement:
SELECT prodno, desc FROM product
Where rowid =-1
Insert into temp mytemp
Vi. Change the value generated by the next insert operation of the serial type
We know that the serial field is an integer field automatically added by the system. How can we control the value of the next serial field. If you want to insert a value of the serial type greater than the default value, you can use:
Alter table tabname MODIFY (ser_col_name SERIAL ([new_start_number])
To insert a value of the serial type smaller than the default value, you must first reset the serial type to 1:
Insert into table (serial_column) valuees (2147483647 );
Insert into table (serial_column) VALUES (0); -- start from 1 again!
... Then execute alter table (just like the above practice ).
7. Terminate SQL script execution when an error occurs
If you have created an SQL script and run it in the UNIX Command Line in the following ways:
$ Dbaccess <脚本文件名>
At this time, all SQL statements in the script will be executed, even if one of the SQL statements has an error. For example, if your script contains the following statement:
Begin work;
Insert into history
SELECT *
FROM current
WHERE month = 11;
Delete from current
WHERE month = 11;
Commit work;
If the INSERT statement fails, the DELETE statement continues to be executed. Until commit work. Such consequences may be very serious. You can set an environment variable to prevent this situation.
DBACCNOIGN = 1
8. Set the accuracy of the decimal Field Calculation Result
If you use dbaccess or isql, you can set the environment variable DBFLTMASK to 6 to the next six digits of the decimal point, for example:
Create temp table t
(Col_a DECIMAL (8, 4) not null,
Col_ B DECIMAL (8, 4) NOT NULL,
Col_c DECIMAL (8, 4) NOT NULL
);
Insert into t VALUES (1.2345, 3.4567, 5.6789 );
SELECT (col_a + col_ B)/col_c AS value FROM t;
Value 0.826075.
If DBFLTMASK = 7
Value 0.8260755.

9. The sysprocplan table is locked.
The sysprocplan table is a table in the sysmaster database. It records the optimized query plan of the stored procedure. The query plan is automatically updated whenever the database objects in the query tree have any structural changes. If any table in the query tree has the update statistics operation, the query plan is automatically updated. When the query plan is updated, the related records in the sysporcplan table are locked.
Note: each time you update statistics for a table, the table-related stored PROCEDURE, update statistics for procedure, is also updated.
Another thing you can do is to use set optimization low in the stored procedure, which will prevent the optimizer from trying to re-optimize it when running the stored procedure. Otherwise, the stored procedure is usually optimized once again.
10. Delete duplicate records in the table
Suppose that the value of the "keycol" field is unique and there is no partition for the table, and no one else is deleting the records in "sometable", you can execute the following SQL:
Delete from sometable as a where rowid <> (select min (rowid) from sometable where keycol = a. keycol)
If this table uses table sharding, The rowid does not exist. You can also use the following method:
Begin work;
Select distinct * FROM Table into temp Temp1;
Delete from Table WHERE 1 = 1;
Insert into Table SELECT * FROM Temp1;
Commit work;
This method is usually effective when you have a small or medium table and you have enough storage space to store the entire temporary table.
11. Accelerate select count (DISTINCT)
Generally, operations such as select count (DISTINCT) take a long time. If you do this:
Select unique xxx into temp xxx "and then" select count (*) from temp xxx"
This usually improves the efficiency by several times.