SQL-IIS, Fastest way to return an image: from the file system or database

moondaddy 911 Reputation points
2021-07-11T18:57:57.833+00:00

I have about 100GB of data imported into SQL Server from a Google Firebase database. The bulk of this data are images which are stored as nvarchar(max) which looks something like this:

/9j/4AAQSkZJRgABAQAASABIAAD/4QBMRXh....

We have to do 2 things with these images:

1) return them to a phone app for review.

2) Produce SSRS reports in IIS and render them as pdf files.

Will we get the best performance leaving them in the database or converting them to files and storing them in the file system? Performance being the speed in which the client view the images and the speed SSRS can produce the reports.

I know the database will be about 1% of the current size or less which may make the over all performance of the app faster.

What recommendations can someone please make?

Thank you.

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,161 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101K Reputation points MVP
    2021-07-12T08:14:47.683+00:00

    Image storage or reading from SQL involves encoding conversion issues. The server side needs to encode the image to form a string and then store it in SQL, while the file system only needs to store it directly, eliminating the need for encoding.

    Not really. You can store a sequence of bytes in a file or in a database. There is no difference. In either case, you need to store some knowledge on how to handle the bytes. (That is, what sort if image it is.) There is no need to encode something, just because you store it in a database. (For some reason Moondaddy has chosen the datatype nvarchar(MAX); a better type would be nvarbinary(MAX).)

    There are other good arguments for storing the images in the file system, and you list some of them.

    I like to point out a few more things. If you store the images in the file system, you can get a problem if there is a major crash and you need to restore from a backup. If everything is in the database, getting a consistent restore is never a problem. But this can be more difficult with files in the file system. How critical this is depends on the actual system, but this is something you should make analysis of.

    When it comes to speed, it is indeed faster to read files from disk than from the database, particularly for larger images, but this has more to do with the access pattern. However, there is an option to combines this, and that is FILESTREAM. With FILESTREAM, the images are stored as files in the file system, but you access them through the database combined with the OpenSqlFilestream API. A key feature here is that while the files are stored in the filesystem, they are still part of the database and included in backups etc, and makes a consistent restore easier to achieve.

    FILESTREAM is mainly useful when your files are of some size. The cut-off limit where it is worthwhile is usually given as 1 MB.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce Zhang-MSFT 3,736 Reputation points
    2021-07-12T02:15:04.953+00:00

    Hi @moondaddy ,

    The better way to store or return image is file system. The more images, the better the performance.

    Image storage or reading from SQL involves encoding conversion issues. The server side needs to encode the image to form a string and then store it in SQL, while the file system only needs to store it directly, eliminating the need for encoding.

    Now most websites use file system not SQL.

    • Image servers can run even when the database is busy or down.
    • File systems are made to store files and are quite efficient at it.
    • Dumping data in your database means slower backups and other operations.
    • No server-side coded needed to serve up an image.
    • You can perform related work (generating thumbnails, etc.) without involving the database.
    • Your database server can keep more of the "real" table data in memory, which is where you get your database speed for queries. If it uses its precious memory to keep image files cached, that doesn't buy you hardly anything speed-wise versus having more of the photo index in memory.

    On the other hand, compare with asp.net application, IIS shows great performance of serving static files. Using static images can be displayed to the client faster than dynamic generation.

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our  documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Bruce Zhang