Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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. –  Nazila Beikpour 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
add comment

marked as duplicate by Marian, Mark Storey-Smith, RolandoMySQLDBA, Derek Downey, JNK Sep 25 '12 at 12:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

up vote 0 down vote accepted

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 
BEGIN
    SELECT @RandomId = ROUND(((@Upper_Town - @Lower -1) * RAND() + @Lower), 0)

    UPDATE a
    SET 
        a.TownOrCity = 
                    (
                        SELECT 
                            Town   
                        FROM 
                            @TownTble aa 
                        WHERE 
                            aa.Id = @RandomId
                    ) 
    FROM 
        Addresses a
    WHERE
        a.Id = @i
        AND a.PersonId IS NOT NULL OPTION (RECOMPILE)

    SET @i = @i + 1
END `

Thanks for your help!

share|improve this answer
add comment

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