Slow running grid loop creating

Question

I'm trying to create an image to SQL parser, but I've run into a snag, it starts off running really quick but as time goes on it progressively slows down, and the memory keeps seesawing between 60MB and 300MB. For a 3000x3000 image it takes over 16 hours......

class Img2Sql
{
    static string table_name="test";
    static string sql_data="";
    public void Start()
    {
        Console.Write("Enter in a full path to file: ");
        String file_full_path = Console.ReadLine();

        Bitmap image = AForge.Imaging.Image.FromFile(file_full_path);
        Console.WriteLine("Loaded image from File.... {0}\n", file_full_path);

        int x = 0;
        int y = 0;
        int grid_x = image.Width;
        int grid_y = image.Height;

        Color pix;
        for (y = 0; y < grid_y; y++)
        {
            for (x = 0; x < grid_x; x++)
            {
                Console.WriteLine("({0},{1})",x,y);
                pix = image.GetPixel(x, y);
                sql_data += pixel_to_sql(pix, x, y);
                //process_slow_destory_max_min(ref pix, ref img, x, y);

            }
        }
        Console.WriteLine(sql_data);
    }

    static string pixel_to_sql(Color pix,int x,int y)
    {
        return ("INSERT INTO "+table_name+"(red,green,blue,x,y) VALUES("+pix.R+","+pix.G+","+pix.B+","+x+","+y+");\n");
    }
}

It seems like a fairly straight forward loop.....

I'm sure you have your reasons, but is that the *best* way to be storing your image data? 9,000,000 inserts is a lot of inserts which is presumably the slow part. Have you looked at creating a DataTable and using a fire hose cursor to insert it (see SqlBulkCopy)? — Plymouth223, Aug 19 '13 at 00:08
I need this for a larger project I'm working on for my company. I can't really talk about the project as a whole, as it's still in alpha stages and is therefore fairly confidential, however we really need this function working correctly, as it is a core component. Is anyone able to help me solve the original query? — Mattisdada, Aug 19 '13 at 02:58
At least use a StringBuilder to build your sql_data, no? ...not sure about the overall impact, but string += string 9000000 times sure ain't helping! ...good luck! — Mathieu Guindon, Aug 19 '13 at 03:35
You might be able to do something like this: http://stackoverflow.com/questions/12510714/improving-speed-of-getpixel-function-in-c-sharp - use lockbits instead of getpixel. I imagine the Console.WriteLine call in the inner loop might also be slowing things down. — Daniel Neal, Aug 19 '13 at 07:21
What dbms are you using? In some there are "image" datatype, e.g. mssql. — athabaska, Aug 19 '13 at 07:50

score 1 · Answer 1 · answered Aug 19 '13 at 14:33

I'm gonna guess that your problem is string concatenation. Without measuring actual performance with a profiler, this IS only a guess, but I do see a problem.

This line:

 sql_data += pixel_to_sql(pix, x, y);

Strings in .NET are immutable, so when you add onto a string, it creates an entirely new string each time. So for a 3000x3000 image, you are creating 9 million string values that are each stored in memory (for a while).

Use StringBuilder instead, like this:

StringBuilder sql_data_builder = new StringBuilder();
...
sql_data_builder.Append(pixel_to_sql(pix, x, y));
...
Console.WriteLine(sql_data_builder.ToString());

score 0 · Accepted Answer · answered Aug 19 '13 at 11:13

You might try running parallel threads. As it stands now the code will run on a single core and probably you have a multiple processor you could take advantage of. Partition the image into chunks and run each chunk in its own thread.

Also, using a StringBuilder will help as string ops are notoriously slow and you have plenty of them.

score 0 · Answer 3 · answered Aug 19 '13 at 14:09

You may want to look into the SqlBulkCopy class.

You would then do something along these lines.

Color pix;
var myList = new List<myPixel>();
for (y = 0; y < grid_y; y++)
{
    for (x = 0; x < grid_x; x++)
    {
        Console.WriteLine("({0},{1})",x,y);
        pix = image.GetPixel(x, y);
        myPixels.Add(new myPixel() { Pixel = pix, X = x, Y = y});
    }
}

var dataTable = //convert your object to a datatable

dbConnection.Open();
//Save to SqlServer
var bulkCopy = new SqlBulkCopy(dbConnection) { DestinationTableName = "YourDatabaseTableName"};

bulkCopy.WriteToServer(dataTable);
dbConnection.Close();

Slow running grid loop creating

3 Answers3