4
\$\begingroup\$

The code works just fine, but recently performace has taken a hit and it has SQL Timeouts far too often. I have it set up so I can run 50 different version of the application two for each different m.qlevel.

This allows it to run fairly fast, but the SQL timeouts are requiring to much babysitting.

document_attachments has over 3 millions rows and jm_documentationissues gains a row anytime I successfully update a row from document_attachments.

The reason it times out is because depending on the time of the day we have some pretty intense jobs running which hog all of the SQL resources causing my application to time out and fail.

I run two applications per qlevel one asc order and one desc order. This creates a problem when there is only one row left and both applications pull that same row it throws a primary key failure.

The above bug and the SQL time out are the two biggest problems with this application. It runs slow since it is trying to navigate through 3 millions rows to find a document path, go into a directory of 5 million files, move it to a new directory and then update the table if successful.

Any improvements to this are welcome!

** I've thought maybe of grabbing 100,000 rows and throwing it into a variable, and then processing from that list until it is 0 then grabbing another 100,000. I'm just not sure if that is the best or even a good approach. The only drawback to this approach is it might limit me to one application at a time which would takes months to complete.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;

namespace LattitudeDocumentationOrganizer
{
    internal class Program
    {
        private static readonly string connString = LattitudeDocumentationOrganizer.Properties.Settings.Default.ConnectionString;

        private static void Main()
        {
            int fileCount = 0;
            int errorCount = 0;
            bool RunLoop = true;
            //List<Task> tasks = new List<Task>();

            while (RunLoop)
            {
                Console.Clear();

                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    // Run through
                    const string Sql = @"select top 1 d.UID, d.CreatedDate, d.Location, m.number from master m with (NOLOCK)
                                    inner join documentation_attachments da with (NOLOCK)
                                        on m.number = da.accountid
                                    inner join documentation d with (NOLOCK)
                                        on da.documentid = d.uid
                                    where m.qlevel = 999
                                    and d.location is not null
                                    and uid not in (select documentid from JM_DocumentationIssues)
                                    order by m.number desc";
                    using (SqlCommand command = new SqlCommand(Sql, connection))
                    using (SqlCommand updateCommand = CreateUpdateCommand(connection))
                    using (SqlCommand logErrorCommand = CreateLogErrorCommand(connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // If no data is returned assume no data is left and report statistic and exit loop
                            if (!reader.HasRows)
                            {
                                Console.WriteLine("Processed {0} files successfully.", fileCount);
                                Console.WriteLine("Did not process {0} files successfully.", errorCount);
                                Console.WriteLine("No more files were found with the current query");
                                Console.ReadLine();
                                Console.WriteLine("Exiting program.");
                                break;
                            }

                            // Close the loop out as a specific day and time.
                            DateTime currentDate = DateTime.Now;
                            if (currentDate.DayOfWeek == DayOfWeek.Monday && currentDate.Hour >= 20)
                            {
                                Console.WriteLine("Processed {0} files successfully.", fileCount);
                                Console.WriteLine("Did not process {0} files successfully.", errorCount);
                                Console.WriteLine(
                                    "The time set for this process to end has been reached.  Program exited at {0}",
                                    DateTime.Now);
                                Console.ReadLine();
                                Console.WriteLine("Exiting program.");
                                break;
                            }

                            Console.WriteLine("Processing data...");
                            while (reader.Read())
                            {
                                SqlCommand updateCommand1 = updateCommand;
                                SqlCommand logErrorCommand1 = logErrorCommand;

                                // Row Values
                                // 0 = UID
                                // 1 = CreatedDate
                                // 2 = Location
                                Guid documentID = reader.GetGuid(0);
                                string fileName = reader.GetSqlValue(0) + ".zip";
                                string location = reader.GetString(2);
                                DateTime createdDate = reader.GetDateTime(1);
                                int number = reader.GetInt32(3);

                                Console.WriteLine("Current File #: {0}", fileCount);
                                Console.WriteLine("Working on document {0}", documentID);

                                FileInfo fileinfo = new FileInfo(Path.Combine(location, fileName));

                                string msg;
                                if (!fileinfo.Exists)
                                {
                                    // Log error to JM_DocumentationIssues
                                    msg = "This file does not exist";
                                    LogError(logErrorCommand1, documentID, location, null, msg, number);
                                    Console.WriteLine(
                                        "This file did not exist, logged it to the database and moving on to the next file.");
                                    errorCount++;
                                    reader.NextResult();
                                }
                                else
                                {
                                    // file exists begin process to create new folders
                                    var fileYear = "DOCS" + createdDate.Year;
                                    var fileMonth = createdDate.ToString("MMM");
                                    const string RootDir = @"\\192.168.22.23";
                                    Console.WriteLine(
                                        "File Exists, checking to make sure the directories needed exist.");

                                    if (!Directory.Exists(Path.Combine(RootDir, fileYear)))
                                    {
                                        // Should no longer error out here all root level folders are created 2006-2014.
                                        // Error, cannot create root level network share folder.  Log to Database.
                                        // Error Root Level Folder Missing
                                        // Directory.CreateDirectory(Path.Combine(rootDir,fileYear));
                                        // Log error to JM_DocumentationIssues
                                        //msg = "Could not create root folder, log to skip this file";
                                        //LogError(logErrorCommand1, documentID, location, Path.Combine(RootDir, fileYear), msg, number);
                                        //errorCount++;
                                    }
                                    else
                                    {
                                        if (!Directory.Exists(Path.Combine(RootDir, fileYear, fileMonth)))
                                        {
                                            // Create the month folder
                                            Directory.CreateDirectory(Path.Combine(RootDir, fileYear, fileMonth));
                                            Console.WriteLine(
                                                "The month folder did not exist, created {0} folder", fileMonth);
                                        }

                                        // Call method to update location in database and move tile
                                        UpdateDocument(updateCommand1, documentID, Path.Combine(RootDir, fileYear, fileMonth));
                                        fileinfo.MoveTo(Path.Combine(RootDir, fileYear, fileMonth, fileName));
                                        ////File.Move(Path.Combine(location, fileName), Path.Combine(rootDir, fileYear, fileMonth, fileName));
                                        msg = "SUCCESS";

                                        LogError(
                                            logErrorCommand1,
                                            documentID,
                                            location,
                                            Path.Combine(RootDir, fileYear, fileMonth),
                                            msg,
                                            number);
                                        Console.WriteLine(
                                            "Successfully moved and logged the file, checking for more files.");
                                        fileCount++;
                                        reader.NextResult();
                                        ////break;
                                    }
                                }
                            }

                            //Task.WaitAll(tasks.ToArray());
                        }
                    }
                }
            }
        }

