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.
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 queryIF @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 queryP.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.aspxI 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? |
|
|
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 ) |
|
|
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 executionP.V.P.MOhan |
|
|
|
|
|
|
|