[讨论]----全文索引的使用-----

[sujinlin]

希望有经验的高人可以指点下。


SELECT TOP 5 bmh, xm, sfzh, xb, lxdh
FROM t_bmk
WHERE CHARINDEX('张',xm)>=1 AND (bmh <
(SELECT MIN(bmh) AS bmh
FROM (SELECT TOP 30 bmh
FROM t_bmk
WHERE CONTAINS(xm, '"张"')
ORDER BY bmh DESC) DERIVEDTBL))
ORDER BY bmh DESC

以上代码耗时 203MS
---------------------------------------------------
SELECT TOP 5 bmh, xm, sfzh, xb, lxdh
FROM t_bmk
WHERE CONTAINS(xm, '"张"') AND (bmh <
(SELECT MIN(bmh) AS bmh
FROM (SELECT TOP 30 bmh
FROM t_bmk
WHERE CONTAINS(xm, '"张"')
ORDER BY bmh DESC) DERIVEDTBL))
ORDER BY bmh DESC

只是在WHERE后面跟换了下条件耗时 3600MS
----------------------------------------------------
SELECT MIN(bmh) AS bmh
FROM (SELECT TOP 30 bmh
FROM t_bmk
WHERE CONTAINS(xm, '"张"')
ORDER BY bmh DESC) DERIVEDTBL
耗时 186MS

输出信息为：0203151200

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

SELECT TOP 5 bmh, xm, sfzh, xb, lxdh
FROM t_bmk
WHERE CONTAINS(xm, '张') AND (bmh <'0203151200')ORDER BY bmh DESC

耗时 186MS

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

问题如下，为什么拆分开进行统计耗时时间相差那么大？

使用CONTAINS(xm, '"张"')的情况下，还要使用CHARINDEX('张',xm)>=1 才可以快了许多？

===================================================

PS:xm上建立了全文索引

[sujinlin]

帮忙指教下好么

[sujinlin]

继续等待

[jspadmin]

建立索引肯定会速度快很多，至于你说拆分开，我不太明白你的意思，你是说条件拆分开后，速度快了很多吗？

[sujinlin]

恩。

不过我明白为什么了，主要原因出错在，我进行全文索引文件与表关联