SQL Script Has to be Changed to Reduce SQL Serverload

Question

A couple of years ago it was a script designed to import persons into a temporary table. Now this script takes 10 minutes to load and that causes problems sometimes.

So I decided to check the code for optimizing but I have no clue how I can change it.

The current code looks like this

// Getting attributes from the configfile
string filePath = getAppSetting("filepath");
string fileName = getAppSetting("filename");
string fileBP = filePath + fileName;

if (File.Exists(fileBP))
{
    // Truncate Temp-Table
    SqlCommand command = new SqlCommand("TRUNCATE TABLE [dbo].[temp_person];", connection);
    command.ExecuteNonQuery();
FileStream logFileStream = new FileStream(fileName, FileMode.Open,     FileAccess.Read, FileShare.ReadWrite);
StreamReader logFileReader = new StreamReader(logFileStream, System.Text.Encoding.Default);

while (!logFileReader.EndOfStream)
{
    string line = logFileReader.ReadLine();
    strActLine = line;
    string sql = "INSERT INTO temp_person(per_nummer, per_pid, per_name)"
                 + "VALUES(@per_nummer, @per_pid, @per_name)"

    SqlCommand cmd = new SqlCommand(sql, connection);

    cmd.Parameters.AddWithValue("@per_nummer", isNull(line.Substring(0, 7)));
    cmd.Parameters.AddWithValue("@per_pid", isNull(line.Substring(7, 7)));
    cmd.Parameters.AddWithValue("@per_name", isNull((line.Substring(14, 20))));
    cmd.ExecuteNonQuery();
}
// Clean up
connection.Close();
logFileReader.Close();
    logFileStream.Close();
}

In this code I open for each person a new connection and it makes no sense to do that. Is it possible to change that to a bulk insert or something like that? The file does not have any kind of separators like a ";"

I'm Using MSSQL 2008 R2, .Net 4.0 (higher is currently not possible on this server)

looks like it is already answered. stackoverflow.com/questions/3825901/… — Jeremy
– Jeremy, Commented Jul 28, 2015 at 13:07
Why are you doing this instead of using SSIS and a Flat file source? It takes minutes to setup. SSIS uses BULK INSERT which means it can insert hundreds of rows per second. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 28, 2015 at 13:07
Or you can simply use BULK INSERT directly to insert your fixed-width file, as the linked question shows — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 28, 2015 at 13:08
Using SSIS will bring in other dependencies, which the user may not want. — Rich Rousseau
– Rich Rousseau, Commented Jul 28, 2015 at 13:11
@RichRousseau the only "dependency" is SQL Server itself. It's part of the product. In fact, simply selecting Task>Import Data will bring up a wizard that in the end generates and executes an SSIS package. Anyway, SSIS is the right tool for ETL jobs — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 28, 2015 at 13:13

Russ · Accepted Answer · 2015-07-28 13:08:08Z

1

Presuming your logFileStream file ("fileName") has all the users you are loading, you are NOT opening a new connection as you think. It is currently using one connection to TRUNCATE the table, then load all entries from the file fileName.

The only way to make this run any faster would be to use the Bulk Insert SQL Server statement, details of which you can find here: https://msdn.microsoft.com/en-us/library/ms188365.aspx

answered Jul 28, 2015 at 13:08

Russ

4,19325 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panagiotis Kanavos Over a year ago

There's nothing wrong with using a single connection and there are alternatives, like using SSIS or bcp. All viable alternatives use the bulk import mechanism but their flexibility varies

Russ Over a year ago

He commented that he was using multiple connections--connecting with each insert, which is not true per the code he posted.

Edmund Schweppe · Accepted Answer · 2015-07-28 13:45:27Z

Have you investigated the SqlBulkCopy class? It provides several different ways to bulk-copy data from .NET code into SQL Server. Here's an example using a DataTable to buffer the records:

if (File.Exists(fileName))
{
    TruncateTempTable(connection);
    DataTable newRecs = new DataTable();
    newRecs.Columns.Add("per_nummer", typeof (string));
    newRecs.Columns.Add("per_pid", typeof(string));
    newRecs.Columns.Add("per_name", typeof(string));
    using (TextReader tr = File.OpenText(fileName))
    {
        while (tr.Peek() > 0)
        {
            string theLine = tr.ReadLine();
            DataRow newRow = newRecs.NewRow();
            newRow["per_nummer"] = theLine.Substring(0, 7);
            newRow["per_pid"] = theLine.Substring(7, 7);
            newRow["per_name"] = theLine.Substring(14, 20);
            newRecs.Rows.Add(newRow);
        }
    }
    SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
    bulkCopy.WriteToServer(newRecs);
}

Much more detail is available at the MSDN link above.

Chandrashekar Jupalli · Accepted Answer · 2015-07-28 13:16:45Z

0

I hope this will work

BULK INSERT Reports FROM  @ReportFile WITH (FIELDTERMINATOR = '|',ROWTERMINATOR = '\n')

if file data like

M1009|20130502|E400969|ARACIL ALONDRA A|2013050220131202201404022014040220140408
M1009|20130502|N1000533|BARRY PATRICIA| 2013050220131202201404022014040220140408
M1009|20130502|N1001263|GRAYSON JOSEPH| 2013050220131202201404022014040220140408
M1009|20130502|N1026710|GANZI LOUIS R.| 2013050220131202201404022014040220140408

this should works with t-sql

answered Jul 28, 2015 at 13:16

Chandrashekar Jupalli

3573 silver badges15 bronze badges

1 Comment

loonybin Over a year ago

i do nat have a FIELDTERMINATOR in the file. its like your first roe without the "|"

Collectives™ on Stack Overflow

SQL Script Has to be Changed to Reduce SQL Serverload

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related