如何加快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查询的结果
如:取昨天的日期到ReportDate
set `dbaccess reportdb<<!
SELECT FIRST 1 TODAY-1 FROM sqlcmd WHERE 1=1;
!`
ReportDate=$2
set后,数据结果排列的方式为:字段名1,字段名2…….,字段1的值,字段2的值
如果SELECT后有2个字段,则$1,$2为字段的名称,$3,$4位取出的结果。
为什么不能对一个计算产生的字段创建视图
问题:为什么我不能创建视图:
CREATE VIEW tst AS
SELECT ship_charge - totval cout
FROM orders WHERE ship_charge > 0;
回答:你应该这样写:
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(或者1=0)
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
注:还可以用类型转换
SELECT ((col_a + col_b) / col_c)::DECIMAL(8,7) AS value FROM t
为什么我们有时会遇到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" 这样通常可以提高几倍的效率。
--------------------------------------------------------
本文转载,蓝色字体部分为本人整理