时间戳

首页 > 解决方案 > 最小化查询获取时间

sql - 最小化查询获取时间

问题描述

以下是我的查询，从 Informix 数据库中获取 900 万条记录需要 10 分钟

select x.no ,x.code ,x.type ,sum(x.val) as val from 
(SELECT a.d_no as no,a.s_code as code, b.type as type, sum(a.value) as val                  
FROM TABLE1 a, TABLE2 b,TABLE3 c
WHERE c.segment_id=b.cat AND c.b_id=b.sub_cat
AND a.terminated_date is null AND a.d_no=c.d_no AND a.s_code=c.s_code
GROUP BY b.type ,b.cat,b.sub_cat, no, code 
union all
SELECT a.d_no as no,a.s_code as code,     
b.type as type,sum(a.value) as val                  
FROM TABLE4 a, TABLE2 b, TABLE3 c
WHERE c.segment_id=b.cat AND c.b_id=b.sub_cat
AND a.terminated_date is null AND a.d_no=c.d_no AND a.s_code=c.s_code
GROUP BY b.type ,b.cat,b.sub_cat, no, code) x
group by x.code ,x.no ,x.type

标签： sqlinformix

解决方案

  1. 尝试重写您的查询，这样“Informix 优化器”就不会出现混淆/歧义：
SELECT x.no, x.code, x.type,
SUM(x.val) val 
FROM (
   SELECT a.d_no no, a.s_code code, b.type type, sum(a.value) val                  
   FROM TABLE1 a
   JOIN TABLE3 c ON (a.d_no=c.d_no AND a.s_code=c.s_code)
   JOIN TABLE2 b ON (c.segment_id=b.cat AND c.b_id=b.sub_cat)
   WHERE a.terminated_date IS NULL 
   GROUP BY 2,1,3   
  UNION ALL
   SELECT a.d_no no,a.s_code code, b.type type, sum(a.value) val                  
   FROM TABLE4 a 
   JOIN TABLE3 c ON (a.d_no=c.d_no AND a.s_code=c.s_code)
   JOIN TABLE2 b ON (c.segment_id=b.cat AND c.b_id=b.sub_cat)
   WHERE a.terminated_date IS NULL 
   GROUP BY 2,1,3) x
GROUP BY 2,1,3  --same thing as using column names btw
  1. 在运行查询1之前运行此语句：

设置解释；

您将能够看到“查询计划”。它解释了如何使用索引以及正在工作的连接类型等。看看 Informix 是否可以通过使用该表的索引之一来获取表中的记录，或者不幸的是，Informix 是否必须读取，这非常有用表按顺序获取它们。

推荐阅读