Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS

I have a number of scripts that query the data from different tables and consequently update them. I have defined a batch file and run them in sqlcmd command. It seems that when I run these scripts directly in SQL management studio, they run quicker than when they are called from the batch file.

Is there any reason for this behavior?

Thanks in advance.

  • 6
    the question is answered in this post Running queries with SQLCMD vs. Running queries with SSMS and nice article understanding performance mystries – AmmarR Sep 17 '12 at 5:55
  • Thanks. I added "WITH RECOMPILE" to avoid getting stuck in parameter sniffing as suggested in the above post. It would force the sql server not to look for a plan and create one at run time. – Sky Sep 17 '12 at 6:45
  • Hi, the answer that Aaron gave in the question that Ammar has referenced is spot on. You are unlikely to get a better answer than that I am afraid. – Mr.Brownstone Sep 18 '12 at 22:28

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)

    UPDATE a
        a.TownOrCity = 
                            @TownTble aa 
                            aa.Id = @RandomId
        Addresses a
        a.Id = @i

    SET @i = @i + 1

Thanks for your help!

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