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();        }    }}
Speed up application and avoid SQL Timeouts
c#;sql;sql server
Try this Queryconst 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 thereIf 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 FalseInside 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 errorsInside your UpdateDocument you execute a command with out exception handling again.to wrap this upI 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 loopYour while loop should be inside of your SqlDataReader using blockusing (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;        }    }......}