Call stored procedure with long running time

Question

I'm attempting to call a stored procedures using C#. The stored procedures have a long running time (3 to 4 minutes), and they cause timeout exceptions when I try to run them. How can I allow them to finish running with a longer timeout?

And when it throws an exception, does the stored procedure continue to run on the server, or does it stop?

My application is a game server and the stored procedure runs to update and delete mail . It runs slow because there are too many records and too many connections for user to insert another a record at the same time as the SP runs.

I only need to run the SP and wait for a long time for the server response.

@minitech Thank you for editing my question

Here is the SP code

ALTER    PROCEDURE [dbo].[SP_Mail_Scan]
 @NoticeUserID nvarchar(4000) output

AS  
Set @NoticeUserID=''
Declare @AuctionID Int        
Declare @AuctioneerID Int
Declare @AuctioneerName Nvarchar(100)
Declare @BuyerID Int
Declare @BuyerName Nvarchar(100)
Declare @ItemID Int
Declare @PayType Int
Declare @Price Int
Declare @Name Nvarchar(200)


Declare @MailID Int
Declare @SenderID Int
Declare @Sender Nvarchar(100)
Declare @ReceiverID Int
Declare @Receiver Nvarchar(100)
Declare @Title Nvarchar(1000)
Declare @Content Nvarchar(4000)
Declare @SendTime DateTime
Declare @IsRead Bit
Declare @IsDelR Bit
Declare @IfDelS Bit
Declare @IsDelete Bit
Declare @Annex1 Nvarchar(100)
Declare @Annex2 Nvarchar(100)
Declare @Gold Int
Declare @Money Int
Declare @Remark Nvarchar(200)
Declare @Annex3 Nvarchar(100)
Declare @Annex4 Nvarchar(100)
Declare @Annex5 Nvarchar(100)


Set @SenderID =0
Set @Sender =dbo.GetTranslation('SP_Mail_Scan.Sender')  --
Set @ReceiverID = ''
Set @Receiver = ''
Set @Title =dbo.GetTranslation('SP_Mail_Scan.Title')
Set @Content =dbo.GetTranslation('SP_Mail_Scan.Content')
Set @SendTime  = getdate()
Set @IsRead  = 0
Set @IsDelR = 0
Set @IfDelS = 0
Set @IsDelete =0
Set @Annex1 =''
Set @Annex2 =''
Set @Gold =0
Set @Money =0
Set @Annex3 =''
Set @Annex4 =''
Set @Annex5 =''

If object_id('tempdb..#PayMail') Is Not null
Drop Table #PayMail 

Create Table #PayMail 
(
id Int Identity(1,1), 
MailID Int Not Null,
SenderID Int Not Null,
Sender Nvarchar(200) Not null,
ReceiverID Int not null,
Receiver Nvarchar(200) not null,
Title Nvarchar(1000) not null,
Annex1 Nvarchar(100) not null,
Annex2 Nvarchar(100) not null,
Annex3 Nvarchar(100) not null,
Annex4 Nvarchar(100) not null,
Annex5 Nvarchar(100) not null,
) 

insert into #PayMail select [ID],SenderID,Sender,ReceiverID,Receiver,Title,isnull(Annex1,''),isnull(Annex2,''),isnull(Annex3,''),isnull(Annex4,''),isnull(Annex5,'') from User_Messages with(nolock) 
where IsExist=1 and Type>100 and datediff(hh,SendTime,getdate())>ValidDate and [Money]>0

  declare @NewTitle nvarchar(200)
  declare @NewContent nvarchar(200)
  set @NewTitle = dbo.GetTranslation('SP_Mail_Scan.Msg1')
  set @NewContent =dbo.GetTranslation('SP_Mail_Scan.Msg2')

set xact_abort on 
  begin tran

