"Microsoft has spaffed out cloudy goodness like a Roomba in reverse"
I lol'd. Good work.
With Premium Blobs, Azure DevOps Server and a new Africa Azure region, Microsoft has spaffed out cloudy goodness like a Roomba in reverse. (Premium) Blobby, Blobby, Blobby! Blob storage in Azure is a flexible thing. It is unstructured and can scale up and down as needed. It can be immutable. You only pay for what you use. Heck …
I'm going to guess that the BLOB speed is the back-end... having messed with SQL Server a couple o' decades ago, where BLOB storage was done in a FILE SYSTEM rather than in the DBMS, deliberately. It was also compatible with the 'Jet' engine this way, too.
POSIX file systems are probably more efficient for handling large numbers of files in a directory. but normally an indexer and 'balancer' algorithm [using the generated file names, let's say] will spread it around the file system in a way that IS efficient. So, ideally, no one directory will have more than 4k files in it, including the tree of directories leading up to the one that has the actual file in it.
Windows systems, from what I recall, are not all that efficient when it comes to directory structure, especially when the directory contains thousands of files.
A simple google search led to this stack overflow article, which has some interesting performance observations in it:
In short, lots of files in a directory on windows, and you get performance problems. If SQL Server or Azure is storing BLOB data in a file system, this could be the problem. And if BLOB storage is going into something "like a file system", keep in mind that the same company wrote NTFS's file system, too. BLOB storage is probably STILL a performance problem with SQL Server.
I saw 'FILESTREAM' mentioned in one place. It's not something I've used but sounds similar to things I've implemented 'the hard way' i.e. put filename in character column in a table, and then create the actual file with the data in it, in a predictable place, making sure the name is unique beforehand. There are no transaction rollbacks on the BLOB this way, but it's most likely faster so if you create the file first, and then do the transactions in the DB, you can roll them back as needed and manage the file system separately...
anyway I think this might explain SQL Server's (and Azure's) BLOB performance problem. (not sure they have actually FIXED this, either, just made it 'less bad' maybe?)
Biting the hand that feeds IT © 1998–2019