you notice that a particular set of parameter values the following qury sometimes executes quickly and
other times executes slowly. you also notice that 90 percent of the rows in the address table contain
the same value for the city.
select addressid,addressline1,city,postalcode from person.address
where city=@city_name and postalcode=@postal_code
you need to use a query hint that,for the particular set of parameter values,will result in a more
consistent query execution time. which query hint should you use?A. FAST
B. MAXDOP
C. OPTIMIZE FOR
D. PARAMETERIZATION FORCED我决定答案应该是c或者d,但是原理不是很清楚,希望高手不吝赐教,谢谢。。
other times executes slowly. you also notice that 90 percent of the rows in the address table contain
the same value for the city.
select addressid,addressline1,city,postalcode from person.address
where city=@city_name and postalcode=@postal_code
you need to use a query hint that,for the particular set of parameter values,will result in a more
consistent query execution time. which query hint should you use?A. FAST
B. MAXDOP
C. OPTIMIZE FOR
D. PARAMETERIZATION FORCED我决定答案应该是c或者d,但是原理不是很清楚,希望高手不吝赐教,谢谢。。
解决方案 »
- access和sql
- 一道面试的sql语句
- 如何将多条记录分隔为一个字符串?
- 让缘随风 17:28:13 请教一下,一个表DB8 ID WX 1 A 2 A 3 B 4 B 5
- 怎样对大是数据做统计,帮我一下,一定给分
- SQL SERVER可以加密存储过程和触发器吗,或者直接加密数据库?
- 请教:关于datediff(year,'1200-11-11',GETDATE())溢出的问题,请问各位咋办?
- sql2000数据库如何打包
- 请教BCP命令....
- 请问根据 业务员访问记录 倒查 他联系过的客户资料这种SQL应该怎么写?
- SQL语句怎么写
- SQL Server2005 的库怎么导入到2000中
当 PARAMETERIZATION 数据库选项设置为 FORCED 时,您可以指定对某一类查询仅尝试执行简单参数化而非强制参数化。通过在查询的强制参数化表单上创建 TEMPLATE 计划指南,并在 sp_create_plan_guide 中指定 PARAMETERIZATION SIMPLE 查询提示,可以执行此操作。
在编译和优化查询时提示查询优化器对本地变量使用特定值。仅在查询优化期间使用该值,在查询执行期间不使用该值。
看起来应该是C
下面的示例指示查询优化器对局部变量 @city_name 使用值 'Seattle',并在优化查询时使用统计数据来确定局部变量 @postal_code 的值。 复制代码
USE AdventureWorks;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO