Struggle Bus - Varbinary(max) Performance Suggestions?

Question

Struggle Bus - Varbinary(max) Performance Suggestions?

beantownace

Old Hand

Points: 333
More actions
December 16, 2022 at 2:53 am

#4126291

I have a very large table I am dealing with that has a varbinary(max) column. I have a Order By that is extremely slow on this field and also I have a Convert as well to a NVARCHAR(MAX) but even without the convert it is slow. How can I somehow increase the performance on this as a nonclustered index on this is not working as a varbinary cannot be used as a key so struggling to find a solution.
Thanks for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

How deep does identical data occur from the left side of the VARBINARY(MAX)? You might be able to speed things up a bit by sorting, say, only the left 50 or 100 bytes (for example).

Just to ask the question, what kind of data does the column contain and why do you need to sort it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Most common mistake is to put data into a varbinary(max) column data is frequently needed for filtering etc.

IMHO that is not what this data type is intended for. ( with xml data type you could opt for xml indexes, but even then )

split the blob column(s) from the rest of the table, so a select * is not that devistating for your environment. And to have more control over your blob storage.

Extract the frequently used dat into the table so you an offload the blob processing to the application side and don't polute sqlserver with that processing.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

beantownace Old Hand Points: 333 More actions · Answer 3

Wow these are awesome replies and super helpful thank you both so much. It has existed for some time but it stored encrypted data then uses a decryptkey with symetric key and password etc. So the Order By looks like this.

ORDER BY LTRIM(CONVERT(NVARCHAR(MAX), DecryptByKey([Customer].[Info]))) ASC

Usually this is mostly not very large data I will need to check more. So what would be the best way to trim and only get say the first 50 characters that may be an option for this particular case great idea.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Ordering by a function is just going to kill you more and more as the data grows. Best suggestion I can make is to not do that. Nothing will make it, as is, run faster. Calculated column with an index instead maybe? Otherwise, the other suggestions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

beantownace wrote:

Wow these are awesome replies and super helpful thank you both so much. It has existed for some time but it stored encrypted data then uses a decryptkey with symetric key and password etc. So the Order By looks like this.
ORDER BY LTRIM(CONVERT(NVARCHAR(MAX), DecryptByKey([Customer].[Info]))) ASC
Usually this is mostly not very large data I will need to check more. So what would be the best way to trim and only get say the first 50 characters that may be an option for this particular case great idea.

Didn't know it was encrypted. I agree with Grant... Decrypting it to sort it is going to hurt a whole lot even if you trying sorting the first 50 or 100 characters.

If there's a "non-classified" sort key that could be extracted from the encrypted column and stored in the same table, I'd do that. Otherwise, you're pretty much stuck with very long wait times and a lot of heavy lifting by the server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60472 More actions · Answer 6

All queries (should) only exist to solve a business problem. If the only way to answer this business problem is to sort on column-encrypted data then maybe the business problem can be changed.

Tell your users what is involved with solving the current problem and how long it will take to get the results. Ask them if there is an alternative that meets their needs that you can see will work faster.

Also look at alternatives to column encryption such as TDE. TDE and column encryption are best suited to different problems, are you using the best solution for the problem that wanted the column encrypted.

This reply was modified 1 year, 11 months ago by EdVassie.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara