为什么where中加多AND 条件时查询速度会慢了9倍速？(索引已加了)

[yuhuahuang]

有两个表va02及va03.
索引有va0201为主索引,va0301为主索引,另建D_ATE及app索引
以下加了and 就慢了9倍
SELECT dbo.va03.so, dbo.va03.kekan, dbo.va03.partno,
dbo.va03.app, dbo.va03.qty + ISNULL(dbo.va03.shipi, 0) AS qty
FROM dbo.va03 WITH (nolock) LEFT OUTER JOIN
dbo.VA02 ON dbo.va03.VA0301 = dbo.VA02.VA0201
WHERE (dbo.VA02.D_ATE > '2006/3/13') AND (CHARINDEX('制扣配件', dbo.va03.app)
> 0)

以下两种方式就快得多了，为什么呢？如何提高速度？谢谢!
SELECT dbo.va03.so, dbo.va03.kekan, dbo.va03.partno,
dbo.va03.app, dbo.va03.qty + ISNULL(dbo.va03.shipi, 0) AS qty
FROM dbo.va03 WITH (nolock) LEFT OUTER JOIN
dbo.VA02 ON dbo.va03.VA0301 = dbo.VA02.VA0201
WHERE (dbo.VA02.D_ATE > '2006/3/13')
---------------------------------------------------------------------
SELECT dbo.va03.so, dbo.va03.kekan, dbo.va03.partno,
dbo.va03.app, dbo.va03.qty + ISNULL(dbo.va03.shipi, 0) AS qty
FROM dbo.va03 WITH (nolock) LEFT OUTER JOIN
dbo.VA02 ON dbo.va03.VA0301 = dbo.VA02.VA0201
WHERE ((CHARINDEX('制扣配件', dbo.va03.app)
> 0)




注：索引的腳本
CREATE UNIQUE CLUSTERED INDEX [a] ON [dbo].[VA02]([VA0201]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [va0204date] ON [dbo].[VA02]([VA0204]) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [va03a] ON [dbo].[va03]([VA0301], [VA0302], [VA0303], [so], [keSo], [kekan], [sup], [partno]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [va03app] ON [dbo].[va03]([app]) ON [PRIMARY]

[-狙击手-]

SELECT dbo.va03.so, dbo.va03.kekan, dbo.va03.partno,
dbo.va03.app, dbo.va03.qty + ISNULL(dbo.va03.shipi, 0) AS qty
FROM dbo.va03 WITH (nolock) LEFT OUTER JOIN
dbo.VA02 ON dbo.va03.VA0301 = dbo.VA02.VA0201
WHERE (dbo.VA02.D_ATE > '2006/3/13') AND ( dbo.va03.app like '%制扣配件%')

---try

[-狙击手-]

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描

[-狙击手-]

当然也尽量避免使用非打头字母搜索，这也使得引擎无法利用索引

关键是这个AND ( dbo.va03.app like '%制扣配件%') 也会引起表级搜索

[yuhuahuang]

大哥：之前我是用AND ( dbo.va03.app like '%制扣配件%') 不是用((CHARINDEX('制扣配件', dbo.va03.app)> 0)
結果是一樣的。

[-狙击手-]

dbo.va03.app 这个列规律是什么样的，只有想办法回避模糊查询才有突破

[-狙击手-]

分开执行快的话，能不能这样把这两个查询结果当作表进行关联查询啊？