如何通过链接服务器存档大量数据时避免超时问题？ [英] How to avoid time out issue while archiving huge data via linked server?

问题描述

How to avoid time out issue while archiving huge data via linked server?

我通过工具界面（前端）将表配置为存档流程的主/事务表类型，并将调用Archival SP。

我在为主表（包含大约500万条记录）存档大量数据时遇到问题。所有其他主表都具有较低的数据量，并且事务表存档是使用日期范围完成的，因此也没有超时问题。

以下是我的档案SP的核心部分

I have tables configured as Master/Transaction Table Type for Archival Process via Tool Interface(front end) and will call Archival SP.
I am having time out issue while archiving huge data for a master table(having around 5 million records). All other master tables are with low data volume and Transaction Tables archival are done with a date range and hence no time out issues for those either.
Below is the core part of my Archival SP

IF(@TableType='M') --Master Table                  
		BEGIN     
		
			print '1'
			                  
			SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName
			PRINT  @STRDELETE                    
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
			
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName  
			PRINT   @STRINSERT       								                          
			EXECUTE sp_executesql @STRINSERT                  
			
			
			
		END                  
		ELSE IF(@TableType='T') --Transaction Table                
		BEGIN                  
			
			print '2'
		              
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName + '                   
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                   
			
			print @STRINSERT
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB  : '+ @ARCHIVEDB )                  
													
			SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '                  
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                 
			
			print @STRINSERT
			EXECUTE sp_executesql @STRDELETE                  

			                 
			
			
		END

我尝试过：



以下是我的档案SP的核心部分

What I have tried:

Below is the core part of my Archival SP

IF(@TableType='M') --Master Table                  
		BEGIN     
		
			print '1'
			                  
			SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName
			PRINT  @STRDELETE                    
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
			
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName  
			PRINT   @STRINSERT       								                          
			EXECUTE sp_executesql @STRINSERT                  
			
			
			
		END                  
		ELSE IF(@TableType='T') --Transaction Table                
		BEGIN                  
			
			print '2'
		              
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName + '                   
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                   
			
			print @STRINSERT
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB  : '+ @ARCHIVEDB )                  
													
			SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '                  
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                 
			
			print @STRINSERT
			EXECUTE sp_executesql @STRDELETE                  

			                 
			
			
		END

推荐答案

我假设您使用的是SQL Server。

您可能知道在连接字符串中设置的连接超时，但也有 CommandTimeout ，默认为30秒，对于备份使此值更大，或将其设置为0表示没有超时。

示例：

I assume you are using SQL Server.
You probably know about Connection Timeout which is set in the connectionstring, but there is also CommandTimeout which defaults to 30 seconds, for backups make this value larger, or set it to 0 for no timeout.
Example:

using (var command = new SqlCommand(query, this.connection))
{
        command.CommandTimeout = 0;         // No Timeout: avoid "Timeout expired" error.
        command.ExecuteNonQuery();
}

这篇关于如何通过链接服务器存档大量数据时避免超时问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！