T-SQL: Slow performance on Stored Procedure

Question

I am new to T-SQL and I am facing an issue with a slow processing stored procedure. The purpose of this procedure is to transfer table data from child databases up to their corresponding parents.

The procedure does this by recursively calling the below listed procedure on the child databases found on other servers (via another procedure called 'RunProcedureOnChildren') and once it reaches the last child it will then get the parent database (via the 'GetParentDatabase' procedure) of that child and push the data upwards.

For the purposes of this question; we will assume that the 'RunProcedureOnChildren' and 'GetParentDatabase' procedures are already being efficient.

Table data Info:

Over 30,000 rows
Primary Key - TestID (int) - Clustered Index
Identity Specification - Yes
Columns - TestID (int), Color (int), ExportedOn (datetime)
Both source and target tables are the exact same; data may vary inside

ALTER PROCEDURE [SendSchema].[TransferMyTable]
AS
BEGIN
    --Step 1: Instruct Children to export their Data up. (recursive call)
    Exec [SendSchema].[RunProcedureOnChildren] '[SendSchema].[TransferMyTable]'

    --Step 2: Get the parent database we'll be sending data to.
    Declare @ParentServer varchar(50), @ParentDatabaseName varchar(50);

    Exec [SendSchema].[GetParentDatabase] @ParentDatabaseName Output, @ParentServer Output

    If @ParentDatabaseName != '' AND @ParentServer != '' OR @ParentDatabaseName IS NOT NULL AND @ParentServer IS NOT NULL Begin
        --Step 3: Get the current export time.
        Declare @ExportedOn datetime;
        Set @ExportedOn = GetDate();

        --Step 4: Update new records to have the current export time.
        Update Top(1000) [Schema].[MyTable] Set [ExportedOn] = @ExportedOn
        From [Schema].[MyTable]
        Where [ExportedOn] is null

        --Step 5: Insert those records into the remote server.
        Declare @TSQL varchar(1500);
        Set @TSQL = 'Insert Into [' + @ParentServer + '].[' + @ParentDatabaseName + '].[Schema].[MyTable] ([Color], [ExportedOn])
                    Select [Color], null From [Schema].[MyTable] 
                    Where [ExportedOn] = ''' + Convert(varchar(25), @ExportedOn, 121) + '''
                    ';
        Exec(@TSQL);
    End
END

RunProcedureOnChildren

ALTER PROCEDURE [SendSchema].[RunProcedureOnChildren] 
    @StoredProcedureName varchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    --Step 1: Remove any old temp tables. This is primarily incase the system fails, that we're not trying to add duplicate records to the temp tables.
    If Object_Id('tempdb..#ChildDatabases') Is Not Null Drop Table #ChildDatabases

    --Step 2: Get the child databases.
    Create Table #ChildDatabases
    (
        [RowNumber] int,
        [ChildDatabaseName] varchar(50),
        [ChildServer] varchar(50),
        [ParentServer] varchar(50)
    )

    Insert Into #ChildDatabases Exec [SendSchema].[GetChildDatabase];

    --Step 3: Execute the Import Data stored procedure on the child databases.
    Declare @RowCount int, @CurrentRow int, @ChildDatabaseName varchar(50), @ChildServer varchar(50);

    Select @RowCount = Count(*) From #ChildDatabases;
    If @RowCount = 0 Begin
        Return
    End
    Set @CurrentRow = 1;

    Declare @OpenQueryTSQL varchar(1500), @TSQL varchar(1500);

    While @CurrentRow <= @RowCount
    Begin
        Select @ChildDatabaseName = [ChildDatabaseName], @ChildServer = [ChildServer]
        From #ChildDatabases
        Where [RowNumber] = @CurrentRow

        Set @TSQL = 'Exec [' + @ChildServer + '].[' + @ChildDatabaseName + '].' + @StoredProcedureName
        Exec (@TSQL)

        Set @CurrentRow += 1
    End

    --Step 4: Clean up the temp tables.
    If Object_Id('tempdb..#ChildDatabases') is not null Drop Table #ChildDatabases
END

GetParentDatabase

ALTER PROCEDURE [SendSchema].[GetParentDatabase]
    @ParentDatabaseName varchar(50) = '' OUTPUT,
    @ParentServer varchar(50) = '' OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    Declare @ServerName varchar(50), @DatabaseName varchar(50), @DatabaseID int;

    Set @ServerName = @@SERVERNAME
    Set @DatabaseName =  DB_NAME()
    
    Select @DatabaseID = [DatabaseID]
    From [Schema].[Database] DB
    Left Join [Schema].[Server] S ON DB.[ServerID] = S.ServerID
    Where DB.[DatabaseName] = @DatabaseName AND S.[ServerName] = @ServerName

    Declare @ParentDatabaseID int;

    Select @ParentDatabaseID = [ParentDatabaseID],
           @ParentServer = [ParentServer ]
    From [Schema].[Mapper]
    Where [DatabaseID] = @DatabaseID

    Select @ParentDatabaseName = [DatabaseName] 
    From [Schema].[Database]
    Where [DatabaseID] = @ParentDatabaseID

    Return
END

GetChildDatabase

ALTER PROCEDURE [SendSchema].[GetChildDatabase]
AS
BEGIN

    SET NOCOUNT ON;

    Declare @ServerName varchar(50), @DatabaseName varchar(50), @DatabaseID int;

    Set @ServerName = @@SERVERNAME
    Set @DatabaseName =  DB_NAME()
    
    Select @DatabaseID = [DatabaseID]
    From [Schema].[Database] DB
    Left Join [Schema].[Server] S ON DB.[ServerID] = S.ServerID
    Where DB.[DatabaseName] = @DatabaseName AND S.[ServerName] = @ServerName

    Select ROW_NUMBER() Over (Order By DB.[DatabaseName] asc) as [RowNumber], DB.[DatabaseName] as [ChildDatabaseName], DBM.[ChildServer], DBM.[ParentServer]
    From [Schema].[Mapper] DBM
    Left Join [Schema].[Database] DB ON DBM.[DatabaseID] = DB.[DatabaseID]
    Where DBM.[ParentDatabaseID] = @DatabaseID


END

I've researched a bit and found some suggestions, but I am not sure if they would even help in my case.

Insert in batches - based off SQL; it looks like only 1000 rows are being inserted
Drop indexes and Recreate Indexes

I would appreciate any suggestions and explanations of where the possible performance issues are and how to fix them. For any solutions please provide explanation and possible documentation.

Hopefully, my question is clear and provided information are clear. If I need to change/update anything please let me know as well.

T-SQL: Slow performance on Stored Procedure

0 Answers