exec sp_executesql带参数比不带参数慢几十倍,怎么优化?
exec sp_executesql N' SELECT BusinessDate,
BusinessHourName,
DiningTableName,
DepartmentName,
FoodName,
FoodNo,
Specification,
Price,
Quantity,
Dues4Food,
OrderDatetime,
OrdererName,
FoodTypeName ,
DepartmentNo,
FoodTypeNo
FROM Meal.V_AllOrderFoods
where
FoodTypeName LIKE @FoodTypeName and
OrderDatetime between @begintime and @endtime and
BusinessHourNo like @businessHourNo and
ordererNo like @ordererno
and DiningTableNo like @tableno
and FoodNo like @foodno
and DiningAreaName like @DiningAreaName
and IsCheckedOut = ''1''
Order by DepartmentNo,FoodTypeNo,FoodNo,OrderDatetime',N'@FoodTypeName varchar(20),@begintime datetime,@endtime datetime,@businessHourNo varchar(2),@ordererno varchar(5),@tableno varchar(5),@foodno varchar(8),@DiningAreaName nvarchar(20)',@FoodTypeName='%',@begintime='2012-01-01 00:00:00:000',@endtime='2012-02-15 23:59:00:000',@businessHourNo='%',@ordererno='%',@tableno='%',@foodno='%',@DiningAreaName=N'%'
比写成下面这样执行慢几十倍
exec sp_executesql N' SELECT BusinessDate,
BusinessHourName,
DiningTableName,
DepartmentName,
FoodName,
FoodNo,
Specification,
Price,
Quantity,
Dues4Food,
OrderDatetime,
OrdererName,
FoodTypeName ,
DepartmentNo,
FoodTypeNo
FROM Meal.V_AllOrderFoods
where
FoodTypeName LIKE ''%'' and
OrderDatetime between ''2012-01-01 00:00:00:000'' and ''2012-02-15 23:59:00:000'' and
BusinessHourNo like ''%'' and
ordererNo like ''%''
and DiningTableNo like ''%''
and FoodNo like ''%''
and DiningAreaName like ''%''
and IsCheckedOut = ''1''
Order by DepartmentNo,FoodTypeNo,FoodNo,OrderDatetime'
有什么设置可以提高传入参数的执行速度