Very Slow VARBINARY(MAX) Select

user_560660 0
Viewed 1979

I store small (between 20K and 2MB) files in a VARBINARY(MAX) column. On my dev environment, which is local (on my development PC), the selection takes 20ms. 

UPDATE [DB_9FB6F6_smartplates].[dbo].[ImageLibrary]
SET filetype = 'jpg' 
WHERE filetype = 'exiting'

SELECT BinaryData 
FROM [ImageLibrary] 
WHERE Id = 1056

(Where Id is the primary key)

I upload my code to my (cheapish) hosting company, and execute the same query, it it takes 13 seconds.

We're both running SQL Server 2016. They just provide a database with no real access rights (no sa or anything like that), so I don't have permission to profile, but the code does seem to stick at that select.

Is there anyway I can find out why the same query takes so long on their environment, and mine is quick? Note, the DB is backed up from their environment and restored onto mine... So it's the exact same database.

All other queries are quick. It's just this selection on a VARCHAR(MAX) that is slow. I'm not sure how to diagnose it, and then query their support.

sql-server-2016 sql-server
1 Answers

I can think of two things you can try to solve this.

One:

CREATE NONCLUSTERED INDEX IX_ImageLibrary_FileType ON [DB_9FB6F6_smartplates].[dbo].[ImageLibrary] (filetype)

to speed up your update statement, which I reckon is the slow query.

Two: Do you have access to SQL Server Management studio on the production database? (not the local one). Try requesting an execution plan of both queries and see if it gives you any suggestions. Also, check the fragmentation of the primary key (I suppose it's on the column Id?)

user_6752030
Related