5
\$\begingroup\$

I'm not familiar with threads but I am not against them either. A bit of background the original program would have taken about 2 years to run. I had modified it and knocked down that time to around 40 days.

Can I make it even faster by using threads or updating my code?

The reason it is 40 days is because it has to navigate through a directory of about 6 million files. That amount of files slows this program down.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Threading;

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

        static void Main(string[] args)
        {
            string fileName;
            Guid documentID;
            string location;
            DateTime createdDate;
            bool runLoop = true;
            int x = 0;
            string msg;
            int fileCount = 0;
            int errorCount = 0;
            int number = 0;


            while (runLoop)
            {
                Console.Clear();
                // reset variables
                fileName = "";
                documentID = Guid.Empty;
                location = "";
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    // Run through
                    string sql = @"select top 1 d.UID, d.CreatedDate, d.Location, m.number from master m
                                    inner join documentation_attachments da
                                        on m.number = da.accountid
                                    inner join documentation d
                                        on da.documentid = d.uid
                                    where m.qlevel in (998,999)
                                    and d.location is not null
                                    and uid not in (select documentid from DocumentationIssues)";
                    using (SqlCommand command = new SqlCommand(sql, 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)
                            {
                                runLoop = false;
                                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)
                            {
                                runLoop = false;
                                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())
                            {
                                // Row Values
                                // 0 = UID
                                // 1 = CreatedDate
                                // 2 = Location
                                documentID = reader.GetGuid(0);
                                fileName = reader.GetSqlValue(0).ToString() + ".zip";
                                location = reader.GetString(2);
                                createdDate = reader.GetDateTime(1);
                                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));

                                if (!fileinfo.Exists)
                                {
                                    // Log error to DocumentationIssues
                                    msg = "This file does not exist";
                                    LogError(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++;
                                }
                                else
                                {
                                    // file exists begin process to create new folders
                                    var fileYear = "DOCS" + createdDate.Year.ToString();
                                    var fileMonth = createdDate.ToString("MMM");
                                    var rootDir = @"\\server";
                                    Console.WriteLine("File Exists, checking to make sure the directories needed exist.");


                                    if (!Directory.Exists(Path.Combine(rootDir, fileYear)))
                                    {
                                        // 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 DocumentationIssues
                                        msg = "Could not create root folder, log to skip this file";
                                        LogError(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(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(documentID, location, Path.Combine(rootDir, fileYear, fileMonth), msg, number);
                                        //runLoop = false;
                                        Console.WriteLine("Successfully moved and logged the file, checking for more files.");
                                        fileCount++;
                                    }

                                }
                            }
                        }
                    }
                }
            }
        }

        static void UpdateDocument(Guid id, string newLocation)
        {
            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();

                string sql = "update documentation set location = @newLocation where uid = @id";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("id", id));
                    command.Parameters.Add(new SqlParameter("newLocation", newLocation));

                    command.ExecuteNonQuery();
                }
            }
        }

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

            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();

                string sql = @"insert into documentationissues (documentid, oldLocation, newLocation, errorMessage, dateAdded, number)
                                values (@id, @prevLocation, @newLocation, @msg, GETDATE(), @number)";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("id", id));
                    command.Parameters.Add(new SqlParameter("prevLocation", prevLocation));
                    command.Parameters.Add(new SqlParameter("newLocation", newLocation));
                    command.Parameters.Add(new SqlParameter("msg", msg));
                    command.Parameters.Add(new SqlParameter("number", number));

                    command.ExecuteNonQuery();
                }
            }
        }
    }
}
\$\endgroup\$
  • \$\begingroup\$ What version of .Net are you using? \$\endgroup\$ – Jeff Vanzella Oct 7 '13 at 20:35
  • 1
    \$\begingroup\$ Threading may help, and it's a great tool to have in your tool belt, but if you're navigating that many files, the bottleneck may be disk access. You could perform some profiling to figure out whether that's the case. \$\endgroup\$ – Robert Gowland Oct 10 '13 at 13:06
6
\$\begingroup\$

