Jul 8, 2016

Complex or multiple tables join best practices SQL server performance tuning

How to optimize table JOIN if there are many tables in a JOIN statement in the SQL Server? How to decrease numbers of tables in a JOIN?

Jul 6, 2016

Efficient | fast way to populate large volume of data in a table c# SQL Server

Sometimes there is a need to populate | insert large volume of data from application code(c#, java, vb.net) to a table in the database (SQL Server).  If data volume is less then performance is not big concern. But if data volume is big we must have to consider most efficient approach. Let us discuss about different approaches and their advantages and disadvantages:

Looping over insert statement:
One of the easiest ways is write an insert statement inside a loop. And it will populate records one by one in the database. For example:
foreach (DataRow row in table.Rows) {

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Users(Name, Phone, Age) VALUES (@Name, @Phone, @Age)");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        cmd.Parameters.AddWithValue("@Name", row[1]);
        cmd.Parameters.AddWithValue("@Phone", row[2]);
        cmd.Parameters.AddWithValue("@Age", row[3]);
        connection.Open();
        cmd.ExecuteNonQuery();
    }
}
      
Here problem with above script is there is separate database call for each row.  For large volume of records above script will take significant amount of time.

Multiple insert statement in a single query:

In this approach first all insert queries are saved in a string variable. Then whole insert statement is executed in the database at the same time. For example:

string Query = @"INSERT INTO Users(Name, Phone, Age) VALUES ('Name_1', 'Phone_1', 'Age_1')
INSERT INTO Users(Name, Phone, Age) VALUES ('Name_2', 'Phone_2', 'Age_2')
.....
.....
INSERT INTO Users(Name, Phone, Age) VALUES ('Name_n', 'Phone_n', 'Age_n')"

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand(Query);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;
    connection.Open();
    cmd.ExecuteNonQuery();
}

In the above approach database is called only one time but inside database rows are being inserted one by one. So it is rows based approach and it is not efficient in case of large volume of data.

Table value constructor:

In SQL server table value constructor is set based approach. And it is very efficient.  Instead of full insert statement we can save the query string in table value constructor format. For example:
string Query = @"INSERT INTO Users(Name, Phone, Age)
       VALUES ('Name_1', 'Phone_1', 'Age_1')
       VALUES ('Name_2', 'Phone_2', 'Age_2')
       .....
       .....
       VALUES ('Name_n', 'Phone_n', 'Age_n')"
                                 

Although this approach is very efficient but limited to 1000 rows. If there are more than 1000 rows above approach is not going to work.

Passing Data table:
One of the best approaches is passing the data table as parameter to stored procedure. For example:

Create a table type in SQL server:
CREATE TYPE UserType AS TABLE(
      Name VARCHAR(50) NOT NULL,
      Phone VARCHAR(10),
      Age INT
)

Create a stored procedure which accepts UserType as a parameter:

CREATE PROCEDURE AddUsers(
    @UserTable UserType READONLY
)
AS
BEGIN
    INSERT INTO Users
      SELECT * FROM @UserTable
END

Using C# code, execute stored procedure and pass data table as a parameter:

using (SqlConnection conn = new SqlConnection(conn))
using (SqlCommand cmd = new SqlCommand("dbo.AddUsers", conn))
{
    var dt = new DataTable();

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@UserType", dt));  
    conn.Open();
    cmd.ExecuteNonQuery();
         
}

Using bulk insert:

SQL server support bulk inserts that loading the data from a file. For this first we have to download or copy data file at specified location using programming language. Then using BULK INSERT command data can be imported to table. For example:

BULK INSERT EXACT_HELP.dbo.Users
FROM 'C:\Data\Users.txt' 
WITH
      ( 
            FIELDTERMINATOR =' |', 
            ROWTERMINATOR =' |\n' 
      ); 

We can also do by using C# SqlBulkCopy.

Pass data in the XML format in SQL server:

In your programming language, first covert whole data table in XML format. Pass that XML to stored procedure to populate in the table. For example:

Create a stored procedure:

CREATE PROCEDURE AddUers(
    @UserTable XML
)
AS
BEGIN

      DECLARE @idoc int

      EXEC sp_xml_preparedocument @idoc OUTPUT, @UserTable;

    INSERT INTO Users
      SELECT  * 
      FROM OPENXML (@idoc, '/ROOT/Users',1) 
    WITH (
            Name VARCHAR(50),
            Phone VARCHAR(10),
            Age INT)

END

C# code to pass XML data:
                  
DataSet ds = new DataSet();
ds.Tables.Add(dt1);
string UserXml= ds.GetXml();

using (SqlConnection conn = new SqlConnection(conn))
using (SqlCommand cmd = new SqlCommand("dbo.AddUsers", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@UserTable", SqlDbType.Xml);   
    cmd.Parameters["@UserTable"].Value = UserXml;        
    conn.Open();
    cmd.ExecuteNonQuery();
         
}