SELECT ... FROM (select * from t0 WHERE t0.f1 IN (1, 2, 3, 4)) t0 INNER JOIN t1 ON (t0.f1 = t1.f1) INNER JOIN t2 ON (t1.f2 = t2.f2) INNER JOIN t3 ON (t2.f3 = t3.f3)试试这个
把语句改成这样试试呢,这两个语句是等价的:SELECT ... FROM t0 INNER JOIN t1 ON (t0.f1 = t1.f1) and t0.f1 IN (1, 2, 3, 4) INNER JOIN t2 ON (t1.f2 = t2.f2) INNER JOIN t3 ON (t2.f3 = t3.f3)
统计更新的时机, 1.数据库AUTO_CREATE_STATISTICS选项=ON时,自动更新.2.手动更新1: update statistics [表名] with FULLSCAN --全表扫描. update statistics [表名] with SAMPLE [百分比] PERCENT --按百分比抽样更新统计. update statistics [表名] with SAMPLE [行数] ROWS --按指定行数抽样更新统计. 参考 http://technet.microsoft.com/zh-cn/library/ms187348.aspx3.手动更新2: sp_updatestats 全部更新,默认选择抽样更新.判断是否需手工更新统计的时机, 1.数据仓库中,大量导入数据后,平时无DML变更,需更新. 2.执行计划中,Rows与EstimateRows差距较大的情况. 3.以下SQL语法中,rowmodctr(未更新统计的记录数)较大的情况. select object_name(a.object_id) 'table_name',a.name 'stats_name', c.name 'col_name',e.rowcnt,e.rowmodctr, case when auto_created=1 then 'auto_created' when user_created=1 then 'user_created' when e.rowcnt>0 then 'index_col' end stype from sys.stats a inner join sys.stats_columns b on a.object_id=b.object_id and a.stats_id=b.stats_id and b.stats_column_id=1 inner join sys.columns c on b.object_id=c.object_id and b.column_id=c.column_id inner join sysobjects d on a.object_id=d.id and d.xtype='U' inner join sysindexes e on a.object_id=e.id and a.name=e.name and e.name is not null order by a.object_id
針對這種情況,可以直接指定某一個索引來解決:with(index( indexName ))E.g.select .... from tableName1 with(index(IndexName)) join tableName_2 with(index(IndexName)) on ....
FROM (select * from t0 WHERE t0.f1 IN (1, 2, 3, 4))
t0
INNER JOIN
t1 ON (t0.f1 = t1.f1)
INNER JOIN
t2 ON (t1.f2 = t2.f2)
INNER JOIN
t3 ON (t2.f3 = t3.f3)试试这个
FROM
t0
INNER JOIN
t1 ON (t0.f1 = t1.f1) and t0.f1 IN (1, 2, 3, 4)
INNER JOIN
t2 ON (t1.f2 = t2.f2)
INNER JOIN
t3 ON (t2.f3 = t3.f3)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[t3].[id]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1008]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[t1].[id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| | | | |--Index Seek(OBJECT:([DBTest].[dbo].[t1].[index_t1id]), SEEK:([DBTest].[dbo].[t1].[id]=(1) OR [DBTest].[dbo].[t1].[id]=(100) OR [DBTest].[dbo].[t1].[id]=(900)) ORDERED FORWARD)
| | | | |--RID Lookup(OBJECT:([DBTest].[dbo].[t1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([DBTest].[dbo].[t3].[index_t3id]), SEEK:([DBTest].[dbo].[t3].[id]=[DBTest].[dbo].[t1].[id]), WHERE:([DBTest].[dbo].[t3].[id]>=(1) AND [DBTest].[dbo].[t3].[id]<=(900)) ORDERED FORWARD)
| | |--RID Lookup(OBJECT:([DBTest].[dbo].[t3]), SEEK:([Bmk1008]=[Bmk1008]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([DBTest].[dbo].[t2].[index_t2id]), SEEK:([DBTest].[dbo].[t2].[id]=[DBTest].[dbo].[t3].[id]), WHERE:([DBTest].[dbo].[t2].[id]>=(1) AND [DBTest].[dbo].[t2].[id]<=(900)) ORDERED FORWARD)
|--RID Lookup(OBJECT:([DBTest].[dbo].[t2]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)
统计更新的时机,
1.数据库AUTO_CREATE_STATISTICS选项=ON时,自动更新.2.手动更新1:
update statistics [表名] with FULLSCAN --全表扫描.
update statistics [表名] with SAMPLE [百分比] PERCENT --按百分比抽样更新统计.
update statistics [表名] with SAMPLE [行数] ROWS --按指定行数抽样更新统计.
参考 http://technet.microsoft.com/zh-cn/library/ms187348.aspx3.手动更新2: sp_updatestats 全部更新,默认选择抽样更新.判断是否需手工更新统计的时机,
1.数据仓库中,大量导入数据后,平时无DML变更,需更新.
2.执行计划中,Rows与EstimateRows差距较大的情况.
3.以下SQL语法中,rowmodctr(未更新统计的记录数)较大的情况.
select object_name(a.object_id) 'table_name',a.name 'stats_name', c.name 'col_name',e.rowcnt,e.rowmodctr,
case when auto_created=1 then 'auto_created' when user_created=1 then 'user_created' when e.rowcnt>0 then 'index_col' end stype
from sys.stats a
inner join sys.stats_columns b on a.object_id=b.object_id and a.stats_id=b.stats_id and b.stats_column_id=1
inner join sys.columns c on b.object_id=c.object_id and b.column_id=c.column_id
inner join sysobjects d on a.object_id=d.id and d.xtype='U'
inner join sysindexes e on a.object_id=e.id and a.name=e.name and e.name is not null
order by a.object_id
查询优化器希望尽可能选择高效的执行计划,而执行计划缓存却希望尽可能的重用缓存,这两种机制在某些情况会产生冲突。