有三个表,两个表内连接,还有一个表ETLState存放的是某个业务上次运行后的时间SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > (SELECT uploaddate FROM ETLState WHERE tablename = 'house')
这样查询的话要20秒左右SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate >'2010-11-10 18:28:10.317'
如果直接把时间赋予它就一下子完成了.似乎 (SELECT uploaddate FROM ETLState WHERE tablename = 'house')是在house表和building表关联查询完后才运行,所以非常慢,请问要怎么解决.
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > (SELECT uploaddate FROM ETLState WHERE tablename = 'house')
这样查询的话要20秒左右SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate >'2010-11-10 18:28:10.317'
如果直接把时间赋予它就一下子完成了.似乎 (SELECT uploaddate FROM ETLState WHERE tablename = 'house')是在house表和building表关联查询完后才运行,所以非常慢,请问要怎么解决.
SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > (SELECT max(uploaddate) FROM ETLState WHERE tablename = 'house')
declare @d varchar(20)
select @d = uploaddate FROM ETLState WHERE tablename = 'house'SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > @d
ETLState 的 tablename 有非聚集索引
cjzm83的方法试了一下,还是一样的很慢,杯具了,感觉很普通的一个查询,不知道怎么搞的,会这么慢
看要多长时间,估计单独查时间就很长
与子查询无关
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > (SELECT uploaddate FROM ETLState WHERE tablename = 'house')
改成
DECLARE @DATE AS DATETIME
SELECT @DATE=uploaddate FROM ETLState WHERE tablename = 'house'
SELECT h.* FROM house h
INNER JOIN Building b ON h.BuildingKey = b.BuildingKey
where h.UpdateDate > @DATE
看来是没有什么办法了.
where
h.BuildingKey = b.BuildingKey
and
h.UpdateDate > e.uploaddate
and
e.tablename = 'house')这样你试一实