We store some documents in a SQL Server database
VarBinary(Max) column. Most documents will be a few KB, but sometimes it maybe a couple of MB.
We run into an issue when the file becomes bigger than about 4MB.
When updating the
VarBinary column in a on-prem SQL Server, it is very fast (0.6 seconds for a 8MB file).
When doing the same statement on a identical database on SQL Azure, it takes more than 15 seconds!
Also if the code is running from an Azure App Service it is very slow. So it's not our Internet connection that is the problem.
I know storing files in SQL Server is not the preferred way of storing and Blob storage would normally the best solution, but we have special reasons we need to do this, so I want to leave that out of the discussion ;-)
When investigating the execution plans, I see a "Table Spool" taking all the time and I'm not sure why. Below are the execution plans for on prem and Azure.
Identical databases and data. If someone can help, that would be great.