Below are what I did to speed up running the scripts with SQLCMD as the outcome of the reading the nice article AmmaR referred me to:
For each script, I added
OPTION (RECOMPILE), so SQL would not need to have a query plan, it is compiling and running the script every time. It is especially important when we have used variables in the script. The variables are unknown to SQL until run time. For example in the code below, the randomId is calculated based on what RAND() function returns, so SQL Server has no idea what it would be beforehand. Adding
OPTION (RECOMPILE) helped to run this script faster, even though I was running it using SQLCMD.
SET @i = 1
WHILE @i <= @Address_Row_Count
SELECT @RandomId = ROUND(((@Upper_Town - @Lower -1) * RAND() + @Lower), 0)
aa.Id = @RandomId
a.Id = @i
AND a.PersonId IS NOT NULL OPTION (RECOMPILE)
SET @i = @i + 1
Thanks for your help!