0

Problem statement : I've a big table and I read only Top 10000 elements of the table at a time. This is for batch processing(insertion) which has a where clause and one order by statement. Now, in order to proceed for the next batch I need to know the count of current batch and current pointers that is the last row elements of the current batch. If the count < 10000 we can exit the loop.

How can I get the last row elements of the current batch i.e. the 10000th row of the current batch? And how can I get the count of the current Top query?

Note: We might not get 10000 rows for each Top 10000 queries.

This is what I tried. If you look at the query I'm using same query at three different places. temp table might not be a choice here because I think that might increase the overhead. Can you help me optimize the query?

   DECLARE @column1Pointer BIGINT = 0
   DECLARE @column2Pointer BIGINT = 0
    
// Inserting the results into a second table 

  INSERT INTO table2
  (
    Column1, Column2
  )
  SELECT   TOP(@size), Column1, Column2
  FROM table1
  WHERE <same condition>
  ORDER BY Column1 , Column2

// Fetching the last element of the row. first I use order by asc and then desc and then fetch top 1 row
  SELECT  TOP 1  @column1Pointer = Column1, @column2Pointer = Column2
          FROM 
          (
                SELECT   TOP(@size) Column1, Column2
                FROM table1
                
                WHERE <same condition>
                ORDER BY Column1 , Column2
          ) T  ORDER BY T.Column1 DESC , T.Column2 DESC
          OPTION (RECOMPILE)

// third place with same query to get the count of the batch 
  SELECT  @countInBatch = COUNT(*)
              FROM 
              (
                  SELECT   TOP(@size) Column1, Column2
                  FROM table1
                  WHERE <same condition>
                  ORDER BY Column1 , Column2
              ) T
              OPTION (RECOMPILE)
0
0

If I correctly understand you could use @@ROWCOUNT after the INSERT command to get the affected rows:

Given the next example:

CREATE TABLE t1 (v1 int PRIMARY KEY, v2 int);
CREATE TABLE t2 (v1 int, v2 int);
INSERT INTO t1 VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

DECLARE @top int = 4,
        @affectedRows int,
        @pointer1 int,
        @pointer2 int;

You can store @ROWCOUNT value:

INSERT INTO t2
SELECT TOP (@top) v1, v2
FROM   t1
--WHERE <CONDITION>
ORDER BY v1, v2;

SET @affectedRows = @@ROWCOUNT;

And then get last row values:

SELECT TOP 1 @pointer1 = v1, @pointer2 = v2
FROM (SELECT TOP (@top) v1, v2
      FROM   t1
      --WHERE <CONDITION>
      ORDER BY v1, v2) T
ORDER BY v1 DESC, v2 DESC;

This is the result:

SELECT @affectedRows Rows, 
       @pointer1 Pointer1, 
       @pointer2 Pointer2;
Rows Pointer1 Pointer2
4 4 4

db<>fiddle here

0
0

If the ability to restart the batch process is important, you might consider this approach.

First, we'll create a sandbox (shamelessly stolen from @McNets excellent answer)

USE tempdb;
SET XACT_ABORT ON;

DROP TABLE IF EXISTS dbo.t1, dbo.t2, #t;

CREATE TABLE dbo.t1
(
      v1        int     NOT NULL
        PRIMARY KEY
        CLUSTERED
    , v2        int     NOT NULL
);

CREATE TABLE dbo.t2
(
      v1        int     NOT NULL
        PRIMARY KEY
        CLUSTERED
    , v2        int     NOT NULL
);

INSERT INTO t1 (v1, v2)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), sc2.id
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

On my system, this results in slightly over 1.2 million rows being inserted into the dbo.t1 table.

This is the batch processing piece:

DECLARE @msg nvarchar(1000);
--this makes the process restartable
DECLARE @id  int = COALESCE((SELECT TOP(1) t2.v1 FROM dbo.t2), 0);
WHILE EXISTS (SELECT TOP(1) 1 FROM dbo.t1 t WHERE t.v1 > @id)
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY

        INSERT INTO dbo.t2 (v1, v2)
        SELECT TOP(5000) 
              t1.v1
            , t1.v2
        FROM dbo.t1
        WHERE dbo.t1.v1 > @id;
        
        SET @id = COALESCE((
            SELECT TOP(1) t.v1
            FROM dbo.t2 t
            ORDER BY t.v1 DESC
            ), 0);

        COMMIT TRANSACTION;
        CHECKPOINT;

    END TRY
    BEGIN CATCH
        SET @msg = N'ERROR_NUMBER() = ' + CONVERT(nvarchar(10), ERROR_NUMBER(), 0) 
            + N', ERROR_MESSAGE() = ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION;
        PRINT @msg;
        BREAK;
    END CATCH
END

On my system, this copies 5,000 rows per batch for a total of 240 batches in 9 seconds. It's restartable and can be scheduled on an on-going basis, and will only copy rows when there are new rows to be copied.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.