        private static SqlCommand CreateUpdateCommand(SqlConnection connection)
        {
            const string Sql = "update documentation set location = @newLocation where uid = @id";
            SqlCommand command = new SqlCommand(Sql, connection);

            command.Parameters.Add(new SqlParameter("id", SqlDbType.UniqueIdentifier));
            command.Parameters.Add(new SqlParameter("newLocation", SqlDbType.VarChar, 50)); // guessing varchar(50) here
            command.Prepare();

            return command;
        }

        private static void UpdateDocument(SqlCommand command, Guid id, string newLocation)
        {
            command.Parameters["id"].Value = id;
            command.Parameters["newLocation"].Value = newLocation;
            command.ExecuteNonQuery();
        }

        private static SqlCommand CreateLogErrorCommand(SqlConnection connection)
        {
            const string Sql =
                @"insert into JM_DocumentationIssues (documentid, oldLocation, newLocation, errorMessage, dateAdded, number)
                            values (@id, @prevLocation, @newLocation, @msg, GETDATE(), @number)";
            SqlCommand command = new SqlCommand(Sql, connection);

            command.Parameters.Add(new SqlParameter("id", SqlDbType.UniqueIdentifier));
            command.Parameters.Add(new SqlParameter("prevLocation", SqlDbType.VarChar, 260)); // guessing varchar(255) here
            command.Parameters.Add(new SqlParameter("newLocation", SqlDbType.VarChar, 260)); // guessing varchar(255) here
            command.Parameters.Add(new SqlParameter("msg", SqlDbType.VarChar, -1)); // guessing varchar(255) here
            command.Parameters.Add(new SqlParameter("number", SqlDbType.Int));

            command.Prepare();
            return command;
        }

        private static void LogError(SqlCommand command, Guid id, string prevLocation, string newLocation, string msg, int number)
        {
            if (newLocation == null)
            {
                newLocation = "no new location";
            }

            command.Parameters["id"].Value = id;
            command.Parameters["prevLocation"].Value = prevLocation;
            command.Parameters["newLocation"].Value = newLocation;
            command.Parameters["msg"].Value = msg;
            command.Parameters["number"].Value = number;

            command.ExecuteNonQuery();
        }
    }
}
\$\endgroup\$
  • \$\begingroup\$ Have you done an execution plan on the SQL statements inside your code? \$\endgroup\$ – John Deters Nov 26 '13 at 22:35
  • \$\begingroup\$ @JohnDeters I have not during times when the sql resources are free it returns a single row in less than a second. It isn't until other things tie up the sql resources that my code is pushed down the job cycle and thus times out. \$\endgroup\$ – James Wilson Nov 26 '13 at 22:44
