[Solved] How to retrieve images faster in SQL Server 2014?

EverSQL Database Performance Knowledge Base

How to retrieve images faster in SQL Server 2014?

Database type:

We are using SQL Server 2014 (not SQL Azure) and I have defined a simple table to store images (.jpg) and 

CREATE TABLE [dbo].[Graphic](
    [GraphicID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [FileName] [varchar](100) NOT NULL,
    [FileDescription] [nvarchar](200) NULL,
    [Image] [varbinary](max) NULL
)

max size of an image stored is 1MB, this validation is taken care on the front-end. I just inserted 15 images and the table size is 5544 KB currently. There is a primary key placed on GraphicID column. No other indexes placed.

But when I retrieve one or more images using the below (simple SELECT) query, it is taking longer time like 25 - 30 seconds.

select * from [Graphic]
where [GraphicID] = 53

Is there a faster mechanism to query images in SQL Server in less than 5 seconds ?

Is there any alternate SAVE & RETRIEVE mechanism for images in SQL Server 2014 for better performance ?

Please help.

Thanks

Bhanu

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX graphic_idx_graphicid ON Graphic (GraphicID);
The optimized query:
SELECT
        * 
    FROM
        [Graphic] 
    WHERE
        [Graphic].[GraphicID] = 53

Related Articles



* original question posted on StackOverflow here.