在访问存储过程时面临超时错误 [英] Facinging time out error while accessing Stored procedures

问题描述

大家好，



我创建了一个名为附件的SP，我将这个SP称为我的应用程序。当这个SP被调用时我得到以下错误



CustomRequest.executeProcedureRq procedureName =ATR_Attachments>参数>参数> N'ATR / R / 283754.01 '/Parameter>/Parameters>/CustomRequest.executeProcedureRq> ;:错误处理请求[CustomRequest.executeProcedureRq]：调用目标引发了异常：[Microsoft] [ODBC SQL Server驱动程序]超时已过期 。





i认为我的SP中存在问题，我正在使用右外连接



请查看以下SP

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,

推荐答案

使用SQL事件探查器知道查询的哪个语句需要花费太多时间。从sql profiler中提取该语句，并尝试通过添加索引，更改连接条件或查询结构进行微调。您也可以使用Estimate执行计划添加缺失的索引。

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.

这篇关于在访问存储过程时面临超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！