优化查询的几点建议之二

时常，查询在客户端很慢，如果在服务器执行很快。为什么？

这个问题有点复杂，这里我们不考虑网络，死锁等问题，仅就查询而言。

一般来说，查询分为两种，存储过程和动态查询，下面分别讨论：

一，存储过程

SQL SERVER 建立查询计划：

存储过程；

自定义函数；

表值函数；

触发器。

不建立计划：

VIEW;

INLINE-TABLE FUNCTION.

如何建立计划呢？下面几点要注意：

常量，服务器一定要使用；

参数，服务器编译可以嗅到；

局部变量，服务器使用标准假设，取决运算符，推理惟一索引。

众所周知，存储过程第一次运行后，服务器会把查询计划放入缓存，下次运行，省略编译。下列事件发生，会清除计划：

ALTER PROCEDURE;

SP_RECOMPILE;

DBCC FREEPROCCACHE;

BUFFER CACHE FULL;

SP_CONFIGURE;

SERVER RESTART.

下列事件，会导致部分编译：

表定义变化；

表索引变化；

表统计变化；

SP_RECOMPILE 表变化。

下面来看不同计划对应的设置，非常重要！

	Applications using ADO .Net, ODBC or OLE DB	SSMS, Query Analyzer	SQLCMD, OSQL, BCP, SQL Server Agent	ISQL, DB-Library
ANSI_NULL_DFLT_ON	ON	ON	ON	OFF
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS	ON	ON	ON	OFF
CONACT_NULLS_YIELD_NULL	ON	ON	ON	OFF
QUOTED_IDENTIFIER	ON	ON	OFF	OFF
ARITHABORT	OFF	ON	OFF	OFF

从上图，清楚看出，最重要的是：ARITHABORT，因为客户和服务器设置不同。

这个可以解释，为什么客户很慢，服务器快的原因。

可以 SET ARITHABORT OFF 进行测试。

二，动态查询

分为两种

非参数的：

SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value) EXEC(@sql)

C#

cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();

参数的：

EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value', N'@value int', @value = @value

C#

cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value"; cmd.Parameters.Add("@value", SqlDbType.Int); cmd.Parameters["@value"].Value = value;

它编译计划和计划缓存，基本与存储过程一样。有两点不同：

1，服务器寻找存储过程使用名字，动态查询使用查询文本的HASH KEY；

2，默认的SCHAMA 会影响动态查询计划，存储过程不会。

三，参数嗅探问题

有时，参数嗅探会产生：

查询使用不适合参数嗅探；

小部分查询不同大量主要查询；

索引结构不合理；

几点解决方法，供参考：

1. Use an index hint to force use of any of the other indexes.
2. The query hintOPTIMIZE FOR.
3. Copy @p to a local variable.
4. OPTION (RECOMPILE) / WITH RECOMPILE
5. Add the "optimal" index on related columns
6. Make the index on Column filtered or drop it entirely.