Performance Analysis of SQL Queries Handling Binary Data

SQL DATA
As data inevitably grows in web applications some use cases start responding slowly compared to others which have more or less the same amount of data. There can be several reasons for this, but in this blog we are specifically targeting SELECT statements which refer to tables that have binary (Image) fields. Later in the blog we will show you some real time results carried out on large amount of data with table containing binary data.

Filesizes

Before we can show our findings there is a standard rule of thumb for handling this kind of data

<256KB (DB is efficient)
>1MB (File system is efficient)
>256 && <1MB (Depends the type of operations and frequency)

There are many approaches to handle this situation and it depends on your particular architecture, but our finding can be useful in most cases developers will face.

Our demo

In our test case table we have a binary field which contains a jpg file, we replicated the data up to 500,000 rows so that we can replicate a real time web application scenario after couple of months of usage. We had some amazing results which helped us improve the performance of an application when we ran SELECT statements with three variations; first access the whole table, then accessing only the binary field and finally accessing whole table excluding the binary field.

The results from the experiment are:

SELECT * FROM TestTable (26:30 minutes for 500,000 rows)
SELECT ImageField FROM TestTable (23:33 minutes for 500,000 rows)

Now the most important result, which can help you improve the performance of your application SELECT statement which excludes binary data column:

SELECT Col1,Col2 … ColN FROM TestTable (<= 00:03 minutes for 500,000 rows)

This behavior can easily be achieved by EF by using anonymous types and excluding the image column which makes the SELECT statement, and eventually the response of your web application, run slowly.

var objallImageUploads =
(from tt in objContext.TestTables
select new TempImages
{
Id = tt.Id,
ImageName = tt.ImageName,
.
.
.
Ncolumn = tt.Ncolumn
}).ToList();

Using the binary

Now the question arises, you have to show that image you saved in your binary data field.

The answer is quite simple, the best way to show your image is to create a page where you can pass the Id of that field and that page in turn renders that image on the screen. Anonymous types in EF are big asset but remember – you should only bring the fields which you want to display to the user, making the response of web application smooth and fast.

 

Find out more about the Strategic Apps development process and client projects on our website

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s