Shoushou Shoushou - 27 days ago 5
SQL Question

SQL SP fast in SQL Slow from C#

I have the following 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


if I run the SP from SQL it takes 1-2 secs, if I call it from C# using the same parameters values, it takes about 10 secs, and sometimes I get one of the following messages.
First Error
Second Error

I call it from C# as below:

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

Please note that if I use the same application with the same code at another client it running normally, and I never encounter the same error. This error happens only at one client.

UPDATE

SQL Profile Data:


  • CPU 2479 Reads 31791 Write 0 Duration 3578 StartTime

    14/06/2016 22:33:30 EndTime 14/06/2016 22:33:34


Answer

Thank you all for your kind help. After hours of searching and troubleshooting, It was the SQL 2008R2 corrupted! I installed SQL 2014 and all went good, the memory decreased from 10 GB to 5 GB! and the CPU load is less than 20% most of the time(Although on some situations it loads to 70% but rare cases and I will check my code again)