求解：参数化Sql查询速度变慢的原因是什么？

[tengteng_rock]

一个分页sql(含Like)，使用查询分析器查非常快，不大于1毫秒。但用程序（相关代码在后面）访问则需要8秒左右。
之前也查资料，参数化SQL时，@参数的数据类型要指定为实际数据库数据类型，的确有很大改观，但有like的sql效率很是很慢。
TEXT字段由nvarchar(MAX)改为nvarchar(4000)，似乎快了一点点，但比起查询分析内执行，还是慢了很多。

大家有没有经验可以分享呢


表T相关字段：
-----------------------
ID varchar(36)
TITLE nvarchar(500)
TEXT nvarchar(MAX)


c#查询数据库代码，用了微软的SqlHelper类
-----------------------------------------------------
String strSql = "SELECT TOP (@pageSize) ID, TITLE, TEXT FROM T WHERE (TITLE LIKE @title OR TEXT LIKE @text ) AND ID NOT IN (SELECT TOP ((@page - 1) * @pageSize) ID FROM T WHERE TITLE LIKE @title OR TEXT LIKE @text )";

SqlParameter spPageSize = new SqlParameter("@pageSize", SqlDbType.Int);
spPageSize.Value = 10;
SqlParameter spTitle = new SqlParameter("@title", SqlDbType.NVarChar);
spTitle.Value = %我的测试%;
SqlParameter spText = new SqlParameter("@text", SqlDbType.NVarChar);
spText.Value = %我的测试%;
SqlParameter spPage = new SqlParameter("@page", SqlDbType.Int);
spPage.Value = 85;

connectionString = "......"; //略
using(DbDataReader rd = SqlHelper.ExecuteReader(connectionString, CommandType.Text, strSql, spPageSize, spTitle, spText, spPage)){
//略
}

[wjhgzx]

like '%条件%' 这类的过滤，是用不了索引的，所以会慢，需要全表扫描的

[zincy]

like '%d%' 比like 'd%' 慢很多

[kkbac]

就算不是sqlserver, 而只是access, 2000条数据, 就算全表再扫描2000次,速度也会不超过1秒. 何况sqlserver.

这么少的数据量下面, 很少有人能写一个查询需要8秒的.

时间应该消耗在其他地方了.

[tengteng_rock]

好吧，现在就讲讲这句有什么问题吧，我真的看不出来了

using(DbDataReader rd = SqlHelper.ExecuteReader(connectionString, CommandType.Text, strSql, spPageSize, spTitle, spText, spPage))

[烟波钓]

[Quote=引用 28 楼 tengteng_rock 的回复:]
好吧，现在就讲讲这句有什么问题吧，我真的看不出来了

using(DbDataReader rd = SqlHelper.ExecuteReader(connectionString, CommandType.Text, strSql, spPageSize, spTitle, spText, spPage))
[/Quote]
你的SqlHelper的方法能跟进去么
跟进去看看 恍恍惚惚的感觉我也遇到过这个问题 解决方案忘记了……

[tengteng_rock]

全表扫描是无法改变了，但这么小的数据量全表扫也应该是毫秒级就完成，在查询分析器里查，的确是1毫秒，通过代码查就8秒，我觉得应该不是sql本身的问题，问题应该出在c#代码这边

[阿非]

说的不够清楚么?

[tengteng_rock]

亲们，问题解决了！解决的方法是为ID字段加中括号，速度马上由8秒变为300+毫秒。
ID是sqlserver的关键字吗?
而且加不加中括号，对与查询分析器是免疫的，查询分析器一直都执行的很快。仅对.net代码里产生影响。

即：
将 SELECT TOP (@pageSize) ID, TITLE, TEXT FROM T WHERE (TITLE LIKE @title OR TEXT LIKE @text ) AND ID NOT IN (SELECT TOP ((@page - 1) * @pageSize) ID FROM T WHERE TITLE LIKE @title OR TEXT LIKE @text )
改为
SELECT TOP (@pageSize) [ID], TITLE, TEXT FROM T WHERE (TITLE LIKE @title OR TEXT LIKE @text ) AND ID NOT IN (SELECT TOP ((@page - 1) * @pageSize) ID FROM T WHERE TITLE LIKE @title OR TEXT LIKE @text )

