两表关联字段查询，怎么提高效益，谢谢

[蓝色光芒]

A表
ID（IDENTITY 有索引）,Name（有索引）,Tel1(int 有索引),Tel2(int 有索引）,Tel3(int 有索引）,Tel4(int 有索引）
B表
AID（有索引）, Money , PayType(int 有索引） , nDate(DateTime有索引）

A,B数据都庞大，
想实现这几种查询，该怎么优化？（这里需要A的所有列，所以*这里省略不了）
①
Select * from A where ID in (Select AID from B) order by ID
②
Select Top 1 * from A where Tel1=8000000 or Tel2=8000000 or Tel3=8000000 Tel4=8000000
（Tel1..Tel4）整个数据表中已经排除重复
③
Select A.*,B.* from where A.ID=B.AID and B.nDate>#2008-1-1# and B.nDate<#2008-8-1# and B.PayType=1 order by A.ID desc

大侠帮帮我，优化一下，谢谢。。。

[comszsoft]

提示：
1. 尽量避免 select * ，可以用 select 字段1,字段2 只返回需要的字段
2. 看看执行计划

-- ① 这个应该比in好用
select a.* from a 
    inner join b on a.id = b.aid
    order by a.id 

-- ② 试试这个
select top 1 * from 
(
  select top 1 * from a where tel1 = 8000000 union all 
  select top 1 * from a where tel2 = 8000000 union all
  select top 1 * from a where tel3 = 8000000 
) tmptb

-- ③ 好像这个没什么可以优化的了

[hero_girl]

进来学习学习

[水族杰纶]

EXISTS/NOT EXISTS一定比IN/NOT IN的效率高吗？
经常别人说EXISTS比IN快！NOT EXISTS比NOT IN快！然而事实真的如此么？
我们先讨论IN和EXISTS。
select * from t1 where x in ( select y from t2 )
事实上可以理解为：
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
select * from t1 where exists ( select null from t2 where y = x )
可以理解为：
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD!
end if
end loop
——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

[lff642]

Select * from A where ID in (Select AID from B) order by ID
-->
改成关联子查询并且使用EXISTS代替IN关键字,速度会不会快一点,

[lff642]

Select Top 1 * from A where Tel1=8000000 or Tel2=8000000 or Tel3=8000000 Tel4=8000000
-->
你这样写.好像不要索引啊.

使用UNION ALL来替换试试看.


select top 1 * from (
select * from A where Tel1 = 8000000
union all
select * from A where Tel2 = 8000000
union all
select * from A where Tel3 = 8000000
union all
select * from A where Tel4 = 8000000
) A

[lff642]

Select A.*,B.* from where A.ID=B.AID and B.nDate>#2008-1-1# and B.nDate <#2008-8-1# and B.PayType=1 order by A.ID desc
-->
你的条件都是先查询出B.nDate在2008-1-1和2008-8-1并且B.PayType=1
不知可否?
先查询出B表中,满足上面条件的数据后,再跟A表联接.
速度是否会快. 不太清楚.
以上代表个人看法.如有错误,请指出.

[蓝色光芒]

谢谢 Iff642和wufeng4552

[ws_hgo]

看来LZ
搞懂了
恭喜恭喜

[蓝色光芒]

刚才试了一下
Select Top 1 * from A where Tel1=8000000 or Tel2=8000000 or Tel3=8000000 Tel4=8000000
30万测试记录,执行3000次,花时间23125豪秒

select top 1 * from (
select * from A where Tel1 = 8000000
union all
select * from A where Tel2 = 8000000
union all
select * from A where Tel3 = 8000000
union all
select * from A where Tel4 = 8000000
) A

花时间 21140豪秒,
是要快那么一点点...晕菜

[蓝色光芒]

这样测试,是不是说明 这样写同样使用了索引的呢?
Select * from Guest where Tel1=8000000 or Tel2=8000000 or Tel3=8000000 Tel4=8000000

首先搞个存储过程

CREATE procedure TestSpeed1
@Count bigint
as
while @Count>0
begin
Select @Count=@Count-1;
if Exists(SELECT * FROM Guest WHERE (Tel1=@Count) OR (Tel2=@Count) OR (Tel3=@Count) OR (Tel4=@Count))
Update Guest set Sex='女' where ID=0
end
GO

删除A.Tel1..Tel4的索引
Exec TestSpeed1 100 用时33秒,注:数据表Guest中的Tel1..Tel4的值都比10000000大
Exec TestSpeed1 200 用时62秒

然后Tel1..Tel4建立索引
CREATE INDEX IX_Guest_5 ON Guest (Tel1);
CREATE INDEX IX_Guest_6 ON Guest (Tel2);
CREATE INDEX IX_Guest_7 ON Guest (Tel3);
CREATE INDEX IX_Guest_8 ON Guest (Tel4);
再来
Exec TestSpeed1 1000000 用时55秒

环境 2003 SQL 2000 企业版

我赞同改成
select top 1 * from (
select * from A where Tel1 = 8000000
union all
select * from A where Tel2 = 8000000
union all
select * from A where Tel3 = 8000000
union all
select * from A where Tel4 = 8000000
) A
也已经这么作了,仅仅当作一个测试.

[victorcai2006]

另外有个建议：不要所有字段都加上索引，这样会影响你写入或修改的速度的，把经常要查询的字段加上索引就行了
个人意见，仅供参考

[hery2002]

①
Select * from A where ID in (Select AID from B) order by ID

--加个Group By试试,
SELECT * FROM [A] WHERE [ID] IN (SELECT [AID] FROM [B] GROUP BY [AID]) ORDER BY [ID]

②
Select Top 1 * from A where Tel1=8000000 or Tel2=8000000 or Tel3=8000000 Tel4=8000000
（Tel1..Tel4）整个数据表中已经排除重复
-- 这个优化.....
-- 可以尝试一下前面提到的UNION ALL方式,

SELECT TOP (1) * FROM ( 
  SELECT * FROM [A] WHERE [TEL1] = 8000000 
  UNION ALL 
  SELECT * FROM [A] WHERE [TEL2] = 8000000 
  UNION ALL 
  SELECT * FROM [A] WHERE [TEL3] = 8000000 
  UNION ALL 
  SELECT * FROM [A] WHERE [TEL4] = 8000000 
) A

③
Select A.*,B.* from where A.ID=B.AID and B.nDate>#2008-1-1# and B.nDate <#2008-8-1# and B.PayType=1 order by A.ID desc
-- 你这个做的是INNER JOIN 还是LEFT JOIN?
-- nDate上面建立索引就可以了,PayType建立索引没有多大意义,因为你的PayType不肯能有很多,

SELECT A.*,B.* FROM [A] 
             INNER JOIN [B] ON A.[ID]=B.[AID] 
                           AND B.[NDATE]>'2008-1-1' 
                           AND B.[NDATE] <'2008-8-1' 
                           AND B.[PAYTYPE]=1 
ORDER BY A.[ID] DESC 

[hyqwan11112]

学习！