File system based reads vs. a simple database query?

A CMS we use called Kentico stores Media Library Files on 开发者_Go百科the file system, and also stores a record in the database for file meta data (title, description, etc.). When you use a Media Library control to list those items, it will read the files from the file system to display them. Is it faster to read from the file system then to query the database? Or would it be faster to run a simple query on the media file meta data database table?

Assumptions:

• Kentico is an ASP.NET application, so the code is in C#. They use simple DataSets for passing their data around.
• Only meta data would be read from the direct files like filename and size.
• At most 100 files per folder.
• The database query would be indexed correctly.

• The query would be something like:

  SELECT *

  FROM Media_File

  WHERE FilePath LIKE 'Path/To/Current/Media/Folder/%'

---

The short answer is, it depends on a number of variable factors, but the file system will generally be faster than a DB.

The longer answer is: scanning the local filesystem at a known location is generally fast, because the resource is close to home and computers are designed to do these operations very efficiently.

HOWEVER, whether it's FASTER than a database depends on the database implementation, where it's located, and how much data we're talking about. On the whole, DBMSes are optimized to very effectively store and query large datasets, while a "flat" filesystem can only scan the drive as fast as the hardware goes. How fast they are depends on the implementation (SqLite isn't going to be as fast overall as MS Sql Server or Oracle), the communication scheme (transferring files over a network is the slowest thing your computer does regularly; by contrast, named pipes provide very fast inter-process communication), and how much hardware you're throwing at it (a quad-Xeon blade server with SATA-RAID striping is going to be much faster than your Celeron laptop).

---

In addition to what others have said here, caching can come into play too depending on your cache settings. Don't forget to take those into account as Kentico, SQL, and IIS all have many different levels of caching and are used at different times depending on your setup, configuration, and which use case(s) you are optimizing.

When it comes to performance issues at this level, the answer is often: it depends. So benchmark your own solution to see which one helps most in your particular users' situational needs.

Kentico did release a couple of performance guides (for 5.0 and another for 5.5) that may help, but they still won't give you a definitive answer until you test it yourself.