1.
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE B.='??'2.
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE C.='??'3.
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE (B.='??' or C.='??')現象:上面1,2執行都很快,3很慢。把 1,2用 union聯起來也很快
想請教一下原因,謝謝大家我的一點想法是:
1,2條件,相當於內聯了。所以快。但3的 or讓sql server不會處理了。所以很慢不知大家怎麼看
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE B.='??'2.
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE C.='??'3.
SELECT * FROM A
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE (B.='??' or C.='??')現象:上面1,2執行都很快,3很慢。把 1,2用 union聯起來也很快
想請教一下原因,謝謝大家我的一點想法是:
1,2條件,相當於內聯了。所以快。但3的 or讓sql server不會處理了。所以很慢不知大家怎麼看
通常情况下, 用UNION 替换WHERE 子句中的OR 将会起到较好的效果. 对索引列使用OR 将造
成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column 没有被索引, 查询效
率可能会因为你没有选择OR 而降低.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
--如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
or有时很会影响性能,可能的话用union代替
LEFT JOIN B ON a=b
LEFT JOIN C ON a=c
WHERE (B.='??' or C.='??') OR影响太大,用UNION ALL
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”试下用这种
SELECT * FROM A
LEFT JOIN B ON a=b and b='??'
LEFT JOIN C ON a=c and C='??'
LEFT JOIN B ON a=b and b='??'
LEFT JOIN C ON a=c and C='??'
想错了
在B,C两列上建立复合索引
drop table TestA,TestB,TestC
create table TestA(Part int ,Unit nvarchar(2))
create table TestB(Part int ,Price int)
create table TestC(Part int ,Package int)declare @i int
set @i=1
while @i<10000
begin
insert into TestA(Part)
values(@i)
insert into TestB(Part,Price)
values(@i,@i)
insert into TestC(Part,Package)
values(@i,@i)
set @i=@i+1
end--select floor(rand()* 10000)create index idx_TestA_Part on TestA(Part)
--create index idx_TestA_Unit on TestA(Unit)
create index idx_TestB_Part on TestB(Part)
create index idx_TestC_Part on TestC(Part)
create index idx_TestB_Price on TestB(Price)
create index idx_TestC_Package on TestC(Package)--1.
SELECT * FROM TestA a
LEFT JOIN TestB b ON a.Part=b.Part
LEFT JOIN TestC c ON a.Part=c.Part
WHERE (b.Price>1 and b.Price<10)--2.
SELECT * FROM TestA a
LEFT JOIN TestB b ON a.Part=b.Part
LEFT JOIN TestC c ON a.Part=c.Part
WHERE c.Package>1 and c.Package<10--3.
SELECT * FROM TestA a
LEFT JOIN TestB b ON a.Part=b.Part
LEFT JOIN TestC c ON a.Part=c.Part
WHERE (b.Price>1 and b.Price<10) or (c.Package>1 and c.Package<10)請大家用這個測試
還需要做什麼處理,可以讓3的速度提上來嗎?