Adding SELECT COUNT(*) subclause trashes performance in SQL Server

Question

I'm building a query and the latest step involved adding a SELECT COUNT(*) FROM [modification] sub-clause to allow me to detect the last row of the query, but it destroys the performance:

SELECT CONCAT(
      IIF(row_number() OVER (ORDER BY forecastId) % 50000 = 1,
          CONCAT('INSERT INTO modification (userId, epochTime, ',
                 'forecastId, description, auxText, auxDate) VALUES ('), 
          '    ('),
      userId, ',',
      epochTime, ',',
      forecastId, ',',
      '''', dbo.encode4MySql(description), ''',',
      '''', dbo.encode4MySql(auxText), ''',',
      '''', CONVERT(VARCHAR(20), auxDate, 120), ''')',
      IIF(row_number() OVER (ORDER BY forecastId) % 50000 = 0
          OR row_number() OVER (ORDER BY forecastId) = 
                      (SELECT COUNT(*) FROM modification),
          '; COMMIT;', ','))
FROM modification
ORDER BY forecastId;

If you can't see what I'm doing, I'm building INSERT () VALUES (),(),(),... statements of 50000 rows at a time.

Please restrict suggestions for completely alternative approaches to the comments. I'm looking for a way to find the last row number here without it slowing the query down massively - which it does.

I'm not massively familiar with query plans but can post one here if it helps. I've tried a lot of things from related questions here, but nothing that I can get to work.

Answer 1

One other option would be to order by forecastId desc in the final or:

IIF(row_number() OVER (ORDER BY forecastId) % 50000 = 0
          OR row_number() OVER (ORDER BY forecastId desc) = 1,
          '; COMMIT;', ','))