找的好痛苦啊，不过还是值得讨论的，这里不加中括号，会产生什么歧义吗？
我的环境是.net2,sqlserver2005(9.00.1399.00)

[tengteng_rock]

更正：

.......

改为
SELECT TOP (@pageSize) [ID], TITLE, TEXT FROM T WHERE (TITLE LIKE @title OR TEXT LIKE @text ) AND ID NOT IN (SELECT TOP ((@page - 1) * @pageSize) [ID] FROM T WHERE TITLE LIKE @title OR TEXT LIKE @text )

[tengteng_rock]

SqlHelper是Microsoft Data Access Application Block那个dll里的，跟不进去。
看了下源码，大概是这样的:
先new了一个SqlConnection，然后new了一个SqlCommand。
然后
using(SqlDataAdapter da = new SqlDataAdapter(cmd)) {
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if(mustCloseConnection)
connection.Close();
return ds;
}
如果慢，就只能慢在SqlDataAdapter的Fill方法上了;
我所有的查询最后都要走到这里，其他的都很快，唯这句sql慢了，这句sql比其他sql只多了这么一个条件：
TITLE LIKE @title OR TEXT LIKE @text

[birdwawe]

为什么所有的连接都要用SqlDataAdapter呢？你查询多条数据用数据适配器可以，但是只是测试连接数据库的话，就不需要用SqlDataAdapter了。你这样效率肯定低

[tengteng_rock]

实际上就是为了取多条记录。。。

[xiaogui340]

like、in、not in、select 嵌套 就是影响 查询速度的，不过LZ还在在查询代码前后加个时间记录，看看是真的查询数据库 时间太久，还是 因为程序有其他原因造成的。

另外数据库 你要查询的条件字段上加上 索引，能提高你的查询速度

[IE11下面经常卡到爆]

用like参数化时，调用一下
SqlCommand.Prepare()
也可以提高效率

[山东蓝鸟贵薪]

那有好的方法，不影响速度呢？？？

[tengteng_rock]

。。。
我单独写了段代码，没有用SqlHelp类，分别用SqlCommand.ExecuteReader和SqlDataAdapter.Fill执行了一下。
分别用了386和390毫秒，都很快。

难道是SqlHelp在执行查询前后还做过什么其他的事情。

[山东蓝鸟贵薪]

记录小是看不出来，当记录上百万条，就会反应慢的

[catmax]

用SQL切割，不用datareader,用dataset，或者用datatable，你去试试，肯定效果不一样

[wsjrzjp]

把要搜索的字段添家索引

[王芜]

句子慢在了 后半句 ，其实是你的分页机制有问题，like 本身 没有优化的余地 。 select * from (
select row_number() over(order by pmember_regdate desc) as RowNumber
,pmember_email as cmember_login
,'['+pmember_login+']'+isnull(zbx.addr,'<未填>') as cmember_contact
,zbx.tel as cmember_mobile
,pmember_province as cmember_province
,pmember_id as cmember_id
,zbx.addx as cmember_comname
,zbx.*
from dbo.zt_Personal_pmember as pm left join
dbo.SMT_zhaobiaox as zbx on pm.pmember_email=zbx.proson_name
where pm.pmember_login like '%'+@comname+'%'
and (@uchecked=-1
or (@uchecked=11 and zbx.shenhe='审核通过')
or (@uchecked=12 and zbx.shenhe='未通过')
)
and (pmember_capital =@provice or @provice='全部')
and len(zbx.tel)=11
) as datatable
where RowNumber between((@pageIndex-1)*@pageCount+1) and ((@pageIndex-1)*@pageCount+@pageCount)


这个你能看懂的 ，再说分页最好是做成存储过程