varbinary(max)

Question

varbinary(max)

LearningDBA

SSCertifiable

Points: 6609
More actions
February 24, 2020 at 5:55 pm

#3728324
Users are experiencing some performance issues when they come in/out of the building. Their ID card is scanned. That process takes sometimes 20 seconds for a pic to come up before we are allowed to enter into a building.
This is the code which runs every time we scan the card.
```
Select LastName, EmpNumber, Granted, Denied, ID_Picture FROM EmployeeCards where ProximityCardNumber = @id',N'@id nvarchar(11)',@id=N'111111111111'
```
ID_Picture has a varbinary(max) data type. When I remove this column, the query runs within the second. With ID_Picture column which is needed for our operation takes time. How do I increase the performance?
This table has 28k records and is about 2GB in size.
8 cores, 128 GB of memory and this is the only database on the server. DB size is 30 GB.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

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

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

You need an index on the ProximityCardNumber column in that table, or SQL will have to scan the full table every time the query runs.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Is ProximityCardNumber really an nvarchar(11)? Does it really need to be? (Not if ProximityCardNumbers are numeric strings like the sample... Of course, if this this a vendor database, your control over that is probably nil). To take advantage of the index Scott suggested, you'll want to ensure the parameter and column data types match. Smaller (varchar vs. nvarchar) is better if possible, as is avoiding conversion between nvarchar and varchar.

This reply was modified 4 years, 9 months ago by ratbak.

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

This doesn't sound like an indexing problem, unless we see an execution plan showing scans. Instead, it sounds like an I/O problem caused by the varbinary(MAX). However, I'd want some more information other than the query runs faster or slower with or without that column.

First, as with any tuning project, get an execution plan and at least validate that you're not dealing with problems there. Assuming there's nothing that needs addressing there, second, get the wait statistics for the query so you can understand specifically what's slowing things down. It could be disk or memory that are causing the problem. It could be resource contention, or a lack of resources. You need to gather more metrics to understand what's up before you just start making changes and applying indexes without knowledge or testing.

"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 4

I cribbed the example code given in the original post and added some line breaks. It looks like the following...

Select LastName, EmpNumber, Granted, Denied, ID_Picture 
FROM EmployeeCards 
where ProximityCardNumber = @id
',N'@id nvarchar(11)',@id=N'111111111111'

There's a missing single quote but, if you look at the last line, it looks like typical ORM code. To the point that others have made, ORMs will frequently make the mistake of using some form of unicode datatype and, if the underlying datatype doesn't match, then no index in the world will help because the difference in datatypes (when the variable is NVARCHAR()) will almost always cause the table to be scanned as it converts the underlying column to (in this case) NVARCHAR(11) before it can do the comparison.

Since this is 3rd party code, you'll need to contact the vendor and get them to fix their code so that the datatype actually matches if it's not matched to the actual datatype of the ProximityCardNumber column in the EmployeeCards column. You might also want to advise them of the Best Practice of using the two part naming convention (SchemaName.ObjectName) to help the code avoid making trips to the master database if the service user for the app isn't actually named "dbo".

If you could post the CREATE TABLE code including the indexes, that would be a good step in helping us help you.

There's also a possible optimization if the vendor drags their feet or refuses to change the code IF there's a datatype mismatch but I need the CREATE TABLE statement with the index creation code and then I also need you to execute the following code and post the output.

--===== We don''t care about super accuracy here.
     -- Make it so the following queries aren''t blocked
     -- by other things. This is a SESSION setting and
     -- it will NOT affect anyone else.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;
--===== Determine if the LOBs are allowed In-Row
     -- or forced out of row.
 SELECT OutOfRow = large_value_types_out_of_row
   FROM sys.tables
  WHERE object_id = OBJECT_ID('dbo.EmployeeCards','U')
;
--===== No pun intended but this will help us get a 
     -- picture of what''s happening with the LOBs.
 SELECT  PLenMin = MIN(LEN(ID_Picture))
        ,PLenAvg = AVG(LEN(ID_Picture))
        ,PLenMax = MAX(LEN(ID_Picture))
;

--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)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 5

Jeff Moden wrote:

To the point that others have made, ORMs will frequently make the mistake of using some form of unicode datatype and, if the underlying datatype doesn't match, then no index in the world will help because the difference in datatypes (when the variable is NVARCHAR()) will almost always cause the table to be scanned as it converts the underlying column to (in this case) NVARCHAR(11) before it can do the comparison.

Certainly the data types should match. And using nvarchar to lookup a varchar value causes a seek to be impossible (although not the other way around: if the column in nvarchar, a varchar value can still do a seek, since SQL will change its datatype to nvarchar automatically).

A nonclustered index should still very much help the nvarchar to varchar lookup, however, since SQL can scan the index to find the single matching value, rather than having to scan the entire table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".