Let's fire up some threads in the form of .NET 4.0 TPL Tasks. I also create your commands once and Prepare the statements, only updating the parameters. That should relieve some GC and pooled database connection pressure. See how this works for you. Here's the updated code:

namespace LattitudeDocumentationOrganizer
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Threading.Tasks;

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

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

            while (true)
            {
                Console.Clear();
                // reset variables
                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
                                    inner join documentation_attachments da
                                        on m.number = da.accountid
                                    inner join documentation d
                                        on da.documentid = d.uid
                                    where m.qlevel in (998,999)
                                    and d.location is not null
                                    and uid not in (select documentid from DocumentationIssues)";
                    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);

                                tasks.Add(Task.Factory.StartNew(() =>
                                {
                                    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 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++;
                                    }
                                    else
                                    {
                                        // file exists begin process to create new folders
                                        var fileYear = "DOCS" + createdDate.Year;
                                        var fileMonth = createdDate.ToString("MMM");
                                        const string RootDir = @"\\server";
                                        Console.WriteLine(
                                            "File Exists, checking to make sure the directories needed exist.");

                                        if (!Directory.Exists(Path.Combine(RootDir, fileYear)))
                                        {
                                            // 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 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++;
                                            ////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 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, 255)); // guessing varchar(255) here
            command.Parameters.Add(new SqlParameter("newLocation", SqlDbType.VarChar, 255)); // guessing varchar(255) here
            command.Parameters.Add(new SqlParameter("msg", SqlDbType.VarChar, 255)); // 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\$ Hey Jesse I copied this over and it keeps erroring out stating that reader1 is closed. I'll have to step through it and play with it a bit, not sure what is causing the issue just yet. \$\endgroup\$ – James Wilson Oct 7 '13 at 21:02
  • \$\begingroup\$ I think I know why - gotta be honest I didn't test it due to the fact I don't have your database schema, etc. The tasks need to be waited on before the reader gets disposed. Let me see if I can come up with something interesting to mitigate that. (time passes) DONE. Give that a look-see. \$\endgroup\$ – Jesse C. Slicer Oct 7 '13 at 21:13
  • \$\begingroup\$ A different error than before. It thinks there was no data found from the sql query. Running the query in sql server returns the correct result. I'm not sure how but it seems the reader is closing out before it is being used. With I knew more about threading. =( \$\endgroup\$ – James Wilson Oct 7 '13 at 22:25
  • \$\begingroup\$ Well, frak. I'll give it some more look-see a little later on. \$\endgroup\$ – Jesse C. Slicer Oct 7 '13 at 22:27
  • 1
    \$\begingroup\$ @JamesWilson the TPL itself is managing a pool of threads that should be adequate for the computer it's being run on. However, check out this article at Microsoft: msdn.microsoft.com/en-us/library/ee789351 \$\endgroup\$ – Jesse C. Slicer Oct 8 '13 at 16:21
9
\$\begingroup\$

I see a number of things in this code. I'll address all of them, then get to multi-threading. Keep in mind, I'm writing these down as I make changes in the code, so I might suggest something, then change that suggestion.

  1. I like your variable names. Very descriptive and I understand what information they are holding. The only exception is connString, I think it should be make readonly and changed to 'ConnString' indicating a private static readonly variable.
  2. The declarations of the variables used in the function should be moved closer to where they are being used. This does a couple of things: makes the declaration a little more clear to any other developers, and it ensures the value hasn't been unintentionally altered between declaration and usage.
  3. Learn about var. I find that using it really cleans up extra code that just causes clutter.
  4. I would make the declaration of sql const, and move it to be a class variable. At the same time, it should be renamed SelectFileInformationSql
  5. Good job with the using using.
  6. The runLoop variable is set in places it is never needed because of the break statements just below it. You can remove this variable, and replace the main loop to a while(true)
  7. The x variable is unused, remove it.
  8. The variable rootDir should be declared const, moved to a class variable and renamed RootDir
  9. The second declaration of sql should be declared const, moved to a class varible and renamed UpdateDocumentLocationSql
  10. The thrid declaration of sql should be declared const, moved to a class varible and renamed LogIssueSql
  11. I would like to see document moved out into its own class. This way you can do things like FilenameAndPath = Path.Combine(Location, Filename) only once, instead of 2-3 times in the original code. It also packages all the information needed for processing that file into one nice package.
  12. I find the msg variable to be unneeded. You can just put the string right in the function call.

I will get to multithreading when I found out what version of .Net you are using :)

\$\endgroup\$
  • 1
    \$\begingroup\$ +1 for #5 alone, along with everything else. I am usually harping on code which ignores using or even Dispose() in any fashion. It's sad that the MSDN examples are rather lacking in this department. \$\endgroup\$ – Jesse C. Slicer Oct 7 '13 at 20:39
  • \$\begingroup\$ Thanks for all of those points. The version I am currently using is .NET Framework 4. \$\endgroup\$ – James Wilson Oct 7 '13 at 20:44
  • \$\begingroup\$ Also I keep the runLoop in there because this program takes around 45 days to run. I do a break point and change runLoop to false when I need to safely exit the program. Is that still possible with the while(true)? \$\endgroup\$ – James Wilson Oct 7 '13 at 21:14
  • \$\begingroup\$ I'm pretty sure the break; that comes in the code 6 lines below makes the runLoop obsolete because the break; will end the loop. But test it. \$\endgroup\$ – Jeff Vanzella Oct 7 '13 at 23:45
  • \$\begingroup\$ I'm not so sure about moving the SQL strings out, I think it makes sense to have them close to their usage. \$\endgroup\$ – svick Oct 8 '13 at 7:45
1
\$\begingroup\$

Some ideas to speed up and improve your code:

  1. Consider refactoring, i.e. break down the steps in the while loop to individual methods. This will not improve speed, but makes the code even more readable.

  2. Read in all data from the database at once. One SELECT is much faster than 6 million single statements. If the table is too large, use paging.

  3. Collect all the FileInfo items and create a list of paths to examine. A dictionary of destination paths will drastically reduce the amount of calls to Directory.Exists. So, while looping, just collect the path and check (and create) the directory only when a new path is to be put into the dictionary. Also, don't do the other operations within the loop. Just collect. After that step you can safely update your file info and move the files without checking.

  4. Avoid updating each record individually. Create a bulk update/insert instead. I create a temp table on the server using SqlBulkCopy and then do a single UPDATE..FROM tempTable.

The bottleneck is for sure the file operations. While multithreading is a nice way to speed up things, I am not sure if this applies to I/O operations in general, as there is usually a single disk spinning which is optimized for carrying out one file operation at a time. But, it's worth a try.

When you collect your FileInfo items (together with the update info items) in a List<FileInfoData>, you can easily use PLINQ to process the updates in parallel FileInfoList.AsParallel().ForAll(..). Of course, how you exactly use multithreading depends on your .NET version.

An alternative to #3 could be to defer the individual file operations to a worker thread pool, i.e. create a job and let a set of threads to the work. This can be done inside the loop.

Honestly, I am not too familiar with .NET and so I can't tell you if there is something prebuilt regarding worker thread pools.

Here's some pseudocode:

string sql = @"SELECT ...."; // without the 'TOP 1'
using (var command = new SqlCommand(sql, connection))
{
  using (var reader = command.ExecuteReader())
  {
    while (reader.read())
    {
      var documentInfo = GetDocumentInfo(reader);
      if (!PathDictionary.Contains(documentInfo.Path))
      {
         EnsureDirectoryExists(documentInfo.Path);
         PathDictionary.Add(documentInfo.Path);
      }
      WorkerThreadPool.AddJob(new WorkerThreadJob(documentInfo));
    }
  }
}
PerformBulkUpdateOperation();

...

The WorkerThreadJob would also create a dataset containing the UPDATE information. This dataset is used by the PerformBulkUpdateOperation to create the temp table on the server and perform the single UPDATE operation.

\$\endgroup\$
  • \$\begingroup\$ I would combine this answere with the suggestions of Jeff Vanzella and everything should be fine \$\endgroup\$ – 0xBADF00D Oct 9 '13 at 7:55
  • \$\begingroup\$ Nice. Let us know how this improved speed. \$\endgroup\$ – alzaimar Oct 9 '13 at 8:01

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.