Performance issue in retrieving data from a varbinary(MAX) field

Question

0.00/5 (No votes)

See more:

how i can improve the performance of the sq statement which contains Varbinary(Max) datatype

Table Structure

SQL

CREATE TABLE [dbo].[Table_1](
    [EmpID] [numeric](18, 0) NOT NULL,
    [SrNo] [numeric](18, 0) NOT NULL,
    [Type] [varchar](10) NULL,
    [FileName] [varchar](100) NULL,
    [D1] [varchar](50) NULL,
    [D2] [varchar](50) NULL,
    [Data] [varbinary](max) NULL,
    [CreatedBy] [varchar](50) NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [EmpID] ASC,
    [SrNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SELECT Data FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 2 to 3 seconds to execute because it contains blob data
SELECT FileName,Type FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 0 seconds to execute

What I have tried:

Checked Indexing of the Table

Posted 21-May-19 20:54pm

kedar001

Updated 21-May-19 21:36pm

v2

Add a Solution

Comments

phil.o 22-May-19 3:03am

A varbinary column should not be indexed at all, at least not if it contains big chunks of data. Indexing this type of data can lead to huge performance drops.

kedar001 22-May-19 3:08am

my table having Primary key on ([EmpID],[SrNo]) columns only. how i can improve the Performance. because of this I'm facing deadlock issues also

phil.o 22-May-19 3:20am

In SQL-Server Management Studio (SSMS), you have the option to analyse your query and see where it spends most of its time.
Here is a list of possible sources to know how to analyze your query's performance:
ssms query performance troubleshooting[^]

OriginalGriff 22-May-19 3:49am

Answer updated.

kedar001 22-May-19 4:35am

please check https://www.codeproject.com/Questions/3301374/Transactionscope-deadlocked-issue-in-concurruncy

the man reason for deadlock is the time require to Delete/Insert the data in Table_1
is there any other way to overcome the issue..

OriginalGriff 22-May-19 4:49am

There is not delete or insert in your query - SELECT does not alter data in any way...

kedar001 22-May-19 4:54am

Sorry that's my observation. when i check on sql profiler the Time require to delete the particulate record is around 3 seconds.
same time require to select the Record.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-05-21T21:28:00

We have no idea what you select query is, or what it's trying to do - so there is nothing we can directly do to speed it up, or even suggest improvements.

So all we can do is direct you to the Performance Monitoring and Tuning Tools - SQL Server | Microsoft Docs[^] and suggest that you start reading!

Quote:
i have a simple SQL statement
SELECT Data FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 2 to 3 seconds to execute because it contains blob data
SELECT FileName,Type FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 0 seconds to execute

is there any way to improve the performance.

No. That's a very simple query - the time is being taken in the sheer amount of data being transferred, which takes a finite time depending on the bandwidth of the connection between the server and the client.
At a guess, you are storing very large data in your DB, which is always a bad idea. Instead, store the data on a file server, and store a link to the file in your DB.
Check your blob size: DATALENGTH (Transact-SQL) - SQL Server | Microsoft Docs[^] and I'm pretty sure it'll be quite large...