SQL Server Performance

Why is C#'s SqlClient So Much Faster Than SQLCMD.exe

Discussion in 'ALL SQL SERVER QUESTIONS' started by Aaron Simmons, Sep 27, 2012.

  1. Aaron Simmons New Member

    I've got a file [inserts.sql] with ~80,000 INSERT INTO statements.

    How come SQLCMD.exe takes 83 seconds to execute and a simple C# app which uses SqlClient takes only 13 seconds?!?

    DETAILS
    SQL Server Version: SQL Server 2008 R2

    SQLCMD.exe command line:
    sqlcmd -S [server] -U [username] -P [password] -d [database] -i inserts.sql

    FYI: I've tried throwing "-m15" in there too to prevent the "PRIMARY KEY Violation..." error messages from being returned .. which seemed to work, but it didn't speed anything up?

    The C# app is dirt simple...it does basically the following:
    /////////////////////
    string sql = File.ReadAllText("C:\\directory\\inserts.sql");

    string connectionString = "Data Source=[server];Initial Catalog=[database];User Id=[username];Password=[password];";

    using (SqlConnection connection =
    new SqlConnection(connectionString))
    {
    SqlCommand command = new SqlCommand(sql, connection);
    try {
    command.Connection.Open();
    command.ExecuteNonQuery();
    }
    catch (Exception ex) {
    Console.WriteLine(ex.Message);
    }
    }
    /////////////////////

    Thoughts anyone?
  2. Shehap MVP, MCTS, MCITP SQL Server

    Fundamentally , exec T-SQL scripts using CMD shell either through direct SQLCMD , osql.exe or EXECmaster..xp_cmdshellare taking often long time consideringkernel version ,T-SQL statement nature (Ex: if it contains Print Commands , they might be not executed inside SQL Server but executed as print Commands inside CMD shell )

    Adding also to these parameters, overload on server resources running CMD Shell like Memory , CPU that it might differs extremely in performance reads ( Ex: you might run them on a remote powerful machine rather than local machine and you can get much better reads)

    Moreover, C#SQLClient is using the best SQL provider which is .net data framework provider (.NET SQL client data provider ) which enjoys of lot of rich powers that can speed up running such OLTP transactions of inserts..

    Therefore, it is heavily recommended to switch over all your heavy OLTP transactions of insert to C# SQL client much better than CMD Shell

    Kindly let me know if any further help is needed
  3. Aaron Simmons New Member

    Thank you so much for the response. I saw the Important note in the docs how SQLCMD might perform differently than SQL Server Management Studio (they seem to indicate it might be related to different SET variables being used). But I never would have guessed there would be such wildly different performances between the two.

Share This Page