SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hoe to do performance optimization in SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/15/2013 :  08:42:24  Show Profile  Reply with Quote
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

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 01/15/2013 :  12:10:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 01/15/2013 :  12:21:23  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 01/16/2013 :  01:17:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000