Click here to Skip to main content
15,354,958 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


SQL
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,
Posted
Updated 20-Feb-14 3:12am
v2
Comments
dbrenth 20-Feb-14 11:25am
   
SQL Server Management Studio is very good at determining what indexes are missing and even gives you the script for creating the potential missing indexes (at least in version 2012). Enter your long running sql query and run Query -> Display Estimated Execution Plan.

1 solution

Use SQL Profiler to know which statement for your query takes too much time. extract that statement from sql profiler and try to fine tune by adding index ,changing joins condition or query structure.you can use Estimate execution plan to add missing index as well.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900