SQL SP在SQL中快速从C＃缓慢

Question

我有以下SQL SP：

ALTER PROCEDURE mysp
@HCPID varchar(10),
@From datetime,
@To Datetime,
@LoadType int
with recompile
AS
BEGIN
set arithabort on;
set nocount on;
begin
Select appt.*,
t1.StoreID,
t2.AppointmentDate,
t2.DoctorID,
t2.LoggedInUserID,
t2.Serial,
t2.TimeIn,
t2.TimeOut,
t3.FName,
t3.LName,
t3.AR_FName,
t3.AR_LName,
t3.MName,
t3.MotherName,
t3.AR_MName as Ar_MName,
t3.AR_MotherName as Ar_MotherName,
t4.ID as VisitID,
t5.Flag,
t5.FlagDate,
convert(date,'1900-1-1') as AdmissionDate,
convert(date,'1900-1-1')as DischargeDate
from cms.Appointments as appt
left join CMs.t1 on appt.ID = t1 .AppointmentID
left join CMs.t2 on appt.ID = t2.AppointmentID
left join CMs.t4 on t4.AppointmentID = appt.ID
left join CMs.t5 on t5.

AppointmentID = appt.ID
    inner join CMs.t3 on t3.ID = appt.PatientID

    where  appt.UserID = @HCPID
     AND  ( CONVERT(varchar(10), StartTime, 112) >=  CONVERT(varchar(10), @From, 112) OR (IsNull(@From, 0) = 0))
  AND  ( CONVERT(varchar(10), StartTime, 112)  <=  CONVERT(varchar(10), @To, 112) OR (IsNull(@To, 0) = 0))
    and ShowInSchedule = 1
end

如果我从SQL运行SP，则需要1-2秒，如果使用相同的参数值从C＃调用它，则大约需要10秒，有时我会收到以下消息之一。第一个错误第二个错误

我从c＃称之为下面：

string commande = "mysp";
CommandType commandType = CommandType.StoredProcedure;
DbHelper.CommandTimeOut = 0;
DbParameter[] dbParameter = new DbParameter[4];
dbParameter[0] = DbHelper.CreateParameter(Settings, "@HCPID", ent.UserID);
dbParameter[1] = DbHelper.CreateParameter(Settings, "@From", ent.StartTime);
dbParameter[2] = DbHelper.CreateParameter(Settings, "@To", ent.EndTime);
dbParameter[3] = DbHelper.CreateParameter(Settings, "@LoadType", LoadType);
list = DbHelper.ExecuteList32<AppointmentsViewEnt_1>(Settings, commandType, commande, dbParameter);

update

请注意，如果我在正常运行的另一个客户端上使用相同的代码使用相同的应用程序，并且我永远不会遇到相同的错误。此错误仅在一个客户端发生。

update

SQL配置文件数据：

• CPU 2479读取31791写0持续时间3578 Starttime
  14/06/2016 22:33:30末日14/06/2016 22:33:34

Answer 1

谢谢大家的帮助。经过数小时的搜索和故障排除，SQL 2008R2损坏了！我安装了SQL 2014，一切都很好，内存从10 GB降至5 GB！大多数时候，CPU负载少于20％（尽管在某些情况下它加载到70％但罕见情况，我将再次检查我的代码）

Answer 2

以为任何未来的寻求者添加更多详细信息。这个问题可能会从所谓的参数嗅闻中发生，并且可以通过将with recompile添加到存储过程中来解决：

create procedure mysp
@par varchar(100
with recompile
as 
-- def of SP goes here