5
\$\begingroup\$

Try this Query

const string Sql = @"select top 1 d.UID, d.CreatedDate, d.Location, m.number from master m with (NOLOCK)
                     inner join documentation_attachments da with (NOLOCK)
                         on m.number = da.accountid
                     inner join documentation d with (NOLOCK)
                         on da.documentid = d.uid
                     LEFT JOIN JM_DocumentationIssues WITH (NOLOCK) 
                         ON d.UID = JM_DocumentationIssues.UID
                 where m.qlevel = 999
                       and d.location is not null
                       -- and uid not in (select documentid from JM_DocumentationIssues)
                 order by m.number desc";

I think that I have the correct join in there

If you use the NOT IN or the <> in the where statement it can cause issues with large queries because it has to run the nested query every time that it goes through a record.

With this query you should be able to do the SELECT TOP 50000 or whatever, which is what you should do.

The code needs to be changed. you need the DataReader to call a Query that returns more than 1 row and then go row by row, maybe do a mass update. this would speed up the application and/or SQL Query and release a lot of resources.

You should be looping through the returned records and not calling the SQL 500,000 times. That is what is slowing down the SQL Database.


You need to look at what you are doing here, because your two if blocks of code are the only place where you can break out of the while loop.

You set a boolean variable RunLoop to True and then tell a while loop to run while that boolean is True but never set it to False

Inside your While loop you have a bunch of using statements that if they fail, the exception is never caught. There is no Exception Handling going on at all.

I am sure that one of your loops is probably doing more work than you think it is.


It also looks like you are using LogError to log the results of the application and not actually logging errors


Inside your UpdateDocument you execute a command with out exception handling again.


to wrap this up

I think that you should take out all of the Console.WriteLine() nonsense and actually log that stuff to a text file or something maybe,

Console.WriteLine() takes a lot of time, I wish that I could remember the post where I saw this, the post actually gave the times of a Console.WriteLine and it wasn't pretty.

Maybe just start out by calling Console.WriteLine() less by merging the strings together.


To Elaborate what @rolfl said in his answer. all of your using blocks should be outside of your while loop

Your while loop should be inside of your SqlDataReader using block

using (SqlDataReader reader = command.ExecuteReader())
{
    While (RunLoop)
    {
        // If no data is returned assume no data is left and report statistic and exit loop
        if (!reader.HasRows)
        {
            Console.WriteLine("Processed {0} files successfully.", fileCount);
            Console.WriteLine("Did not process {0} files successfully.", errorCount);
            Console.WriteLine("No more files were found with the current query");
            Console.ReadLine();
            Console.WriteLine("Exiting program.");
            break;
        }
    }

......
}
\$\endgroup\$
  • \$\begingroup\$ I was unaware of the time console.writeline() used. I will remove them, but I highly doubt those are the cause of the SQL timeouts. The applications runs incredible fast when the SQL resources are plentiful. It runs so fast that I cannot even read the writelines(). The code is ordered ugly because it worked one way then went through a significant changes and rather than rewriting i just hijacked existing working code. \$\endgroup\$ – James Wilson Nov 26 '13 at 22:55
  • \$\begingroup\$ I'm just trying to understand this better. If I move my RunLoop to where you suggested after my reader wouldn't this application run a single time affecting a single row only? My reader will only have a single row by design. Or the application finishes the last line of code here will it automatically go back up to my reader line and select a new top 1? \$\endgroup\$ – James Wilson Nov 26 '13 at 23:13
  • \$\begingroup\$ @JamesWilson, let me digest that for a little bit \$\endgroup\$ – Malachi Nov 26 '13 at 23:18
  • \$\begingroup\$ @JamesWilson I haven't forgot about this. you will have to change the select statement so that it grabs all the records that you want, and then go through each record in the query. I will look at it again tomorrow and see what I can figure out to make it nicer. \$\endgroup\$ – Malachi Nov 27 '13 at 6:35
  • 1
    \$\begingroup\$ @JamesWilson, the speed of which the Console.WriteLine's are printing, or your inability to read them, is not a very good indicator of the performance. \$\endgroup\$ – Max Nov 27 '13 at 14:57
5
\$\begingroup\$

Instantiate Objects Once, and early

  • Move declarations to the top, above any while or using blocks
  • Move assignment of constants to the top.

Here are some: .

const string Sql = @"select top 1 d.UID, d.CreatedDate, d.Location, m.number from master m with (NOLOCK)
                                inner join documentation_attachments da with (NOLOCK)
                                    on m.number = da.accountid
                                inner join documentation d with (NOLOCK)
                                    on da.documentid = d.uid
                                where m.qlevel = 999
                                and d.location is not null
                                and uid not in (select documentid from JM_DocumentationIssues)
                                order by m.number desc";

