为什么我建立的索引没有用上？？？急！！！！

[cuizu]

原有SQL：
SELECT NSRSBH,SUM(YSXSSR) AS XSJE_HJ
INTO #U1_0401_TMP
FROM SB_SBXX
WHERE SSSQ_Q>=@KJND_Q AND SSSQ_Q<=@KJND_Z
GROUP BY NSRSBH
io:
Parse and Compile Time 1.
SQL Server cpu time: 100 ms.
Table: SB_SBXX scan count 1, logical reads: (regular=110225 apf=0 total=110225), physical reads: (regular=12551 apf=1152 total=13703), apf IOs used=1152

建立索引：
CREATE INDEX IDX_SB_SBXX_0401
ON dbo.SB_SBXX(SSSQ_Q, YSXSSR)
go

可是没有用上，为什么啊！！！！请执教

[zyhlhx]

那就加强制索引看看效果如何
另外，你的 GROUP BY NSRSBH 这个字段最好也包含在索引中
SELECT NSRSBH,SUM(YSXSSR) AS XSJE_HJ
INTO #U1_0401_TMP
FROM SB_SBXX （index index_name）
WHERE SSSQ_Q>=@KJND_Q AND SSSQ_Q<=@KJND_Z
GROUP BY NSRSBH

[hanps]

给NSRSBH 单建立一个索引

[cuizu]

select 语句：
SELECT A.ND,
A.YF,
ISNULL(A.NSR_SWJG_DM,D.NSR_SWJG_DM),
B.SWJG_MC,
ISNULL(D.ZG_SWGY_DM,'N'),
ISNULL(E.CZRY_MC,'N'),
A.NSRSBH,
D.NSRMC,
A.ZSXM_DM,
C.ZSXM_MC,
A.NSQX_DM,
F.NSQX_MC,
A.SBRQ,
A.SBQX,
A.SSSQ_Q,
A.SSSQ_Z
FROM SB_YZCWSBQC A (index IDX_SB_YZCWSBQC_0801) ,DM_SWJG B,DM_ZSXM C,DJ_NSRXX D (index IDX_DJ_NSRXX_0801), DM_CZRY E,DM_NSQX F
WHERE A.ND = 2003 AND A.YF=1 AND A.NSRSBH=D.NSRSBH AND A.ZSXM_DM = C.ZSXM_DM
AND A.NSR_SWJG_DM = B.SWJG_DM AND D.ZG_SWGY_DM*=E.CZRY_DM
AND A.NSQX_DM = F.NSQX_DM

----------------------------------------------------

索引：

CREATE INDEX IDX_SB_YZCWSBQC_0801
ON dbo.SB_YZCWSBQC(ND, YF, NSRSBH, ZSXM_DM, NSR_SWJG_DM, NSQX_DM)
go

CREATE INDEX IDX_DJ_NSRXX_0801
ON dbo.DJ_NSRXX(NSRSBH, ZG_SWGY_DM, NSR_SWJG_DM)
go
----------------------------------------------------
我强制用了以上两个索引，出现以下情况：

Index 'IDX_SB_YZCWSBQC_0801' specified as optimizer hint in the FROM clause of table 'SB_YZCWSBQC' does not exist. Optimizer will choose another index instead.
... ...
... ...

Parse and Compile Time 2.
SQL Server cpu time: 200 ms.
Table: SB_YZCWSBQC scan count 1, logical reads: (regular=196237 apf=0 total=196237), physical reads: (regular=8 apf=26318 total=26326), apf IOs used=26318
Table: DM_SWJG scan count 85089, logical reads: (regular=170178 apf=0 total=170178), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: DM_ZSXM scan count 85089, logical reads: (regular=85089 apf=0 total=85089), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: DJ_NSRXX scan count 85089, logical reads: (regular=429725 apf=0 total=429725), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: DM_CZRY scan count 84097, logical reads: (regular=255991 apf=0 total=255991), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: DM_NSQX scan count 85088, logical reads: (regular=85088 apf=0 total=85088), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

: DJ_NSRXX 表的io消除了，可是SB_YZCWSBQC仍然很大。
清高手指点

[zyhlhx]

第一 你的索引的字段好象太多了 6个？！只把 where group 需要的加入
另外，错误信息说你索引没有找到？

[zyhlhx]

如果 where 条件中哪个的差异小，比如 10000行只有10个不同的，就不要建在索引中
sp_help SB_YZCWSBQC
看看有没有 索引 IDX_SB_YZCWSBQC_0801

[zyhlhx]

表有多少数据量？ 你选择的数据有多少？比例

[cuizu]

SB_SBXX 100万
需要10万左右

[sunshinethinking]

索引名称太长，SYBASE自动截断，所以你没有用上索引，把名称截短点就可以了
还记得找到截断后的索引，要删除掉

[zyhlhx]

楼上的不对把 ，索引名称可以有30 个字符，这个名称是可以的

[cuizu]

sunshinethinking:
我的索引名称是正确的，因为没有建上，但是建上之后发现io更大了。
wuwuwuwu......需要重新分析

----------------------------------
Table: SB_YZCWSBQC scan count 1, logical reads: (regular=1647886 apf=32 total=1647918), physical reads: (regular=32452 apf=1605725 total=1638177), apf IOs used=1574214
Table: DM_SWJG scan count 85089, logical reads: (regular=170178 apf=0 total=170178), physical reads: (regular=7 apf=0 total=7), apf IOs used=0
Table: DM_ZSXM scan count 85089, logical reads: (regular=85089 apf=0 total=85089), physical reads: (regular=1 apf=0 total=1), apf IOs used=0
Table: DJ_NSRXX scan count 85089, logical reads: (regular=429725 apf=0 total=429725), physical reads: (regular=21455 apf=2443 total=23898), apf IOs used=2373
Table: DM_CZRY scan count 84097, logical reads: (regular=255991 apf=34 total=256025), physical reads: (regular=47 apf=13 total=60), apf IOs used=3
Table: DM_NSQX scan count 85088, logical reads: (regular=85088 apf=0 total=85088), physical reads: (regular=1 apf=0 total=1), apf IOs used=0

[zyhlhx]

你索引本身建的不好，最多两三个字段。差异太小的不要加进去！！

[8524]

对单个字段查询，可能并不会使用该字段加上其他字段的组合索引
可以为这个字段单独建一个索引