Facinging time out error while accessing Stored procedures

See more:

SQL-Server

Hi All,

I am created a SP with name "Attachments" and i am calling this SP form my application. When this SP is Called i am getting below error

CustomRequest.executeProcedureRq procedureName="ATR_Attachments">Parameters>Parameter>N'ATR/R/283754.01'/Parameter>/Parameters>/CustomRequest.executeProcedureRq>: Error processing request [CustomRequest.executeProcedureRq].: Exception has been thrown by the target of an invocation.: [Microsoft][ODBC SQL Server Driver]Timeout expired.


i think there is a problem in my SP where i am using right outer join

please view below SP

USE [ExampleData]
GO
/****** Object:  StoredProcedure [dbo].[ATR_Attachments]    Script Date: 02/20/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ATR_Attachments]  @PolicyNumber VARCHAR(50)
AS
BEGIN

DECLARE @ModifiedPolicyNumber nvarchar(100),@Count int,@ModifiedPolicyNumber1 nvarchar(100),@ModifiedPolicyNumber2 nvarchar(100)    
     SELECT @count=(SELECT len(@PolicyNumber)-len(replace(@PolicyNumber,'/','')))
      IF @count >2
            BEGIN
                  DECLARE @test nvarchar(1000)
                  SELECT @test=(SELECT substring(@PolicyNumber,charindex('/',@PolicyNumber)+1,len(@PolicyNumber)))
                  SELECT @test=(SELECT substring(@test,charindex('/',@test)+1,len(@test)))
                  SELECT @test=(SELECT substring(@test,charindex('/',@test)+1,len(@test)))
                  SELECT @test=(SELECT substring(@PolicyNumber,0,len(@PolicyNumber)-len(@test)))
                  SELECT @ModifiedPolicyNumber=@test
            END
      ELSE
            BEGIN
                  SELECT @ModifiedPolicyNumber=@PolicyNumber
            END
      IF @ModifiedPolicyNumber like '%.%'
            BEGIN
                  SELECT @ModifiedPolicyNumber=(select substring(@ModifiedPolicyNumber,0,charindex('.',@ModifiedPolicyNumber)))
            END
      ELSE
            BEGIN
                  SELECT @ModifiedPolicyNumber=(@ModifiedPolicyNumber)
            END
            select @ModifiedPolicyNumber1=(@ModifiedPolicyNumber+'.%')
            select @ModifiedPolicyNumber2= (@ModifiedPolicyNumber+'/%')
      --below entire query will take the records and insert the records into carry forward quote id's
     INSERT INTO attachments
      SELECT DISTINCT s1.Quoteid as Quoteid, s2.Moniker AS Moniker, s2.caption AS caption, s2.FileName AS FileName, getdate() AS AttachDate, s2.MessageID AS MessageID

      FROM attachments s1 WITH(NOLOCK)
      right outer join 

      (SELECT Quoteid, caption,FileName,Moniker,AttachDate,MessageID FROM attachments WITH(NOLOCK) WHERE Quoteid in (SELECT quoteid FROM Quote WITH(NOLOCK) WHERE LOB not like'CLAIM' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2))
      And Caption not like 'PRINTATTACHMENT%') s2 

      ON s1.Quoteid <> s2.Quoteid and s1.moniker<>s2.moniker and s1.caption<>s2.caption      WHERE 
            S1.Quoteid in (SELECT quoteid FROM quote WITH(NOLOCK) WHERE LOB not like'CLAIM%' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2))
      AND

      not exists (SELECT Quoteid, caption FROM attachments WITH(NOLOCK) WHERE Quoteid in (SELECT quoteid FROM quote WITH(NOLOCK) WHERE LOB not like'CLAIM%' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2)) and Quoteid = s1.Quoteid and  moniker=s2.moniker and caption = s2.caption)and S1.quoteid > s2.quoteid

--above entire query will take the records and insert the records into carry forward quote id's
Select QuoteId    from [ExampleData].[dbo].[Quote] WITH(NOLOCK) where PolicyNumber=@Policynumber;
RETURN @@ROWCOUNT;
      
END

I think we need add Indexs for the join to increase the performance. But i am new to indexs could any please help me on this.

I appreciate your efforts and time.

Thanks,