Efficient way to load thousands of data from database and process it

Question

I have a table in Sql Server 2008 named PhotoStorages with following columns. This table contains 5000 data.

PhotoStorageId              bigint
Photo                       image

Currently I am loading data using C# as below

string sql = "SELECT * FROM PhotoStorages";
using (SqlCommand sqlCommand = new SqlCommand(sql))
{
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand))
    {
        using (DataTable dataTable = new DataTable())
        {
            dataAdapter.Fill(dataTable);

            if (dataTable.Rows.Count > 0)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    /*Resize the image and again update those resized image to the same database table*/
                }
            }
        }
    }
}

Now the execution of this process is very slow. I want to know is there any alternative way to achieve this. Thanks in advance!

I actually think that the code where you resize the image is the most important! Please add it for us! — cramopy, Jan 16 '16 at 16:03
It's likely that 99% of your slow process happens in the section that you've commented out. Loading 5000 rows from a database may only take milliseconds. SQL connection will only take milliseconds and very few of them. Therefore, I suggest you post the content of your resizing code. — James Hay, Jan 16 '16 at 16:03
@JamesHay I don't think it will take milliseconds if you are getting 5000 of rows containing bytestreem of data... — lucian.nicolaescu, Jan 16 '16 at 16:07
@JamesHay: The photo column contains image that are of big size. So it takes more time. In sql server itself while executing the select query it takes more than 10 minutes. — nightfire001, Jan 16 '16 at 16:09
@lucian.nicolaescu True, I didn't think about that. If that is the case then perhaps you'd want to break this task up into smaller chunks. If you've got megabytes of binary data per image it's going to eat up the memory in your computer which will slow it down immensly — James Hay, Jan 16 '16 at 16:09
@cramopy: Resizing image code is not a big deal. I just want to know efficient way to load the data from the sql server. Is data table good for this or there are any other ways? — nightfire001, Jan 16 '16 at 16:10
@Kushal If the image data size is large as you mentioned, it will almost definitely be more efficient to run the query in several batches. You will incur a small cost per query, but releasing the memory after each batch should keep it running a lot smoother. It does depend on the size of both the images and your available memory however. — James Hay, Jan 16 '16 at 16:13
@JamesHay: Do you have any idea how to break the query in batches? — nightfire001, Jan 16 '16 at 16:15
Do you really need data adapter and data table here, i think using simple data reader will be perfect in your case. — Anupam Singh, Jan 16 '16 at 16:41

score 2 · Accepted Answer · edited May 23 '17 at 12:23

You can break your query up into smaller chunks using the ROW_NUMBER function in SQL Server to paginate your results.

I've based the answer in this question: Paginate rows from SQL

First you have to convert your query to one that performs pagination:

string sql = @"SELECT
                *
              FROM
                (
                    SELECT
                        ROW_NUMBER() OVER(ORDER BY Id DESC) AS RowNumber,
                        *
                    FROM
                        PhotoStorages
                ) AS T
              WHERE
                RowNumber BETWEEN @start AND @start + @pageSize";

You should change the ORDER BY Id to match the actual ID column of your query. In your specific case, you can use any column in your table since you are using all data, you don't need to worry about the order being returned. 5000 rows is not big in the grand scheme of things but if it was a large table 100,000+ rows, you will want to use a column that has an index on it. And DO NOT use the binary data column.

Then you can run your query inside a loop:

var pageSize = 10;
var startAt = 0;

while(true) 
{
    using (SqlCommand sqlCommand = new SqlCommand(sql))
    {
        sqlCommand.Parameters.Add("@start", SqlDbType.Int).Value = startAt;
        sqlCommand.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;

        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand))
        {
            using (DataTable dataTable = new DataTable())
            {
                dataAdapter.Fill(dataTable);

                var rowCount = dataTable.Rows.Count;
                var startAt = startAt + rowCount;

                if (dataTable.Rows.Count > 0)
                {
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        /*Resize the image and again update those resized image to the same database table*/
                    }
                }
                else 
                {
                    break;
                }
            }
        }
    }
}

The idea is to select a certain number of rows (which we have provided via the pageSize variable. We then take those rows do the resize, and continue the loop. The loop will continue until the query returns no rows. The value you use for pageSize is up to you, but as an example if you have 2GB of memory free, and each photo is 2MB, you will very roughly be using all memory if your pageSize is 1000 rows. You want to use substantially less than all available memory as this will not only slow down this process, but all other processes happening on the machine that require free memory.

My C# skills are very rusty, but hopefully that should work for you.

Efficient way to load thousands of data from database and process it

1 Answers1