1

I am working on a web application that needs to store a lot of pictures. I tried storing them in the blob-format in a database, but I quickly found out, that it takes far too long to retrieve the pictures from the db.

I read on some different forums, that it's possible to store the pictures in folders on a live server along with my web application, and that this option is far superior when it comes to performance etc.

So my question is; if I store these pictures on a live server, then how would I go about referencing these pictures in a SQL Server database, and secondly, how would I create 'mock-data' to test my stored procedures?

I realize that this is my first post, and that it probably is pretty basic for most experienced programmers.

I tried reading around on this site (and others), however, but I have been unable to find an answer.

Best regards

Brad Bit

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brad Bit
  • 39
  • 1
  • 8
  • 3
    You would store the file name in the database and the path in your config file. – Sean Lange Sep 07 '18 at 20:29
  • if it fits your use case, for example a project has 1 image, i store the image as projectId + ".jpg" this way I don't need to call the database to find image name, or i store folder named projectId. Try and remove the db call for name if possible when getting images. With this method you won't even need to store image links in db. – MIKE Sep 07 '18 at 20:39

2 Answers2

2

Most usually, you will store the files in the server filesystem (or some accessible network folder) and your database should store the relative or full path to them.

That way, if an user requests the file with id 250, you will retrieve the entry from the database "Document" (or whatever) table, and get its full path.

HaroldH
  • 533
  • 1
  • 4
  • 10
  • But how would i go about it in my stored procedure? How would the code look like, so i can execute the procedure and test if it works? – Brad Bit Sep 08 '18 at 13:39
  • Without knowing what you exactly want to achieve, what you tried, and in what language or platform you want to implement it, it is impossible to give you a straightforward answer. – HaroldH Sep 08 '18 at 13:50
2

Consider that (most) filesystems are optimized to access data hierarchcally (i.e. in folders) and perform poorly if you have one large folder with, say million files. Here is more on NTFS performance

To circumvent the bottleneck, create an n-level folder structure, where name of each level is a number. e.g. xxx/yyy/zzz/qqq.jpg.

Where xxx, yyy, zzz, and qqq are numbers 000-999. This should be sufficient to store 1 000 000 000 000 files and keep number of files/folders in a single folder within limits.

In your db create a table (file_id, level1, level2, level3, level4) to store where each file is located. Create a procedure that takes a file, figures out where to put it and writes location in the db.

Eriks Klotins
  • 4,042
  • 1
  • 12
  • 26