Informix SQL 的使用技巧

Informix SQL 的使用技巧

一、加快sql的执行速度

1.select 语句中使用sort,或join
如果你有排序和连接操作，你可以先select数据到一个临时表中，然后再对临时表进行处理。因为临时表是建立在内存中，所以比建立在磁盘上表操作要快的多。
如：
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
这个语句返回34个经过排序的记录，花费了5分钟42秒。而：
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.使用not in 或者not exists 语句

下面的语句看上去没有任何问题，但是可能执行的非常慢：
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;
看上去也许要花费更长的时间，但是你会发现不是这样。
事实上这种方式效率更快。有可能第一种方法也会很快，那是在对相关的每个字段都建立了索引的情况下，但是那显然不是一个好的注意。
3.避免使用过多的“or"
如果有可能的话，尽量避免过多地使用or： WHERE a = "B" OR a = "C"
要比 WHERE a IN ("B","C") 慢。 有时甚至UNION会比OR要快。
4.使用索引

在所有的join和order by 的字段上建立索引。 在where中的大多数字段建立索引。
WHERE datecol >= "this/date" AND datecol <= "that/date" 要比 WHERE datecol BETWEEN "this/date" AND "that/date" 慢
二、在shell脚本中使用一个sql查询的结果
以下的是一个运行在sh/ksh下面的脚本。在online中，如果你想要更新一个有许多表的数据库的统计信息。这个脚本不太好。因为这个脚本只能单个处理数据库中的表，而不能同时处理大量的表。
例子：
# 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 systables where tabid >= 100 order by tabname;
EOF
exit 0
也许你已经注意到exit的返回值对不同的isql不是都相同，因此这样作不是很可靠，代替通过$?来检查返回值的更好的主意是将标准错误重定向到一个文件中，然后在这个文件中grep “error"。例如：
# 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
三、对一个计算产生的字段创建视图
应该这样写：
CREATE VIEW tst (cout) AS
SELECT ship_charge - totval
FROM orders WHERE ship_charge > 0;
四、只select 出数据库中的部分数据（例如10％）
问题：如果你想要得到一个select 语句正常返回的数据的一部分，例如：
SELECT firstname, lastname, city, state
FROM bigdatabase
WHERE state = "TX"
回答： 有一个方法可以返回一个近似值，只需要在where后加上：AND rowid=(trunc(rowid/x)*x)
其中的x代表你想要返回的总的记录的1/x。需要说明的是，这种方法只能返回一个近似的值，并且表中的数据在物理上分布的连续性。
五、创建一个表结构和永久表完全一致的临时表。 例如：CREATE TEMP TABLE mytemp (prodno LIKE product.prodno desc LIKE product.desc)
你可以使用如下的语句：
SELECT prodno, desc FROM product
WHERE ROWID = -1
INSERT INTO TEMP mytemp
六、更改serial类型下一次插入操作产生的值
我们知道serial类型的字段是系统自动增加的整数字段，那么怎样能控制下一个serial类型字段的值。想要下一个插入的serial类型的值比默认值大，可以用：
ALTER TABLE tabname MODIFY( ser_col_name SERIAL([new_start_number])
想要下一个插入的serial类型的值比默认的值要小，首先需要将serial类型重新置为1：
INSERT INTO table (serial_column) VALUES (2147483647);
INSERT INTO table (serial_column) VALUES (0); -- 重新从1开始!
....然后执行ALTER TABLE(就像上面的做法一样）。
七、在发生错误的时候终止sql脚本的执行
如果你创建了一个sql脚本，并且在UNIX命令行中使用以下的方式来执行这个脚本：
$ dbaccess <database> <脚本文件名>
这时，脚本中的所有的sql语句都会被执行，即使其中的一个sql语句发生了错误。例如，如果你脚本中为如下的语句：
BEGIN WORK;
INSERT INTO history
SELECT *
FROM current
WHERE month = 11;
DELETE FROM current
WHERE month = 11;
COMMIT WORK;
如果INSERT语句失败了，DELETE语句仍旧会继续执行。直到commit work。这样的后果可能会很严重。你可以通过设置一个环境变量来防止这种情况的发生。
DBACCNOIGN=1
八、设置decimal字段运算结果的精度
假定你使用dbaccess或者isql，设置环境变量DBFLTMASK=6 就可以设置为小数点后面6位，比如：
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
如果DBFLTMASK=7
value 0.8260755

九、遇到sysprocplan表被锁的提示
sysprocplan 表是sysmaster库中的一个表，其中记录存储过程经过优化的查询计划。每当查询树中的数据库对象有任何结构上的变化，这个查询计划就会自动更新。如 果对查询树中存在的任何表有update statistics操作，也会自动更新查询计划。在查询计划更新的时候，会对sysporcplan表中的相 关记录加锁。
注意：每次你对一个表更新统计的时候，也同时会更新于这个表相关的存储过程，即UPDATE STATISTICS FOR PROCEDURE 。
你可以作的另外一件事情就是：在存储过程中使用SET OPTIMIZATION LOW，这会让优化器在存储过程运行的时候不会试图去重新优化它。否则存储过程通常都会被重新优化一次。
十、删除掉表中重复的记录
假设“keycol”字段的值唯一，而且没有对表进行分片，并且没有其它的人正在删除"sometable"中的记录，你可以执行如下的SQL：
delete from sometable as a where rowid <> (select min(rowid) from sometable where keycol = a.keycol)
如果这个表使用表分片，rowid不存在，你还可以用如下的方法：
BEGIN WORK;
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
DELETE FROM Table WHERE 1 = 1;
INSERT INTO Table SELECT * FROM Temp1;
COMMIT WORK;
对于规模较小或中等的表，并且你有足够的存储空间来存储整个的临时表的时候，这种方法通常十分有效。
十一、加快SELECT COUNT(DISTINCT)的速度
通常“SELECT COUNT(DISTINCT)”这样的操作要花费比较长的时间，如果你这样作：
SELECT UNIQUE xxx INTO TEMP XXX " 然后再"SELECT COUNT(*) FROM TEMP XXX"
这样通常可以提高几倍的效率。

 

 

如何对SQLServer的海量数据进行优化

     我们都知道在涉及海量数据问题的时候，一般都需要用到数据仓储和数据优化技术。那么怎样对数据优化呢？本文将简单介绍一下如何对海量数据的优化。

      1.索引的使用：索引可以提高检索数据的速度，但同时也会降低写入数据的速度。

      在SQLServer中专门有一个索引表来存放我们建立的索引，一般情况下我们都将索引建立在数据类型是Int或是Char型的字段上。

      2.数据锁的使用：行锁，页锁，表锁等。（默认是页锁）

       切记 防止死锁的发生。

      3.数据库事务，数据并发方式。　

       一般将主从表放在事务中来处理。

     4.尽量不循环读取数据，适当的时候可以增加一些临时表，或在临时表中增加一些临时字段，来避免循环。

    5.横向切分

     把表中经常查询的和不常用的分开成几个表
 

    6.纵向切分

     把不同类型的分成几个表

    7.尽量不循环读取数据

       可以使用临时表和一些临时字段来避免循环。临时表可以防止数据在中途被修改，且插入临时表中的数据不写入日志，建索引也非常快，且不插入索引实体表。可将要插入的数据先插入临时表中，最后一次性导入正式表中。

   8.合理建立表的索引并利用表的索引

   9.关联的表要尽量少，数据量少的表尽量放在数据量多的表的前面

 10.避免where条件后面用函数

      如：sum等。

11.避免长事务(等待时间过长)

12.数据表的字段不能太多

13.多使用试图(View)

14.大量数据的处理尽量放在存储过程中来实现

15.OR语句的写法尽量规范

     如:select tid,tname from table where tid=5 and (tname='3' or tname='t')尽量写成select tid,tname from table where (tid=5 and tname='3' ) or (tid=5 and tname='t')

16.尽量不要在Sql语句中嵌套Sql语句(子查询)

17.在数据量大的情况下,为了提高检索速度,可适当增加表的冗余

18.用联接和Delete语句来代替Not In,In 比Not In要快