0

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.

Thanks Chris

On Prem SQL 2016 enter image description here

Azure SQL database enter image description here

  • 1
    Can you provide the performance/tier specifications for both servers you are comparing? – datagod Oct 24 '17 at 16:07
  • 1
    Thank you @datagod . Indeed it was still on a basic tier. Changing that solved my issue. – Christian Oct 25 '17 at 7:43
1

The on-prem instance was just a local install on my surface. The other is a 50eDTU elastic pool on Basic-tier. After the comment I changed it to a standard tier and the query now takes about 2 seconds instead of more than 15! So I think that's the answer!

I never thought that a local install of SQL on a surface together with all other software like Visual Studio etc running, would out perform SQL Azure by 25 times! (Even on a basic tier). But now I know :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.