|
mohan123
Posting Yak Master
India
201 Posts |
 Posted - 01/15/2013 : 08:42:24
|
hello all,
i have Sp it is giving result set in 40 secs and giving time out issue in application how i need to overcome this issue???
i will declare parameters in different scenarios..
IF @v_TabName = 'Duration'
else
IF @v_TabName = 'Intime'
same query repeats for all the statements
one sample query
IF @v_TabName = 'Intime'
BEGIN
SELECT DISTINCT
QNR.QuestionaireName ,
tUser.UserId ,
Task.TaskId ,
TaskStatus.TaskStatusText ,
ISNULL(TaskMaxAttempt.TaskCount , 0) TaskCount ,
QNR.QuestionaireId
INTO
#tblAssements
FROM
Task WITH (NOLOCK)
LEFT OUTER JOIN ( SELECT
TaskId ,
COUNT(*) AS TaskCount
FROM
TaskAttempts
WHERE
TaskAttempts.AttemptedContactDate IS NOT NULL
GROUP BY
TaskId ) TaskMaxAttempt
ON TaskMaxAttempt.TaskId = Task.TaskId
INNER JOIN TaskStatus WITH (NOLOCK)
ON Task.TaskStatusId = TaskStatus.TaskStatusId
INNER JOIN #tblFilteredUsers tUser WITH (NOLOCK)
ON tUser.UserId = Task.PatientUserId
INNER JOIN Questionaire QNR WITH (NOLOCK)
ON QNR.QuestionaireId = Task.TypeID
AND QNR.StatusCode = 'A'
INNER JOIN TaskType tt
ON tt.TaskTypeId = Task.TaskTypeId
WHERE
( ( Task.TaskDueDate BETWEEN GETDATE()
AND ( GETDATE() + @DueDateValue ) )
OR @v_DueDate IS NULL )
AND tt.TaskTypeName = 'Questionnaire'
AND tt.StatusCode = 'A'
AND ( ( EXISTS ( SELECT
1
FROM
@t_PrimaryCarePhysician ) )
OR ( SELECT
COUNT(*)
FROM
@t_PrimaryCarePhysician ) = 0 )
AND (CASE WHEN DATEDIFF(DAY,GETDATE() ,CASE WHEN ISNULL(Task.TerminationDays , 0) <> 0 THEN DATEADD(DD , Task.TerminationDays , Task.TaskDueDate)
ELSE Task.TaskDueDate
END ) between -7 and -1 THEN -7
WHEN DATEDIFF(DAY,GETDATE() ,CASE WHEN ISNULL(Task.TerminationDays , 0) <> 0 THEN DATEADD(DD , Task.TerminationDays , Task.TaskDueDate)
ELSE Task.TaskDueDate
END ) between -30 and -1 THEN -30
WHEN DATEDIFF(DAY,GETDATE() ,CASE WHEN ISNULL(Task.TerminationDays , 0) <> 0 THEN DATEADD(DD , Task.TerminationDays , Task.TaskDueDate)
ELSE Task.TaskDueDate
END ) = 0 THEN 0
END = @v_DueDate OR @v_DueDate IS NULL )
AND ( EXISTS ( SELECT
1
FROM
@t_ProgramID
WHERE
tKeyId = Task.ProgramID )
OR ( SELECT
COUNT(1)
FROM
@t_ProgramID ) = 0
)
please help me out in optimizing this query
P.V.P.MOhan |
Edited by - mohan123 on 01/15/2013 08:43:28
|
|
Hoe to do performance optimization in SP
