select count(*)
from User a ,Offering b
where b.c1>=a.c1
And b.c2=a.c2
And b.c3=a.c3
And b.c4>=a.c4*0.8
And b.c5<=a.c5*1.20
And b.c6<=a.c6*1.20 用户表有2千万数据,商品表有几千条数据,查询很慢
除了建索引,还有其他优化办法吗
from User a ,Offering b
where b.c1>=a.c1
And b.c2=a.c2
And b.c3=a.c3
And b.c4>=a.c4*0.8
And b.c5<=a.c5*1.20
And b.c6<=a.c6*1.20 用户表有2千万数据,商品表有几千条数据,查询很慢
除了建索引,还有其他优化办法吗
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1011],0)))
|--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[a].[Brand], [Expr1007], [Expr1008]))
|--Compute Scalar(DEFINE:([Expr1007]=[mydb].[dbo].[a].[Latest3MonthAvgARPU]*(1.20), [Expr1008]=[mydb].[dbo].[a].[Latest3MonthAvgARPU]*(0.80)))
| |--Clustered Index Scan(OBJECT:([mydb].[dbo].[a].[a_PK]))
|--Filter(WHERE:([mydb].[dbo].[b].[Brand]=(999) OR [mydb].[dbo].[b].[Brand]=[mydb].[dbo].[a].[Brand]))
|--Index Spool(SEEK:([mydb].[dbo].[b].[MonthMinCostFee] >= [Expr1008] AND [mydb].[dbo].[b].[MonthMinCostFee] <= [Expr1007]))
|--Clustered Index Scan(OBJECT:([mydb].[dbo].[b].[b_PK]), WHERE:([mydb].[dbo].[b].[RecommendType]=(2)))
这个怎么理解,SQL怎么写
where a=b/1.0 可以用
from User a ,Offering b
where b.c1>=a.c1
And b.c2=a.c2
And b.c3=a.c3
And b.c4>=a.c4*0.8
And b.c5<=a.c5*1.20
And b.c6<=a.c6*1.20 那我上面这个已经用了索引?
1.2个表的c1,c2,c3字段上建复合索引.
2.索引及统计信息需更新.
这么查询后,count(*)是多少?要是多的话,建索引也快不了的。要是小,到是可以通过建索引来加速
说别用 a.1=b.1 and a.1=c.1之类的多表慢的很。
多用left 速度提高多
select count(*)
from Offering b left join User a on b.c2=a.c2
where b.c1>=a.c1
And b.c3=a.c3
And b.c4>=a.c4*0.8
And b.c5<=a.c5*1.20
And b.c6<=a.c6*1.20