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();
}
}
}
