Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hoe to do performance optimization in SP

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-15 : 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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-15 : 12:10:26
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

I might have some time to look at teh query it self, but you need to give us more information. How many rows are in each table? What are the indexes on each table? What are the datatype of each column? What does the query plan look like?

Have you tried anything in particular? Is there a particular part of the query that is the bottle-neck?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-15 : 12:21:23
I don't know what the performance impact is, but I did a quick look over and I can see that one of youer predicates in meaningless. It looks like you are checking to see if the @t_PrimaryCarePhysician (presumably a table variable) has a row or has no rows which is the same as not checking:
    AND 
(
(
EXISTS
(
SELECT
1
FROM
@t_PrimaryCarePhysician
)
)
OR
(
SELECT
COUNT(*)
FROM
@t_PrimaryCarePhysician
) = 0
)
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-16 : 01:17:15
i have created index for all required columns and i gave with(NOLOCK) and how i reduce the timing of query execution

P.V.P.MOhan
Go to Top of Page
   

- Advertisement -