Moving files according to paths in database

Question

I'm trying to move a large number of files. I get the path of source and destination from database.

It takes hours to move images, and I was wondering if there is a faster way for moving large numbers of files.

Table example:

source	destination
C:\x	D:\y
C:\z	D:\y

This the code I'm using.

var datasource = @"00.000.0.00"; 
var database = "maydatabase"; 
var username = "power"; 
var password = "123"; 
string connString = @"Data Source=" + datasource + ";Initial Catalog="
           + database + ";Persist Security Info=True;User ID=" + username + ";Password=" + password;
SqlConnection conn = new SqlConnection(connString);
     static void moveImg(string getimg, SqlConnection conn) 
        {
                using (SqlCommand command = new SqlCommand(getimg, conn)) 
                {
                    command.CommandTimeout = 0;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if ((File.Exists(reader["source"].ToString())) && !(File.Exists(reader["destination"].ToString())))

                            {

                                    File.Move(reader["source"].ToString(), reader["destination"].ToString(), true);
                            }

                        }

                    }

                }
        }

Note

The number of rows in database can exceed 1 million, meaning 1 million images to move. Each image has size between 16KB and 60KB.

The query takes 1.30 mins to run. I don't know how can I get the timing for how long a file move takes, as not every file is same size. I can tell from the log file that it moves around 4 to 6 files in 1 second.

Answer 1

Here are my observations and suggestions:

connString

• I would suggest to prefer string interpolation over string concatenation

string connString = $@"Data Source={datasource};Initial Catalog={database};Persist Security Info=True;User ID={username};Password={password}";

• Since C# 8 the ordering of @ and $ does not matter

using

• You can use take advantage of C# 8's new using declaration over using statement

using var command = new SqlCommand(getimg, conn);
command.CommandTimeout = 0;
using var reader = command.ExecuteReader();

Fetch

• I would suggest to separate database read from actual move operations
• Your database connection should not last long

static IEnumerable<(string Source, string Destination)> FetchMoveParameters(string getimg, SqlConnection conn)
{
    var result = new List<(string Source, string Destination)>();
    using var command = new SqlCommand(getimg, conn);
    command.CommandTimeout = 0;
    using var reader = command.ExecuteReader();
    while (reader.Read())
    {
        result.Add(reader["source"].ToString(), reader["destination"].ToString());
    }

    return result;
}

• I've used ValueTuples here but you can create your own structure for that

Move

• If you want to increase the performance of move operations then you should consider to use parallelism
• You have to make sure that there are no two (or more) conflicting instructions in the fetched move parameters to avoid race condition

var moveParams = FetchMoveParameters(...);
moveParams.AsParallel().ForAll(moveParam =>
{
    //move source to destination
});

Alternative

Parallel.ForEach(moveParams, moveParam =>
{
    //move source to destination
});

• And as always measure, measure and measure
  • Make sure that the newly applied changes are giving some performance gain

Answer 2

Quick suggestion:

1. Iterate through all the records in the database
2. Group them by folder
3. See if there are other items in the folders
4. Moving folders is exponentially faster than individual files
5. If the files are on different drives, or you are not allowed to change the directory structure, you are pretty much SOL and will just have to be patient =)