求优化:
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中加上第二个表是不行的优化IN
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中加上第二个表是不行的优化IN
--如果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%')
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中是做好了,后面的我只是加了条件,不过我想想有没办法去改变我原来的
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
)
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%')