提高Sql过程性能

Question

我有一个从表中选择记录的Sql过程，如果匹配，则插入到不同的table.It，如下所示。

    CREATE PROCEDURE [dbo].[MarkCopyData] 
@copyDataOperationId int,
@countryCode nvarchar(50),
@batchCount int,@batchId int,@jobId int
    AS
    BEGIN
DECLARE @TableObjectId AS Int;
SET @TableObjectId = OBJECT_ID('Table1');
INSERT INTO  
    Table2(
            TableObjectId, 
            SourcePrimaryKey, 
            DestinationCopyOperationId, 
            IsModifiedAftertCopied,IsDeleted,BatchId,JobId)

    SELECT TOP (@batchCount) @TableObjectId, tbl1.pkid, @copyDataOperationId,      0,0,@batchId,@jobId
    FROM [dbo].[Table1] AS tbl1
    Inner join [dbo].[Country] AS C on C.CountryId =  tbl1.fkid
    LEFT OUTER JOIN Table2 tbl2 on (tbl2 .SourcePrimaryKey = tbl1.pkid and tbl2 .TableObjectId = @TableObjectId)
    WHERE C.CountryCode = @countryCode AND tbl2.DestinationCopyOperationId IS NULL
    SELECT @@ROWCOUNT
    END
    GO

有没有人能给出一些建议来提高这个过程的性能？

Answer 1

如果看不到底层表的结构，就不可能提供帮助。尝试使用CTRL+L查看估计的执行计划。你可能想注释掉CREATE PROCEDURE的东西，这会使计划更容易阅读。

通常情况下，需要创建一个或两个索引。

您可能会发现，将C.CountryCode = @countryCode从WHERE子句移到INNER JOIN中可能会有一些帮助。通常，您希望在开始执行外连接之前过滤结果集。

SELECT TOP (@batchCount) @TableObjectId, tbl1.pkid, @copyDataOperationId,0,0,@batchId,@jobId
FROM [dbo].[Table1] AS tbl1
Inner join [dbo].[Country] AS C on C.CountryId =  tbl1.fkid AND C.CountryCode = @countryCode
LEFT OUTER JOIN Table2 tbl2 on (tbl2 .SourcePrimaryKey = tbl1.pkid and tbl2 .TableObjectId = @TableObjectId)
WHERE tbl2.DestinationCopyOperationId IS NULL