【求助】查询速度贼满鸟。。。求高人相助！在线等！
数据库里面， BizData有12万条数据，Data2Type有20万条数据，其他表很少，几十条。
这个是我的分页查询，现在查询的速度还要一秒多.
这个是第一个郁闷的地方，更郁闷的是：将最外层的where子句去掉，将select * 改为select count(*)或者select count(bizdataId)。来得到未分页前的总行数，竟然要6秒。
目前索引情况：所有的表的外键都建立NonCluster索引。
求高手们帮帮我吧！！
select *
from
(
select
BizUnit.bizUnitId,
BizClass.bizClassId,
Location.locationId,
BizType.bizTypeId,
ROW_NUMBER() OVER(ORDER BY BizData.bizDataId) as rn
from
BizData, BizUnit, BizClass, Location, Data2Type, BizType
where
BizUnit.bizUnitId = BizData.bizUnitId
and
BizClass.bizClassId = BizData.bizClassId
and
Location.locationId = BizData.locationId
and
Data2Type.bizDataId = BizData.bizDataId
and
BizType.bizTypeId = Data2Type.bizTypeId
) as a
where
a.rn between #StartIndex# and #EndIndex#
[解决办法]
将表结合，把外层where条件放到里面试试
[解决办法]
帮你顶一下
[解决办法]
帮你弄漂亮点~~~高手些看起来方便
SELECT *
FROM
(
SELECT
B.BIZUNITID,
C.BIZCLASSID,
D.LOCATIONID,
F.BIZTYPEID,
ROW_NUMBER() OVER(ORDER BY A.BIZDATAID) AS RN
FROM
DBO.BIZDATA A WITH(NOLOCK),DBO.BIZUNIT B WITH (NOLOCK),
DBO.BIZCLASS C WITH(NOLOCK),DBO.LOCATION D WITH (NOLOCK),
DBO.DATA2TYPE E WITH (NOLOCK),DBO.BIZTYPE F WITH (NOLOCK)
WHERE
B.BIZUNITID = A.BIZUNITID
AND
C.BIZCLASSID = A.BIZCLASSID
AND
D.LOCATIONID = A.LOCATIONID
AND
E.BIZDATAID = A.BIZDATAID
AND
F.BIZTYPEID = E.BIZTYPEID
) AS A
WHERE
A.RN BETWEEN #STARTINDEX# AND #ENDINDEX#
