我正在对执行非常缓慢的非常复杂查询的一部分进行故障排除。
按照编写的方式,查询需要40分钟才能执行,并返回约141,000个结果。我正在使用SQL Server 2005(是)。
代码中有问题的部分是JOIN中的多个OR语句。这是原始查询。请记住,由于空间原因,SELECT子句包含大量的处理,因此我不包括在内。该查询随后又联接到另外三个表,但是由于它们都是简单联接,所以我不包括它们。
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t2.date_1 = t1.date
INNER JOIN table3 t3 on ( t3.sid = t1.sid )
LEFT JOIN table4 t4 ON
((t1.cid != 26 AND t4.srscid = t1.cid) OR (t1.cid = 26 AND t1.pcode = t4.pcode) )
AND (t1.sid = t4.section OR t1.sid = t4.r_section)
and t1.nprice >= t4.min_breakpoint AND t1.nprice <= t4.max_breakpoint
and t1.date >= t4.start AND t1.date <= t4.end
为了加快速度,参考其他我在这里看到的建议,我将两个OR语句转换为UNION。
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t2.date_1 = t1.date
INNER JOIN table3 t3 on ( t3.sid = t1.sid )
LEFT JOIN table4 t4 ON
((t1.cid != 26 AND t4.srscid = t1.cid) )
AND (t1.sid = t4.section OR t1.sid = t4.r_section)
and t1.nprice >= t4.min_breakpoint AND t1.nprice <= t4.max_breakpoint
and t1.date >= t4.start AND t1.date <= t4.end
UNION
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t2.date_1 = t1.date
INNER JOIN table3 t3 on ( t3.sid = t1.sid )
LEFT JOIN table4 t4 ON
((t1.cid = 26 AND t1.pcode = t4.pcode) )
AND (t1.sid = t4.section OR t1.sid = t4.r_section)
and t1.nprice >= t4.min_breakpoint AND t1.nprice <= t4.max_breakpoint
and t1.date >= t4.start AND t1.date <= t4.end
上面的查询确实执行得更快,在约30分钟内执行,但返回了307,000个结果。
我的案例与其他地方遇到的示例之间唯一的区别是OR语句出现在一组括号中。
谁能帮助解决为什么UNION从OR联接返回这么多不同结果的问题?
谢谢。
这是因为这2个查询左连接到table4。 但是在on子句中有cid 26的标准。
为了说明这一点,请看以下简化示例的输出:
--
-- sample data
--
create table tbl1
(
cid int primary key,
pcode varchar(30) not null
);
create table tbl2
(
id2 int identity(1,1) primary key,
srscid int,
pcode varchar(30) not null
);
insert into tbl1 (cid, pcode) values
(26, 'a'),
(31, 'b'),(32, 'c'),(33, 'd');
insert into tbl2 (pcode, srscid) values
('a', 26),('b', 26),
('x', 31),('y', 32);
GO
受影响的8行
-- query 1
-- cid != 26, srscid = cid
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.srscid = t1.cid and t1.cid != 26)
GO
cid | pcode | id2 | pcode2
-:| :---- | ---::-----
26 | 一个| 空 | 空值
31 | b | 3 | X
32 | c | 4 | ÿ
33 | d | 空 | 空值
-- query 2
-- cid = 26, pcode
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.pcode = t1.pcode and t1.cid = 26)
GO
cid | pcode | id2 | pcode2
-:| :---- | ---::-----
26 | 一个| 1 | 一种
31 | b | 空 | 空
32 | c | 空 | 空
33 | d | 空 | 空值
-- query 3
-- cid != 26 or cid = 26
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on ((t2.srscid = t1.cid and t1.cid != 26) or
(t2.pcode = t1.pcode and t1.cid = 26))
GO
cid | pcode | id2 | pcode2
-:| :---- | ---::-----
26 | 一个| 1 | 一种
31 | b | 3 | X
32 | c | 4 | ÿ
33 | d | 空 | 空值
-- query 4
-- union query 1 & 2
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.srscid = t1.cid and t1.cid != 26)
union
select t1.*, t2.id2, t2.pcode
from tbl1 t1
left join tbl2 t2
on (t2.pcode = t1.pcode and t1.cid = 26)
GO
cid | pcode | id2 | pcode2
-:| :---- | ---::-----
26 | 一个| 空 | 空值
26 | 一个| 1 | 一种
31 | b | 空 | 空值
31 | b | 3 | X
32 | c | 空 | 空值
32 | c | 4 | ÿ
33 | d | 空 | 空值
-- query 4
-- union with where clauses
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on t2.srscid = t1.cid
where t1.cid != 26
union all
select t1.*, t2.id2, t2.pcode
from tbl1 t1
left join tbl2 t2
on t2.pcode = t1.pcode
where t1.cid = 26
order by t1.cid
GO
cid | pcode | id2 | pcode2
-:| :---- | ---::-----
26 | 一个| 1 | 一种
31 | b | 3 | X
32 | c | 4 | ÿ
33 | d | 空 | 空值
db <> 在这里拨弄
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。