.

string msg;

.

const string RootDir = @"\\192.168.22.23";

.

const string Sql =
            @"insert into JM_DocumentationIssues (documentid, oldLocation, newLocation, errorMessage, dateAdded, number)
                        values (@id, @prevLocation, @newLocation, @msg, GETDATE(), @number)";

.

const string Sql = "update documentation set location = @newLocation where uid = @id";

System.Data.SqlClient objects are composites. Instantiate the parts at the top and then compose them as needed

  • This means you will not be "newing-up" objects in the using block parameter. But it does not mean you can't use using.
  • If you need multiple SqlConnection objects, SqlConnection implements ICloneable

.

SqlConnection oneConnToRuleThemAll = new SqlConnection(connString);
SqlCommand command = new SqlCommand(oneConnToRuleThemAll);
SqlCommand updateCommand = CreateUpdateCommand(oneConnToRuleThemAll);

// if we swap out parameters for a command, make individual SqlParameter collections
SqlParameterCollection updateParameters = new SqlParameterCollection() {
    new SqlParameter( "id", SqlDbType.UniqueIdentifier ),
    new SqlParameter( "prevLocation", SqlDbType.VarChar, 260 )
};
SqlCommand logErrorCommand = CreateLogErrorCommand(oneConnToRuleThemAll);

Then

using ( SqlCommand thisCommand = command  )
using ( SqlCommand updateCommand = updateCommand )
using ( SqlCommand logErrorCommand = logErrorCommand )

String Theory

Turn multiple WriteLines into a single write:

 Console.WriteLine( "Processed {0} files successfully.", fileCount );
 Console.WriteLine( "Did not process {0} files successfully.", errorCount );
 Console.WriteLine( "No more files were found with the current query" );

into this:

// and do this "at the top"!
string processNotice = "Processed {0} files successfully.\n" +
                       "Did not process {1} files successfully.\n" +
                       "Did not process {0} files successfully.";

 // and use it thus:
 Console.Writeline(processNotice,fileCount, errorCount);

One more thing(s)

  • Open the connection immediately before you execute the command. Close it ASAP after reading.
  • Make a struct rather than separate variables - documentID, fileName,location,createDate, number. Keeps the record association. Will simplify method parameters. Makes sense.
  • Remove ExecuteNonQuery from CreateLogErrorCommand(), UpdateDocument(), etc. Keep configuration separate from execution. This will help with refactoring this code monstrosity.
  • Wrap the db calls - ExecuteNonQuery(), including the connection.Open() that should be immediately above it, in a try/catch and catch SqlException

EDIT: added SqlException bullet above.

\$\endgroup\$
3
\$\begingroup\$

Just so I get this straight in my head..... (My C# is non-existent).

You have:

  1. a connection to a SQL Server database
  2. with that connection you select TOP 1 .... from a complex join query
  3. with that record you create a couple of other prepared statements, one for the update, and another for a possible error condition.
  4. you then update the record's location with a new file location.... and at the same time also add an 'issue record' so that the record is not selected the next time through the loop.
  5. you then return to step 1.

If this is the case, you should:

Only connect once! The connection to the database should happen outside the loop (not a new connection for every record).

The command, updateCommand and errorCommand should all be 'prepared' outside the loop too.

You should select more than just 'TOP 1' from the results, and you should probably be careful with your transactions so that the database performance is not limited by the amount of time it takes to unzip a file

\$\endgroup\$
  • \$\begingroup\$ That should just be as simple as moving my RunLoop to right after my connection.Open() I assume? \$\endgroup\$ – James Wilson Nov 26 '13 at 22:59
  • \$\begingroup\$ your While loop should be inside of your SqlDataReader using block \$\endgroup\$ – Malachi Nov 26 '13 at 23:02
  • \$\begingroup\$ @Malachi but wouldn't that prevent my original select top 1 from running multiple times? If I change that to be select top 10000 I can no longer safely run multiple instances of my application because of possibility of primary key violation. Unless I am just missing something. \$\endgroup\$ – James Wilson Nov 26 '13 at 23:05
  • \$\begingroup\$ @JamesWilson, you actually aren't calling that query before the SqlDataReader \$\endgroup\$ – Malachi Nov 26 '13 at 23:07
  • 1
    \$\begingroup\$ @JamesWilson - just so you are aware, the connect to the database is probably half your time.... and the prepared statements is about half of the remaining time.... \$\endgroup\$ – rolfl Nov 27 '13 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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