INSERT INTO User_Messages( SenderID, Sender, ReceiverID, Receiver, Title, Content, SendTime, IsRead, IsDelR, IfDelS, IsDelete, Annex1, Annex2, Gold, Money, IsExist,Type,Remark, Annex3, Annex4, Annex5) 
      select ReceiverID,Receiver,SenderID,Sender,@NewTitle+Title,REPLACE(@NewContent,'{0}',Receiver),getdate(), 0, 0, 0, 0,Annex1,Annex2,0,0,1,7,'Gold:0,Money:0,Annex1:'+Annex1+',Annex2:'+Annex2+',Annex3:'+Annex3+',Annex4:'+Annex4+',Annex5:'+Annex5,Annex3,Annex4,Annex5
    from #PayMail

  if @@error<>0 or @@ROWCOUNT =0
    begin
      rollback tran
      return 1 
   end

  update User_Messages set IsExist=0,@NoticeUserID = @NoticeUserID + cast(SenderID as nvarchar(50)) + ',' from User_Messages where [ID] in (select MailID from #PayMail)

  if @@error<>0 or @@ROWCOUNT =0
    begin
      rollback tran
      return 1 
   end

  commit tran
set xact_abort off

if len(@NoticeUserID)>0
begin
 set @NoticeUserID = substring(@NoticeUserID,1,len(@NoticeUserID)-1)
end
--set @NoticeUserID='100,200'

return 0

Answer 1

As per your sample code, you have use "Declare" for each of variable. Why don't you use single Declare for each variable. Like this

Declare  @AuctionID Int        
        ,@AuctioneerID Int
        ,@AuctioneerName Nvarchar(100)
        ,@BuyerID Int
        ,@BuyerName Nvarchar(100)
        ,@ItemID Int
        ,@PayType Int
        ,@Price Int
        ,@Name Nvarchar(200)

        ,@MailID Int
        ,@SenderID Int
        ,@Sender Nvarchar(100)
        ,@ReceiverID Int
        ,@Receiver Nvarchar(100)
        ,@Title Nvarchar(1000)
        ,@Content Nvarchar(4000)
        ,@SendTime DateTime
        ,@IsRead Bit
        ,@IsDelR Bit
        ,@IfDelS Bit
        ,@IsDelete Bit
        ,@Annex1 Nvarchar(100)
        ,@Annex2 Nvarchar(100)
        ,@Gold Int
        ,@Money Int
        ,@Remark Nvarchar(200)
        ,@Annex3 Nvarchar(100)
        ,@Annex4 Nvarchar(100)
        ,@Annex5 Nvarchar(100)

Even why don't you use single 'Select' instead of "set" for each variable like this

Select   @SenderID =0
        ,@Sender = dbo.GetTranslation('SP_Mail_Scan.Sender')  --
        ,@ReceiverID = ''
        ,@Receiver = ''
        ,@Title = dbo.GetTranslation('SP_Mail_Scan.Title')
        ,@Content = dbo.GetTranslation('SP_Mail_Scan.Content')
        ,@SendTime  = getdate()
        ,@IsRead  = 0
        ,@IsDelR = 0
        ,@IfDelS = 0
        ,@IsDelete =0
        ,@Annex1 =''
        ,@Annex2 =''
        ,@Gold =0
        ,@Money =0
        ,@Annex3 =''
        ,@Annex4 =''
        ,@Annex5 =''

You have created #table for your insert operation but you have not specified any key for that table, I suggest, you should define Primary key

Create Table #PayMail 
(
     id Int Identity(1,1) Primary Key
    ,MailID Int Not Null
    ,SenderID Int Not Null
    ,Sender Nvarchar(200) Not null
    ,ReceiverID Int not null
    ,Receiver Nvarchar(200) not null
    ,Title Nvarchar(1000) not null
    ,Annex1 Nvarchar(100) not null
    ,Annex2 Nvarchar(100) not null
    ,Annex3 Nvarchar(100) not null
    ,Annex4 Nvarchar(100) not null
    ,Annex5 Nvarchar(100) not null
)

and you have used "with (Nolock)" when inserting into #table, so you should use with (Nolock) when select from #table also. And if your #table is used only once for insertion then I suggest you should use With CTE (Common Table Expression) for same, it may increase your SP performance.

Answer 2

You can use Asynchronous Procedure Execution. This is a reliable way to execute the procedure w/o the client having to wait for it to finish.

But a procedure like the one you describe (which has to run periodically and scan for mails) is more likely a job and SQL Agent is a better fit.

Answer 3

I presume you are using ADO.NET SqlCommand?

SqlCommand.CommandTimeout will set the timeout you require

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.80).aspx

The stored procedure stops as soon as the client times out.

Answer 4

Four minutes is getting pretty hefty, even a database with millions of rows and plenty of tables to join can return results in seconds if designed and queried in the correct way.

You need to spend some time with the sproc and Explain Plan to figure out why it's taking so long. Consider data segmentation, splitting the query into discrete chunks, indices, how joins are constructed, subselects etc. Explain Plan will give you where it is wasting its time.

Stored procedures should not take four minutes to run, especially if they're running on some kind of client application.

I had a legacy query which was taking 52 minutes to run, with a small refactor it was 40 seconds, then after a little more, 6 seconds. Even 40 seconds is pushing it really.

If you have such a massive query and it is reporting based, please consider a data warehouse as it will feature a schema design much more suited to these purposes.