Speed up application and avoid SQL Timeouts

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;

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

......
}

StackExchange Code Review Q#36161, answer score: 5