大家帮忙看看能不能优化这个IN语句

[winnet]

求优化：
SELECT * FROM pds_buy WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
AND p_buyid in (select distinct p_buyid from pds_buy_product where p_id like '%33300%')
以上这句执行结果为37秒

把上面二句单独
SELECT * FROM pds_buy WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01' （时间0.125S）

select distinct p_buyid from pds_buy_product where p_id like '%33300% （时间0.062S）

组合为IN后，结果36 37秒，有没什么优化方法，注意，第二表pds_buy_product 不能放进前面的FROM中，
因为这个是程序组合条件进去的，如果没有这个条件，就不用的，所以在FROM中加上第二个表是不行的

[hgwyl]

--如果p_dt是个聚集索引的话，试试先利用一下
SELECT * from
(
SELECT * FROM pds_buy WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
)
where xxoo

[---涛声依旧---]

SELECT * FROM pds_buy t1 
WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
 AND  EXISTS(select 1 from pds_buy_product where t1.p_buyid=p_buyid AND p_id like '%33300%')

[wwx851285]

楼上正解~~~

[Andy__Huang]

SELECT * 
FROM pds_buy a
inner join (select distinct p_buyid from pds_buy_product where p_id like '%33300%') b
on a.p_buyid=b.p_buyid
WHERE a.p_dt BETWEEN '1999-01-01' AND '2013-09-01'

[winnet]

引用 2 楼 rockyljt 的回复:

SELECT * FROM pds_buy t1 
WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
 AND  EXISTS(select 1 from pds_buy_product where t1.p_buyid=p_buyid AND p_id like '%33300%')

我这个方法也写了测试过，同样要这么时间，反而多二秒左右

[winnet]

引用 4 楼 hdhai9451 的回复:

SELECT * 
FROM pds_buy a
inner join (select distinct p_buyid from pds_buy_product where p_id like '%33300%') b
on a.p_buyid=b.p_buyid
WHERE a.p_dt BETWEEN '1999-01-01' AND '2013-09-01'

这个时间是很快0.046秒，但是，我不能这样用，因为我FROM中是做好了，后面的我只是加了条件，不过我想想有没办法去改变我原来的

[Shawn]

--#1.先缓存结果集2到表变量(有主键)
DECLARE @temp TABLE(p_buyid INT NOT NULL PRIMARY KEY)
INSERT @temp(p_buyid)
select distinct p_buyid from pds_buy_product where p_id like '%33300%'

--#2.用下面2种方法均可:
SELECT a.* 
FROM pds_buy a
	INNER JOIN @temp b
		ON a.p_buyid = b.p_buyid
WHERE a.p_dt BETWEEN '1999-01-01' AND '2013-09-01'

SELECT * 
FROM pds_buy a
WHERE a.p_dt BETWEEN '1999-01-01' AND '2013-09-01'
AND EXISTS
(
	SELECT 1
	FROM @temp B
	WHERE b.p_buyid = a.p_buyid
)

[---涛声依旧---]

那p_dt 、p_buyid建议索引肯定快了　

[---涛声依旧---]

SELECT * FROM pds_buy WHERE p_dt<='2013-09-01' --改成这样就行了
 AND p_buyid in (select distinct p_buyid from pds_buy_product where p_id like '%33300%')

[winnet]

引用 9 楼 rockyljt 的回复:

SELECT * FROM pds_buy WHERE p_dt<='2013-09-01' --改成这样就行了
 AND p_buyid in (select distinct p_buyid from pds_buy_product where p_id like '%33300%')

小数据库，没有加索引的，这个主要是IN的速度问题，我还是改原来的吧，多谢大家